How to interpretate the clustering factor of an index

• Vital statistic used by CBO to determine cost of index access
• Clustering factor value corresponds to likely physical I/0s or blocks visited during a full index scan (note same block could be visited many times)
• If the same block is read consecutively then Oracle assumes only the 1 physical I/0 is necessary
• Determines the relative order of the table in relation to the index
• The better the Clustering factor, the more efficient the access via the corresponding index as less physical I/Os are likely
• “Good” Clustering factor generally has value closer to blocks in table
• “Bad” Clustering factor generally has a value closer to rows in table
• Generaly the clustering factor will be between the number of blocks and the number of rows in a table
• A low number is good and reflect strong clustering
• A high number is bad and reflect weak clustering
• The clustering may be lower than the number of blocks if there are empty blocks in the table below the HWM and/or there are many rows that have null for the indexed columns
• The clustering factor can never be greater than the number of rows
• The clustering factor for a bitmap index is just a copy of the number of rows and is never used