Clustering Factor

Clustering Factor (Doc ID 39836.1)

Clustering Factor

The Clustering Factor is a statistic collected for use by the Cost Based Optimizer (CBO) and is a measure of the 'ordered-ness' of an index in comparison to the table that it is based upon. It is used as an indicator for computing the estimated cost of the table lookup following an index access.

The Clustering Factor records the number of data blocks that will be accessed when scanning an index.

Clustering factor values can be seen in the following views:

• ALL|DBA|USER_INDEXES.

It can be set also in:

• ALL|DBA|USER_IND_PARTITIONS 
• ALL|DBA|USER_IND_SUBPARTITIONS

when the indexes are partitioned.

For further details on Clustering Factor see:

Calculation

It is calculated as follows:

• The index is scanned in order.
• The block portion of the rowid pointed at by the current indexed value is compared with that pointed at by the previous indexed value. This is achieved by comparing adjacent rowids in the index leaf block (the blocks themselves do not need to be visited). 
• If these rowids are pointing at different blocks then a counter is incremented.
• This is continued throughout the whole index
• The resultant count is then stored.

From 12c, the SET_GLOBAL_PREFS Procedure allows you to set a value for TABLE_CACHED_BLOCKS. The default is 1 and the max is 255. This parameter is used when gathering the index clustering factor and it tells the gathering engine the average number of blocks cached in the buffer cache for any table. With small tables this could have quite a significant effect, less so with larger objects since 255 will be a less significant proportion of the objects. The recommendation is to leave this parameter at the default. See:

Interpretation

If the count is close to the number of blocks in the table then the index is well ordered. This is true because the counter only gets incremented when the actual data is found in a different block from the last piece of row data. What this means is that when an index block is read, the table lookups required based on that block are likely to be in the same table blocks. So, the lower the Clustering Factor the less I/O is likely to be used by the statement and so the optimizer is more likely to choose that index.

If the count is close to the number of rows in the table then the index is less well ordered. In this case adjacent index entries do not tend to point to the same block in the table, thus more block reads are likely to be required. An index with a higher clustering factor is more likely to have to revisit Data blocks than with a lower Clustering factor .

The clustering factor can be used by the optimizer to adjust the potential number of blocks that will be accessed by a particular predicate or query. This is useful for determining the number of base table blocks that will be visited when accessed via the index.

The clustering factor is effectively a count of the number of data blocks visited as the result of an index lookup. Multiplying the clustering factor by the selectivity will give the cost of the operation. It is predominately used to calculate costs for index range scans.

Common Questions

• How can Clustering Factor be Reduced
  
  The only method to affect the clustering factor is to sort and then store the rows in the table in the same order as in they appear in the index. Exporting rows and putting them back in the same order that they appeared originally will have no affect. Remember that ordering the rows to suit one index may have detrimental effects on the choice of other indexes.

=======================================================================

advice which included that indexes should be rebuilt if:

• Deleted entries represent 20% or more of current entries
• The index depth is more than 4 levels

==========================================================