Clustering Factor for Indexes in Oracle

Indexes are a good way to increase the performance of a query, it can avoid table scan and directly help us to reach the data we need. However there are cases when the CBO will not consider the indexes that are associated to the table. The normal scenario that I see is that the index is not getting used because of poor selectivity or cardinality. There is another factor called Clustering Factor which also determines if the index needs to be used by the query. If the Clustering Factor is almost equal to the same number of rows in the table, then the index is poor and the CBO might not use the index.

To solve the problem, the best way is to reload the table and rebuild the index. During the reload the process, make sure that the column which is referenced by the index is in the ascending order. For e.g. For Employee table, the index XPKEMP is indexed on EMPID column. The process that needs to be followed are

create table EMPLOYEE_BKP as select * from EMPLOYEE order by empid asc;

drop table EMPLOYEE;

Create table EMPLOYEE as select * from EMPLOYEE_BKP;

Create index XPKEMP on EMPLOYEE(empid);

Now the index is created fresh and the clustering factor would be high. This can be verified by

Select clustering_factor from all_indexes where index_name=’XPKEMP’;

The value should be equal to the number of blocks occupied by the table. Now that all the indexes has good clustering factor, the chances of CBO taking the index is relatively high.