原创 转载请注明出处

come from：http://space.itpub.net/?uid-7728585-action-viewspace-itemid-612691

先引出ORACLE WAIT INTERFACE中的原话：

In addition to SQL tuning, it may also be worthwhile to check the index’s clustering factor if the execution plan calls fortable access by index rowid. The clustering factor of an index defines how ordered the rows are in the table. It affects the number of I/Os required for the whole operation. If the DBA_INDEXES.CLUSTERING_FACTOR of the index approaches the number of blocks in the table, then most of the rows in the table are ordered. This is desirable. However, if the clustering factor approaches the number of rows in the table, it means the rows in the table are randomly ordered. In this case, it is unlikely for the index entries in the same leaf block to point to rows in the same data block, and thus it requires more I/Os to complete the operation. You can improve the index’s clustering factor by rebuilding the table so that rows are ordered according to the index key and rebuilding the index thereafter. What happens if the table has more than one index? Well, that is the downside. You can only cater to the most used index.

然后给出中文概念：

在索引的分析数据上clustering_factor是一个很重要的参数，表示的是索引和表之间的关系，因为，索引是按照一定的顺序排列的，但是，对于表来说是按照一种heap的形式存放，每一行可能分布在段上任何一个块上，所以要是通过索引来查找数据行的时候，就有可以一个索引块对应多个，甚至全部表的块，所以引入了clustering_factor这个参数来表示表上数据存放和索引之间的对应关系。这样CBO就可以根据这个参数来判断使用这个索引产生的cost是多少。ITPUB个人空间5R _#o_x
一般来说，如果这个表的排列是按照索引列的顺序存放数据的话，这个参数就应该和数据表上的块相类似。

然后做简单的试验证明：

1、试验1

SQL> create table test10ITPUB个人空间a#v;W+M