The importance of clustering_factor in multi-block I/O Oracle Tips by Burleson Consulting

 

Oracle MOSC Note:223117.1 has some great advice for tuning-down ?db file sequential read? waits by table reorganization in row-order: 

- If Index Range scans are involved, more blocks than necessary could be being visited if the index is unselective: by forcing or enabling the  use of a more selective index, we can access the same table data by  visiting fewer index blocks (and doing fewer physical I/Os).   

 

- If the index being used has a large Clustering Factor, then more table data blocks have to be visited in order to get the rows in each Index block: by rebuilding the table with its rows sorted by the particular index columns we can reduce the Clustering Factor and hence the number of table data blocks that we have to visit for each index block.    

 

This validates the assertion that the physical ordering of table rows can reduce I/O (and stress on the database) for many SQL queries.

 

For more information on tuning with clustering_factor, see these links:

 

http://www.dba-oracle.com/art_index_clustering_factor.htm

 

In sum, the CBOs decision to perform a full-table vs. an index range scan is influenced by the clustering_factor, db_block_size, and avg_row_len. It is important to understand how the CBO uses these statistics to determine the fastest way to deliver the desired rows.

 

http://www.dba-oracle.com/art_otn_cbo_p5.htm  

Conversely, a high clustering_factor, where the value approaches the number of rows in the table (num_rows), indicates that the rows are not in the same sequence as the index, and additional I/O will be required for index range scans. As the clustering_factor approaches the number of rows in the table, the rows are out of sync with the index.