【DB筆試面試563】在Oracle中，什麼是聚簇因子（Clustering Factor）？

• 2019 年 10 月 10 日
• 筆記

♣

題目部分

在Oracle中，什麼是聚簇因子（Clustering Factor）？

♣

答案部分

Oracle資料庫中最普通、最為常用的即為堆表，堆表的數據存儲方式為無序存儲，當對數據進行檢索的時候，非常消耗資源，這個時候就可以為表創建索引了。在索引中，數據是按照一定的順序排列起來的。當新建或重建索引時，索引列上的順序是有序的，而表上的順序是無序的，這樣就存在了差異，即表現為聚簇因子（Clustering Factor，簡稱CF），也稱為群集因子或集群因子等，本書統一稱為聚簇因子。聚簇因子值的大小對CBO判斷是否選擇相關的索引起著至關重要的作用。

在Oracle資料庫中，聚簇因子是指按照索引鍵值排序的索引行和存儲於對應表中數據行的存儲順序的相似程度，也就是說，表中數據的存儲順序和某些索引欄位順序的符合程度。CF是基於表上索引列上的一個值，每一個索引都有一個CF值。

Oracle按照索引塊所存儲的ROWID來標識相鄰索引記錄在表塊中是否為相同塊。Oracle通過如下方法計算CF：檢查索引塊上每一個ROWID的值，查看是否前一個ROWID的值與後一個ROWID指向了相同的數據塊，如果指向了不相同的數據塊那麼CF的值增加1。當索引塊上的每一個ROWID被檢查完畢，即得到最終的CF值。舉個例子，比如說索引中有a、b、c、d、e五個記錄，首先比較a和b是否在同一個塊，如果不在同一個塊，那麼CF+1，然後繼續比較b和c。同理，如果b和c不在同一個塊，那麼CF+1，這樣一直進行下去，直到比較了所有的記錄才結束，最終得到CF的值。注意，這裡Oracle在比對ROWID的時候並不需要回表去訪問相應的表塊。

具體來說，計算CF的演算法如下所示：

（1）聚簇因子的初始值為1。

（2）Oracle首先定位到目標索引處於最左邊的葉子塊。

（3）從最左邊的葉子塊的第一個索引鍵值所在的索引行開始順序掃描，在順序掃描的過程中，Oracle會比對當前索引行的ROWID和它之前的那個索引行（它們是相鄰的關係）的ROWID，如果這兩個ROWID並不是指向同一個表塊，那麼Oracle就將聚簇因子的當前值遞增1；如果這兩個ROWID是指向同一個表塊，那麼Oracle就不改變聚簇因子的當前值。注意，這裡Oracle在比對ROWID的時候並不需要回表去訪問相應的表塊。

（4）上述比對ROWID的過程會一直持續下去，直到順序掃描完目標索引所有葉子塊里的所有索引行。

（5）上述順序掃描操作完成後，聚簇因子的當前值就是索引統計資訊中的CLUSTERING_FACTOR，Oracle會將其存儲在數據字典里。

好的CF值接近於表上的塊數，而差的CF值則接近於表上的行數。CF值越小，相似度越高，CF值越大，相似度越低。如果CF的值接近塊數，那麼說明表的存儲和索引存儲排序接近，也就是說表中的記錄很有序，這樣在做INDEX RANGE SCAN的時候，讀取少量的數據塊就能得到想要的數據，代價比較小。如果CF值接近表記錄數，那麼說明表的存儲和索引排序差異很大，在做INDEX RANGE SCAN的時候，由於表記錄分散，所以會額外讀取多個塊，代價較高。

由於聚簇因子高的索引走索引範圍掃描時比相同條件下聚簇因子低的索引要耗費更多的物理I/O，所以聚簇因子高的索引走索引範圍掃描的成本會比相同條件下聚簇因子低的索引走索引範圍掃描的成本高。Oracle選擇索引範圍掃描的成本可以近似看作是和聚簇因子成正比，因此，聚簇因子值的大小實際上對CBO判斷是否走相關的索引起著至關重要的作用。其實，聚簇因子決定著索引回表讀的開銷。在Oracle資料庫中，能夠降低目標索引的聚簇因子的唯一方法就是對表中數據按照目標索引的索引鍵值排序後重新存儲。需要注意的是，這種方法可能會同時增加該表上存在的其它索引的聚簇因子的值。

可以通過如下的命令顯式的設置聚簇因子的值：

EXEC DBMS_STATS.SET_INDEX_STATS(OWNNAME=>'LHR',INDNAME=>'IND2',CLSTFCT=>400000000,NO_INVALIDATE=>FALSE);  

CF值可以通過查詢視圖DBA_INDEXES中的CLUSTERING_FACTOR列來獲取。下邊的SQL是查詢索引的相關資訊，通過視圖DBA_INDEXES、DBA_OBJECTS和DBA_TABLES關聯得到，可以查詢當前索引的大小、行數、創建日期、索引高度和聚簇因子等資訊。

SELECT DI.OWNER INDEX_OWNER,         DI.TABLE_OWNER,         DI.TABLE_NAME,         DI.INDEX_NAME,         DI.INDEX_TYPE,         DI.UNIQUENESS,         (SELECT DECODE(NB.CONSTRAINT_TYPE, 'P', 'YES')            FROM DBA_CONSTRAINTS NB           WHERE NB.CONSTRAINT_NAME = DI.INDEX_NAME             AND NB.OWNER = DI.OWNER             AND NB.CONSTRAINT_TYPE = 'P') IS_PRIMARY_KEY,         DI.PARTITIONED,         (SELECT COUNT(1)            FROM DBA_IND_COLUMNS DIC           WHERE DIC.INDEX_NAME = DI.INDEX_NAME             AND DIC.TABLE_NAME = DI.TABLE_NAME             AND DIC.INDEX_OWNER = DI.OWNER) 索引列個數,         DI.TABLESPACE_NAME,         DI.STATUS,         DI.VISIBILITY,         (SELECT (SUM(BYTES))            FROM DBA_SEGMENTS ND           WHERE SEGMENT_NAME = DI.INDEX_NAME AND ND.OWNER = DI.OWNER           GROUP BY SEGMENT_NAME) INDEX_SIZE_BYTES,         DI.DOMIDX_OPSTATUS,         DI.DOMIDX_STATUS,         DI.PARAMETERS,         DI.LAST_ANALYZED,         DI.DEGREE,         DT.NUM_ROWS TABLE_NUM_ROWS,         DT.BLOCKS TABLE_BLOCKS,         DI.NUM_ROWS INDEX_NUM_ROWS,         DECODE(DI.NUM_ROWS, 0, '', ROUND(DI.DISTINCT_KEYS / DI.NUM_ROWS, 2)) SELECTIVITY,         DIS.STALE_STATS,         DI.BLEVEL 索引的分支層數,         DI.BLEVEL + 1 索引的高度,         DI.LEAF_BLOCKS 葉子結點的個數,         DI.DISTINCT_KEYS 唯一值的個數,         DI.AVG_LEAF_BLOCKS_PER_KEY 每個KEY的平均葉塊個數,         DI.AVG_DATA_BLOCKS_PER_KEY 每個KEY的平均數據塊數,         DI.CLUSTERING_FACTOR 集群因子,         DI.COMPRESSION,         DI.LOGGING,         (SELECT D.CREATED            FROM DBA_OBJECTS D           WHERE D.OBJECT_NAME = DI.INDEX_NAME             AND D.OBJECT_TYPE = 'INDEX'             AND D.OWNER = DI.OWNER) INDEX_CREATE    FROM DBA_INDEXES DI    LEFT OUTER JOIN DBA_IND_STATISTICS DIS      ON (DI.OWNER = DIS.OWNER AND DI.INDEX_NAME = DIS.INDEX_NAME AND         DI.TABLE_NAME = DIS.TABLE_NAME AND DI.TABLE_OWNER = DIS.TABLE_OWNER AND         DIS.OBJECT_TYPE = 'INDEX')    LEFT OUTER JOIN DBA_TABLES DT      ON (DI.TABLE_NAME = DT.TABLE_NAME AND DI.TABLE_OWNER = DT.OWNER)   WHERE DI.INDEX_NAME = 'IDX_T_CF_20160927_LHR';  

使用PLSQL Developer工具運行查看可以得到如下的結果：

針對聚簇因子的內容，可以做一個實驗來深入理解它的作用。建立實驗環境如下所示：

CREATE TABLE T_CF_161021_LHR_01 AS SELECT TRUNC(ROWNUM/100) ID ,OBJECT_NAME FROM DBA_OBJECTS WHERE ROWNUM<1000;  CREATE TABLE T_CF_161021_LHR_02 AS SELECT MOD(ROWNUM,100) ID ,OBJECT_NAME FROM DBA_OBJECTS WHERE ROWNUM<1000;  CREATE INDEX INX_T1_LHR ON T_CF_161021_LHR_01(ID);  CREATE INDEX INX_T2_LHR ON T_CF_161021_LHR_02(ID);  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_CF_161021_LHR_01',CASCADE => TRUE);  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_CF_161021_LHR_02',CASCADE => TRUE);  

表T_CF_161021_LHR_01的數據量分布，每個ID對應大約100行記錄：

SELECT T.ID,COUNT(1) FROM T_CF_161021_LHR_01 T GROUP BY T.ID;  

表T_CF_161021_LHR_02的數據量分布，每個ID對應大約10行記錄：

SELECT T.ID,COUNT(1) FROM T_CF_161021_LHR_02 T GROUP BY T.ID;  

當這兩個表的ID為2時，查看其執行計劃：

SYS@lhrdb> SET AUTOT TRACE EXP  SYS@lhrdb> SELECT * FROM T_CF_161021_LHR_01 A WHERE A.ID=2;  100 rows selected.  Execution Plan  ----------------------------------------------------------  Plan hash value: 894988015  --------------------------------------------------------------------------------------------------  | Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |  --------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |                    |   100 |  2000 |     2   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY INDEX ROWID| T_CF_161021_LHR_01 |   100 |  2000 |     2   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | INX_T1_LHR         |   100 |       |     1   (0)| 00:00:01 |  --------------------------------------------------------------------------------------------------  Predicate Information (identified by operation id):  ---------------------------------------------------     2 - access("A"."ID"=2)    SYS@lhrdb> SELECT * FROM T_CF_161021_LHR_02 A WHERE A.ID=2;  10 rows selected.  Execution Plan  ----------------------------------------------------------  Plan hash value: 775989556  ----------------------------------------------------------------------------------------  | Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |  ----------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |                    |    10 |   200 |     3   (0)| 00:00:01 |  |*  1 |  TABLE ACCESS FULL| T_CF_161021_LHR_02 |    10 |   200 |     3   (0)| 00:00:01 |  ----------------------------------------------------------------------------------------  Predicate Information (identified by operation id):  ---------------------------------------------------     1 - filter("A"."ID"=2)  

可以看到，針對錶T_CF_161021_LHR_01，執行計劃選擇了索引掃描，而針對錶T_CF_161021_LHR_02，執行計劃選擇了全表掃描。由於這兩個表中都有999行記錄，而表T_CF_161021_LHR_01返回100行記錄，表T_CF_161021_LHR_02返回10行記錄，執行計劃應該都選擇索引才對，但表T_CF_161021_LHR_02卻選擇了全表掃描。現在來看一下這兩個表的聚簇因子情況，如下所示：

SYS@lhrdb> SELECT  A.INDEX_NAME,    2          B.NUM_ROWS,    3          B.BLOCKS,    4          A.CLUSTERING_FACTOR    5  FROM     USER_INDEXES A,    6          USER_TABLES B    7  WHERE   A.INDEX_NAME IN ('INX_T1_LHR','INX_T2_LHR')    8  AND A.TABLE_NAME = B.TABLE_NAME;  INDEX_NAME                       NUM_ROWS     BLOCKS CLUSTERING_FACTOR  ------------------------------ ---------- ---------- -----------------  INX_T1_LHR                            999          4                 4  INX_T2_LHR                            999          4               400  

可以看到T_CF_161021_LHR_01的CF值和表的塊數相同，說明表的存儲和索引存儲排序接近，數據分布比較集中，所以，執行計劃選擇了索引掃描。表T_CF_161021_LHR_02的CF值是錶行數的一半，CF值較大，說明表數據分布比較分散，可能需要讀取更多的塊，所以，Oracle選擇了全表掃描。

由此看出，聚簇因子和Oracle的執行計劃是息息相關的。

& 說明：

有關CF的更多知識和實驗可以參考：http://blog.itpub.net/26736162/viewspace-2139229/

本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。