Oracle SQL編程及最佳化：聚簇因子筆記

1、聚簇因子（Clustering factor)

索引行的儲存順序與表中行的儲存順序之間的相似程度。

當相似性高時，這些資料行就會密集地儲存在相對較少的資料區塊中，這是聚簇因子比較好的情況。Oracle中，對於同一個查詢語句，有時候會很快的完成，有時候卻很慢，但是表結構什麼的完全一致，表中的資料也完全一致，這個具體是什麼原因呢，就要從Index中的細節說起了。在Oracle中的一個特殊的視圖user_indexes中有一個特殊的列，名字是clustering_factor，這個值的內容就是如果訪問表的整個表資料，會造成多少次資料庫IO。

A：如果這個值與塊數接近，則說明表相當有序，得到了很好的組織。在這種情況下，同一個葉子塊中的索引條目可能指向同一個資料區塊中的行。(clustering_factor=blocks 好)

B：如果這個值與行數接近，表的次序可能就是非常隨機的。在這種情況下，同一個葉子塊上的索引條目不太可能指向同一個資料區塊上的行。(clustering_factor=num_rows 不好)

可以把聚簇因子看作是通過索引讀取整個表時對錶執行的邏輯I/O次數。也就是說聚簇因子指示了表相對於索引本身的有序程度。當oracle對索引結構執行區間掃描時，如果它發現索引中的下一行與前一行在同一個資料區塊上，就不會再執行另一個I/O從緩衝區快取中獲得表塊。它已經有了表塊的一個控制代碼，只需要直接使用就行了。不過，如果下一行不在同一個塊上，就會釋放當前的這個塊，而執行另一個物理I/O在緩衝區快取存放要處理的下一個塊。

另外還要記住：對於一個表來說，一般只有一個索引能夠有合適的聚簇因子！表中的行可能只以一種方式排序。

我們可以通過下面的SQL語句來查看。

1. select T.TABLE_NAME || '.' || I.index_name IDX_NAME,  
2.        I.clustering_factor,  
3.        T.BLOCKS,  
4.        T.NUM_ROWS  
5.   from user_indexes i, user_tables t  
6.  where i.table_name = t.TABLE_NAME  
7.    and t.TABLE_NAME = ?  
8.  ORDER BY T.TABLE_NAME, I.table_NAME; 

在這個SQL語句中，?代表的就是我們要檢索的Index的名稱。在表中資料有時候屬於無序狀態，這個時候的CLUSTERING_FACTOR比較接近NUM_ROWS，說明如果掃描整個表，每次都要根據Index來讀取相應行的RowID，這個時候的IO操作很多，自然檢索時間會比較長。如果資料有序的話，CLUSTERING_FACTOR比較接近BLOCKS，說明相鄰的資料在一個塊中，減少了IO運算元量，自然檢索時間會大大降低。

2、準備實驗條件

1. CREATE TABLE test_1 AS 
2.   SELECT ROWNUM rn, a.* FROM all_objects a ORDER BY <SPAN style="COLOR: #ff0000">object_name DESC</SPAN>  
3. ;  
4. CREATE INDEX ind_test_1 ON test_1(rn);  
5.  
6. CREATE TABLE test_2 AS 
7.  SELECT * FROM (SELECT ROWNUM rn, a.* FROM all_objects a)<SPAN style="COLOR: #ff0000"> ORDER BY rn ASC</SPAN>  
8. ;  
9. CREATE INDEX ind_test_2 ON test_2(rn);   

代碼：

1. SQL> EXEC DBMS_STATS.gather_table_stats('USERS','TEST_1');  
2.    
3. SQL> EXEC DBMS_STATS.gather_table_stats('USERS','TEST_2');  
4.    
5. SQL> EXEC DBMS_STATS.gather_index_stats('USERS', 'IND_TEST_1');  
6.  
7. SQL> EXEC DBMS_STATS.gather_index_stats('USERS', 'IND_TEST_2'); 

1 2 下一頁