Managing Indexes and Clusters in Oracle

Managing Indexes and Clusters in Oracle

INDEXES

Use indexes to speed up queries. Indexes speeds up searching of information in tables. So create indexes on those columns which are frequently used in WHERE conditions. Indexes are helpful if the operations return only small portion of data i.e. less than 15% of data is retrieved from tables.

Follow these guidelines for creating indexes

• Do not create indexes on small tables i.e. where number of rows are less. (Full table scan itself will be faster if table is small)
• Do not create indexes on those columns which contain many null values.
• Do not create BTree  index on those columns which contain many repeated values. In this case create BITMAP indexes on these columns.
• Limit the number of indexes on tables because, although they speed up queries, but at the same time DML operations becomes very slow as all the indexes have to updated whenever an Update, Delete or Insert takes place on tables.

Creating Indexes

To create an Index give the create index command. For example the following statement creates an index on empno column of emp table.

create index empno_ind on emp (empno);

If two columns are frequently used together in WHERE conditions then create a composite index on these columns. For example, suppose we use EMPNO and DEPTNO oftenly together in WHERE condition. Then create a composite index on these column as given below

create index empdept_ind on emp (empno,deptno);

The above index will be used whenever you use empno or deptno column together, or you just use empno column in WHERE condition. The above index will not be used if you use only deptno column alone

BITMAP INDEXES

Create Bitmap indexes on those columns which contains many repeated values and when tables are large. City column in EMP table is a good canditate for Bitmap index because it contain many repeated values.  To create a composite index give the following command.

create bitmap index city_ind on emp (city);

FUNCTION BASED Indexes

Function Based indexes are built on expressions rather  than on column values. For example if you frequently use the expression SAL+COMM  in WHERE conditions then create a Function base index on this expression like this

create index salcomm_ind on emp (sal+comm);

Now, whenever you use the expression SAL+COMM in where condition then oracle will use SALCOMM_IND index.

DROPPING INDEXES

To drop indexes use DROP INDEX statement. For example to drop SALCOMM_IND give the following statement

drop index salcomm_ind;

Listing Information about indexes

To see how many indexes are there in your schema and its information, give the following statement.

select * from user_indexes;

CLUSTERS

If you two are more tables are joined together on a single column and most of the time you issue join queries on them, then consider creating a cluster of these tables.

A cluster is a group tables that share the same data blocks i.e. all the tables are physically stored together.

For example EMP and DEPT table are joined on DEPTNO column. If you cluster them, Oracle physically stores all rows for each department from both the emp and dept tables in the same data blocks.

• Since cluster stores related rows of different tables in same data blocks, Disk I/O is reduced and access time improves for joins of clustered tables.

• Each cluster key value is stored only once each in the cluster and the cluster index, no matter how many rows of different tables contain the value.

Therefore, less storage might be required to store related table and index data in a cluster than is necessary in non-clustered table format.

CREATING A CLUSTER

To create clustered tables. First, create a cluster and create index on it. Then create tables in it.

For example to create a cluster of EMP and DEPT tables in which the DEPTNO will be cluster key, first create the cluster by typing the following command.

create cluster emp_dept (deptno number(2));

Then create index on it.

create index on cluster emp_dept;

Now create table in the cluster like this

create table dept (deptno number(2),
                    name varchar2(20),
                    loc varchar2(20))
                    cluster emp_dept (deptno);

create table emp (empno number(5),
            name varchar2(20),
            sal number(10,2),
            deptno number(2)) cluster emp_dept (deptno)      

Dropping Clusters

To drop a cluster use DROP CLUSTER statement. For example to drop the emp_dept cluster give the following command.

drop cluster emp_dept;

This will drop the cluster, if the cluster is empty i.e. no tables are existing it it. If tables are there in the cluster first drop the tables and then drop the cluster. If you want to drop the cluster even when tables are there then give the following command.

drop cluster emp_dept including tables;

Listing Information about Clusters

To see how many clusters are there in your schema give the following statement.

select * from user_clusters;

To see which tables are part of a cluster. Give the following command.

select * from tab

TABLE_NAME    TYPE      CLUSTER_ID
----------    ----      -----------
EMP           TABLE         1
SALGRADE      TABLE        
CUSTOMER      TABLE        
DEPT          TABLE         1

In the above example notice the CLUSTER_ID column, for EMP and DEPT table the cluster_id is 1. That means these tables are in cluster whose cluster_id is 1. You can see the cluster_id’s name in USER_CLUSTERS table.