聚簇因子是 Oracle 統計資訊中在CBO優化器模式下用于計算cost的參數之一，決定了目前的SQL語句是否走索引，還是全表掃描以及是否作為嵌套連接配接外部表等。如此這般，那到底什麼是聚簇因子，那些情況下會影響到聚簇因子，以及如何提高聚簇因子？本文将對此展開描述。
1、堆表的存儲方式
Oralce 資料庫系統中最普通，最為常用的即為堆表。
堆表的資料存儲方式為無序存儲，也就是任意的DML操作都可能使得目前資料塊存在可用的空閑空間。
處于節省空間的考慮，塊上的可用空閑空間會被新插入的行填充，而不是按順序填充到最後被使用的塊上。
上述的操作方式導緻了資料的無序性的産生。
當建立索引時，會根據指定的列按順序來填充到索引塊，預設的情況下為升序。
建立或重建索引時，索引列上的順序是有序的，而表上的順序是無序的，也就是存在了差異，即表現為聚簇因子。
2、什麼是聚簇因子(clustering factor/CF)
聚簇因子是基于表上索引列上的一個值，每一個索引都有一個聚簇因子。
用于描述索引塊上與表塊上存儲資料在順序上的相似程度，也就說表上的資料行的存儲順序與索引列上順序是否一緻。
在全索引掃描中，CF的值基本上等同于實體I/O或塊通路數，如果相同的塊被連續讀，則Oracle認為隻需要1次實體I/O。
好的CF值接近于表上的塊數，而差的CF值則接近于表上的行數。
聚簇因子在索引建立時就會通過表上存存在的行以及索引塊計算獲得。
3、Oracle 如何計算聚簇因子
執行或預估一次全索引掃描。
檢查索引塊上每一個rowid的值，檢視是否前一個rowid的值與後一個指向了相同的資料塊，如果指向了不相同的資料塊則CF的值增加1。
當索引塊上的每一個rowid被檢查完畢，即得到最終的CF值。
4、聚簇因子圖示
a、良好的索引與聚簇因子的情形
b、良好的索引、差的聚簇因子的情形
c、差的索引、差的聚簇因子的情形
5、影響聚簇因子的情形
當插入到表的資料與索引的順序相同時，可以提高聚簇因子(接近表上的塊數)。
是以，任意影響該順序的情形都将導緻索引列上的聚簇因子變差。
如列的順序，反向索引，空閑清單或空閑清單組。
6、提高聚簇因子
堆表的資料存儲是無序存儲，是以需要使無序變為有序。下面是提高聚簇因子的辦法。
a、對于表上的多個索引以及組合索引的情形，索引的建立應考慮按應該按照經常頻繁讀取的大範圍資料的讀取順序來建立索引。
b、定期重構表(針對堆表)，也就是使得表與索引上的資料順序更接近。注意，是重構表，而不是重建索引。
重建索引并不能顯劇提高CF的值，因為索引列通常是有序的，無序的是原始表上的資料。
提取原始表上的資料到一個臨時表，禁用依賴于該表的相關限制，truncate原始表，再将臨時表的資料按索引通路順序填充到原始表。
c、使用聚簇表來代替堆表。
7、實戰聚簇因子随索引結構變化的情形
a、示範環境
scott@SYBO2SZ> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
b、列順序對CF的影響
--列順序指索引列值順序與表中的列值的順序，一緻，則CF良好，不一緻，CF較差。
scott@SYBO2SZ> create table t as select * from dba_objects order by object_name;
scott@SYBO2SZ> create index i_obj_name on t(object_name); -->基于object_name列建立索引
scott@SYBO2SZ> create index i_obj_id on t(object_id); -->基于object_id列建立索引
cott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);
PL/SQL procedure successfully completed.
scott@SYBO2SZ> @idx_stat
Enter value for input_table_name: T
Enter value for owner: SCOTT
AVG LEAF BLKS AVG DATA BLKS
BLEV IDX_NAME LEAF_BLKS DST_KEYS PER KEY PER KEY CLUST_FACT LAST_ANALYZED TB_BLKS TB_ROWS
---- ------------- ---------- ---------- ------------- ------------- ---------- ----------------- ---------- ----------
1 I_OBJ_NAME 241 29476 1 1 675 20130418 17:00:42 695 48931
1 I_OBJ_ID 108 48931 1 1 24887 20130418 17:06:10 695 48931
--從上面的查詢可以看出，索引I_OBJ_NAME的聚簇因子小于表上的塊數，一個良好的CF值，因為object_name列是有序插入的。
--而索引I_OBJ_ID上的CF接近于表上行數的一半，說明該索引上的CF值不是很理想，因為object_id在插入到table時是無序的。
--從上可知，一個表隻能有一種有序的方式來組織資料。是以對于多出一個索引的表，且順序按照非插入時的順序時，則其他索引上的聚簇因子很難獲得理想的值。
c、組合索引對CF的影響
--對于組合索引，列的順序影響聚簇因子的大小
--我們建立如下組合索引
scott@SYBO2SZ> create index i_obj_name_id on t (object_name, object_id);
scott@SYBO2SZ> create index i_obj_id_name on t (object_id, object_name);
scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true)
PL/SQL procedure successfully completed.
scott@SYBO2SZ> @idx_stat
Enter value for input_table_name: T
Enter value for owner: SCOTT
AVG LEAF BLKS AVG DATA BLKS
BLEV IDX_NAME LEAF_BLKS DST_KEYS PER KEY PER KEY CLUST_FACT LAST_ANALYZED TB_BLKS TB_ROWS
---- ---------------- ---------- ---------- ------------- ------------- ---------- ----------------- ---------- ----------
1 I_OBJ_NAME 241 29476 1 1 675 20130418 17:17:17 695 48931
1 I_OBJ_ID 108 48931 1 1 24887 20130418 17:17:17 695 48931
1 I_OBJ_NAME_ID 274 48931 1 1 945 20130418 17:17:17 695 48931
1 I_OBJ_ID_NAME 274 48931 1 1 24887 20130418 17:17:18 695 48931
--從上面的結果可知，
--新建立的組合索引，I_OBJ_NAME_ID(object_name, object_id),object_name是前導列，是以CF值盡管比單列是大，依然表現良好。
--而索引I_OBJ_ID_NAME(object_id, object_name)，object_id作為前導列，CF值與單列索引I_OBJ_ID相同。
--上面的四個索引來看，無論是單列還是符合索引，當索引列(leaf)的順序接近于表上行的順序，CF表現良好。
d、反向索引對CF的影響
--反轉索引主要是重新分布索引值，也就是将相連比較緊密地索引鍵值分散到不同或相距比較遠的快上以避免競争。
--下面基于表t來新建立表t2
scott@SYBO2SZ> create table t2 nologging as select * from t;
scott@SYBO2SZ> create index i_obj_name_reverse on t2(object_name) reverse; -->建立反向索引
scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','T2',cascade=>true)
PL/SQL procedure successfully completed.
scott@SYBO2SZ> @idx_stat
Enter value for input_table_name: T2
Enter value for owner: SCOTT
AVG LEAF BLKS AVG DATA BLKS
BLEV IDX_NAME LEAF_BLKS DST_KEYS PER KEY PER KEY CLUST_FACT LAST_ANALYZED TB_BLKS TB_ROWS
---- ------------------ ---------- ---------- ------------- ------------- ---------- ----------------- ---------- ----------
1 I_OBJ_NAME_REVERSE 241 29476 1 1 28104 20130418 17:22:49 695 48931
--上面建立的反向索引的CF較之前的都要大，因索引鍵上的值是反向的，也就是說是無序的。
--在段空間管理基于手動管理的方式下，如果使用freelist可以避免段操作上DML的競争，但索引列上将具有較比較糟糕的聚簇因子(示範省略) 複制
8、實戰聚簇因子随DML變化的情形
a、建立示範環境
scott@SYBO2SZ> @cr_big_tb 1000000 -->建立一張百萬記錄的表
Table created.
scott@SYBO2SZ> @idx_stat -->檢視表與索引相關資訊(CF為14489,TB_BLKS為14652)
Enter value for input_table_name: BIG_TABLE
Enter value for owner: SCOTT
AVG LEAF BLKS AVG DATA BLKS
BLEV IDX_NAME LEAF_BLKS DST_KEYS PER KEY PER KEY CLUST_FACT LAST_ANALYZED TB_BLKS TB_ROWS
---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------
2 BIG_TABLE_PK 2088 1000000 1 1 14489 20130422 12:27:43 14652 999712
b、模拟DML操作
--建立一個臨時表來存儲将要從表big_table删除的記錄
scott@SYBO2SZ> create table big_table_tmp nologging as select * from big_table where id>=10000 and id<=200000;
scott@SYBO2SZ> delete from big_table nologging where id>=10000 and id<=200000; -->從表big_table删除一些記錄
scott@SYBO2SZ> commit;
-->檢視表與索引相關資訊(從下面的查詢結果可知，删除記錄并不使得CF發生變化)
scott@SYBO2SZ> @idx_stat
Enter value for input_table_name: BIG_TABLE
Enter value for owner: SCOTT
AVG LEAF BLKS AVG DATA BLKS
BLEV IDX_NAME LEAF_BLKS DST_KEYS PER KEY PER KEY CLUST_FACT LAST_ANALYZED TB_BLKS TB_ROWS
---- -------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------
2 BIG_TABLE_PK 2088 1000000 1 1 14489 20130422 12:27:43 14652 999712
scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true); -->收集統計資訊
scott@SYBO2SZ> @idx_stat -->檢視表與索引相關資訊(在收集統計資訊後，删除記錄後CF為11732,TB_BLKS依然為14652)
Enter value for input_table_name: BIG_TABLE --(TB_BLKS塊數未發生變化是因為空閑空間沒有釋放，需要shrink)
Enter value for owner: SCOTT
AVG LEAF BLKS AVG DATA BLKS
BLEV IDX_NAME LEAF_BLKS DST_KEYS PER KEY PER KEY CLUST_FACT LAST_ANALYZED TB_BLKS TB_ROWS
---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------
2 BIG_TABLE_PK 1692 809999 1 1 11732 20130422 12:31:45 14652 808497
-->接下來将删除的資料插入到big_table以模拟表上新增資料，分兩次插入，以使得id變得無序
scott@SYBO2SZ> insert into big_table nologging select * from big_table_tmp where id>=150000 and id<=200000
2 order by object_name;
scott@SYBO2SZ> insert into big_table nologging select * from big_table_tmp where id>=10000 and id<150000
2 order by object_name;
scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true); -->收集統計資訊
scott@SYBO2SZ> @idx_stat -->檢視表與索引相關資訊(此時CF的值由原來的14489增大到114256，呈數量級變化)
Enter value for input_table_name: BIG_TABLE
Enter value for owner: SCOTT
AVG LEAF BLKS AVG DATA BLKS
BLEV IDX_NAME LEAF_BLKS DST_KEYS PER KEY PER KEY CLUST_FACT LAST_ANALYZED TB_BLKS TB_ROWS
---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------
2 BIG_TABLE_PK 2088 1000000 1 1 114256 20130422 12:33:31 14652 998513
--下面嘗試move table是否對CF有向影響
scott@SYBO2SZ> alter table big_table move;
scott@SYBO2SZ> @idx_stat -->檢視表與索引相關資訊(move table之後，無任何變化)
Enter value for input_table_name: BIG_TABLE
Enter value for owner: SCOTT
AVG LEAF BLKS AVG DATA BLKS
BLEV IDX_NAME LEAF_BLKS DST_KEYS PER KEY PER KEY CLUST_FACT LAST_ANALYZED TB_BLKS TB_ROWS
---- -------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------
2 BIG_TABLE_PK 2088 1000000 1 1 114256 20130422 12:33:31 14652 998513
-->嘗試收集統計資訊後，在看CF的變化
-->下面的錯誤表明，move之後，索引失效
scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);
BEGIN dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true); END;
*
ERROR at line 1:
ORA-20000: index "SCOTT"."BIG_TABLE_PK" or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 13182
ORA-06512: at "SYS.DBMS_STATS", line 13202
ORA-06512: at line 1
scott@SYBO2SZ> alter index big_table_pk rebuild nologging; ---->重建索引
scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true); -->再次收集統計資訊
PL/SQL procedure successfully completed.
scott@SYBO2SZ> @idx_stat -->重建索引後，CF的值反而增大了
Enter value for input_table_name: BIG_TABLE
Enter value for owner: SCOTT
AVG LEAF BLKS AVG DATA BLKS
BLEV IDX_NAME LEAF_BLKS DST_KEYS PER KEY PER KEY CLUST_FACT LAST_ANALYZED TB_BLKS TB_ROWS
---- ------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------
2 BIG_TABLE_PK 2088 1000000 1 1 118384 20130422 12:36:31 14649 999427
c、重建big_table
-->下面通過重建big_table來縮小CF的值，新的表名為big_table_tmp
scott@SYBO2SZ> drop table big_table_tmp purge; --->删除之前的臨時表
scott@SYBO2SZ> create table big_table_tmp nologging as select * from big_table order by id;
scott@SYBO2SZ> create unique index big_table_tmp_pk on big_table_tmp(id);
scott@SYBO2SZ> alter table big_table_tmp add constraint big_table_tmp_pk primary key(id) using index big_table_tmp_pk;
scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE_TMP',cascade=>true);
scott@SYBO2SZ> @idx_stat --->表big_table_tmp上的CF值(14486)小于原始的CF值(14489)
Enter value for input_table_name: big_table_tmp
Enter value for owner: scott
--Author : Robinson
--Blog : http://blog.csdn.net/robinson_0612
AVG LEAF BLKS AVG DATA BLKS
BLEV IDX_NAME LEAF_BLKS DST_KEYS PER KEY PER KEY CLUST_FACT LAST_ANALYZED TB_BLKS TB_ROWS
---- ---------------- ---------- ---------- ------------- ------------- ---------- ------------------ ---------- ----------
2 BIG_TABLE_TMP_PK 2088 1000000 1 1 14486 20130422 12:38:37 14649 995891
d、比較不同的CF對查詢性能的影響
-->下面來基于表big_table與big_table_tmp來比較一下不同的CF對查詢的影響
scott@SYBO2SZ> set autot trace;
scott@SYBO2SZ> select * from big_table where id between 10000 and 15000;
5001 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3747652938
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5001 | 478K| 606 (0)| 00:00:08 |
| 1 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 5001 | 478K| 606 (0)| 00:00:08 |
|* 2 | INDEX RANGE SCAN | BIG_TABLE_PK | 5001 | | 13 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=10000 AND "ID"<=15000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2993 consistent gets
531 physical reads
116 redo size
287976 bytes sent via SQL*Net to client
4155 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5001 rows processed
--原始表上的查詢的cost為606, consistent gets與physical reads分别為2993,531
scott@SYBO2SZ> select * from big_table_tmp where id between 10000 and 15000;
5001 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1127920103
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4982 | 476K| 86 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE_TMP | 4982 | 476K| 86 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | BIG_TABLE_TMP_PK | 4982 | | 13 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=10000 AND "ID"<=15000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
750 consistent gets
76 physical reads
0 redo size
287976 bytes sent via SQL*Net to client
4155 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5001 rows processed
--新建立的表的cost 為86, consistent gets與physical reads分别為750,76. 呈數量級低低于原表的開銷
-->可以将原始表big_table上的資料删除(truncate)，删除前禁用依賴于該表的所有限制，然後将big_table_tmp的資料使用order by插入到big_table
-->注上面的create table as ..方式并不适合用于生産環境的真實操作，因為表上的一些屬性會被忽略掉. 複制
9、小結
a、任意情形下(堆表)，表上資料的存儲隻能按照一種特定的順序進行存儲。
b、由上面的特性決定了表上的隻有一個特定的索引列(單索引或組合索引)具有最佳的CF值。
c、索引的建立應考慮按應該按照經常頻繁讀取的大範圍資料的讀取順序來建立索引，以保證得到最佳的CF值。
d、索引在被建立之時，基于該索引列上的CF值即被産生，但表上的DML操作後需要收集統計資訊才可以更新CF的值。
e、基于表上頻繁的DML操作，尤其是delete後再新增記錄，可用空閑空間被填充，将使得CF的值呈增大趨勢。
f、alter table move tabname并不會影響CF的值，該功能隻是移動高水位線，且不釋放空間。
g、重建索引對CF的值收效甚微，因為原始表資料存儲順序未發生根本變化。
h、CF的值是影響查詢分析器對執行計劃的評估與生成的因素之一(即是否走索引還是全表掃描，嵌套連接配接時哪個表為驅動表等)。
i、通過重建表或使用聚簇表來改進CF的值，建議将原始表資料填充到臨時表，禁用依賴于該表的所有限制後truncate該表，再從臨時表導回資料(按順序)，啟用限制。
j、不推薦使用create table as select(CTAS)，因為表上的一些特性會被忽略，可以用SQL*Plus copy方式來實作。具體參考: 當心 CREATE TABLE AS