DBA_INDEXES.CLUSTERING_FACTOR 索引的群集因子初探

原創 轉載請註明出處

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

先引出ORACLE WAIT INTERFACE中的原話：

In addition to 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 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