oracle Clustering Factor

转载自：http://chenlin10058.iteye.com/blog/1558326

 

Clustering Factor的含义是通过一个索引扫描一张表时需要访问的表的数据块的数量。

Clustering Factor计算的方法如下：

1、扫描一个索引.

2、比较某行的rowid和前一行的rowid，如果这两个rowid不属于同一个数据块，那么cluster factor增加1.

3、整个索引扫描完毕后，就得到了该索引的cluster factor.

如果Clustering Factor接近于表存储的块数，说明这张表是按照索引字段的顺序存储的。如果Clustering Factor接近于行的数量，那说明这张表不是按索引字段顺序存储的。在计算索引访问成本的时候，这个值十分有用。Clustering Factor乘以选择性参数（selectivity ）就是访问索引的开销.

如果这个统计数据不能真实反映出索引的真实情况，那么可能会造成优化器错误的选择执行计划。另外如果某张表上的大多数访问是按照某个索引做索引扫描，那么将该表的数据按照索引字段的顺序重新组织，可以提高该表的访问性能.

官网说明:

       The index clustering factor measures row order in relation to an indexed value such as employee last name. The more order that exists in rows torage for this value, the lower the clustering factor.

       -- row 存储的越有序，clustering factor 的值越低

       Theclustering factor is useful as a rough measure of the number of I/Os required to read an entire table by means of an index:

       （1）If the clustering factor is high, then Oracle Database performs a relatively high number of I/Os during a large index range scan. The index entries point to random table blocks, so the database  may have to read and reread the same blocks over and over again to retrieve the data pointed to by the index.

       --当clustering factor 很高时，说明index entry（rowid） 是随机指向一些block的，在一个大的indexrange scan时，这样为了读取这些rowid 指向的block，就需要一次有一次重复的去读这些block。

       （2）If the clustering factor is low, then Oracle Database performs a relatively low number of I/Os during a large index range scan. The index keys in a range tend to point to the same data block, so the database does not have to read and reread the same blocks over and over.

       --当clustering factor 值低时，说明index keys（rowid） 是指向的记录是存储在相同的block里，这样去读row时，只需要在同一个block里读取就可以了。就可以减少重复读取block的次数。

The clustering factor is relevant for index scans because it can show:

       （1）Whether the database will use an index for large range scans

       （2）The degree of table organization in relation to the index key

       （3）Whether you should consider using an index-organized table,partitioning, or table cluster if rows must be ordered by the index