It is used by Oracle's optimizer to help determine the cost associated with index range scans in comparison to full table scans.
To calculate the clustering factor of an index during the gathering of index statistics, Oracle does the following.
For each entry in the index Oracle compares the entry's table rowid block with the block of the previous index entry.
If the block is different, Oracle increments the clustering factor by 1.
The minimum possible clustering factor is equal to the number of blocks identified through the index's list of rowid's -- for an index on a column or columns conatining no nulls, this will be equal to the number of blocks in the table that contain data. The maximum clustering factor is the number of entries in the index.
create table abc as select object_id,object_name from all_objects;
create index aidx on abc(object_id) compute statistics;
select clustering_factor From user_indexes where index_name ='AIDX';
CLUSTERING_FACTOR
-----------------
279
select count(*) from abc;
COUNT(*)
----------
54540
select blocks from user_segments where segment_name='ABC';
BLOCKS
----------
384
In the above example index has to jump 279 times to give all the data if a full table scan is performed using the index.
A good clustering factor is equal (or near) to the values of number of blocks of table.
A bad clustering factor is equal (or near) to the number of rows of table.
Rebuilding index does not improve the clustering factor. To improve the clustering factor table must be rebuild and reordered.
