溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
索引統計信息中需要我們最為重點關注的是CLUSTERING_FACTOR(聚簇因子)。
在Oracle數據庫中，聚簇因子是指按照索引鍵值排序的索引行和存儲于對應表中數據行的存儲順序和相似度。Oracle是按照如下的算法來計算聚簇因子的值：
聚簇因子的初始值為1。
Oracle首先定位到目標索引處于最左邊的葉子塊。
從最左邊的葉子塊的第一個索引鍵值所在的索引行開始順序掃描，在順序掃描的過程中，Oracle會比對當前索引行的rowid和它之前的那個索引行(它們是相鄰的關系)的rowid，如果這兩個rowid并不是指向同一個表塊，那么Oracle就將聚簇因子的當前值遞增1；如果這兩個rowid是指向同一個表塊，Oracle就不改變聚簇因子的當前值。注意，這里Oracle在比對rowid時不需要回表去訪問相應的表塊。
上述比對rowid的過程會一直持續下去，直到順序掃描完目標索引所有葉子塊里的所有索引行。
上述順序掃描操作完成后，聚簇因子的當前值就是索引統計信息中的CLUSTERING_FACTOR，Oracle會將其存儲在數據字典里。
從上述聚簇因子的算法可以知道，如果聚簇因子的值接近對應表的表塊的數量，則說明目標索引索引行和存儲于對應表中數據行的存儲順序相似程度非常高。這也就意味著Oracle走索引范圍掃描后取得目標rowid再回表去訪問對應表塊的數據時，相鄰的索引行所對應的rowid極有可能處于同一個表塊中，即Oracle在通過索引行記錄的rowid回表第一次讀取對應的表塊并將該表塊緩存在buffer cache中后，當再通過相鄰索引行記錄的rowid回表第二次讀取對應的表塊時，就不需要再產生物理I/O了，因為這次要訪問的和上次已經訪問過的表塊是同一個塊，Oracle已經將其緩存在了buffer cache中。而如果聚簇因子的值接近對應表的記錄數，則說明目標索引索引行和存儲于對應表中數據行的存儲順序和相似程度非常低，這也就意味著Oracle走索引范圍掃描取得目標rowid再回表去訪問對應表塊的數據時，相鄰的索引行所對應的rowid極有可能不處于同一個表塊中，即Oracle在通過索引行記錄的rowid回表第一次去讀取對應的表塊并將表塊緩存在buffer cache中后，當再通過相鄰索引行記錄的rowid回表第二次讀取對應的表塊時，還需要再產生物理I/O，因為這次要訪問的和上次已經訪問過的表塊并不是同一個塊。
換句話說，聚簇因子高的索引走索引范圍掃描時比相同條件下聚簇因子低的索引要耗費更多的物理I/O，所以聚簇因子高的索引走索引范圍掃描的成本會比相同條件下聚簇因子低的索引走索引范圍掃描的成本高。
這里構造一個非常極端的例子，全索引中沒有任何相鄰的索引行記錄的rowid指向表中相同的數據塊:
根據上述聚簇因子的算法，我們可以算出此索引IDX_T1的聚簇因子的值應是20。
zx@MYDB>create table t1 (id number,name char(1200)); Table created. zx@MYDB>insert into t1 values(1,'1'); 1 row created. zx@MYDB>insert into t1 values(3,'3'); 1 row created. zx@MYDB>insert into t1 values(5,'5'); 1 row created. zx@MYDB>insert into t1 values(7,'7'); 1 row created. zx@MYDB>insert into t1 values(9,'9'); 1 row created. zx@MYDB>insert into t1 values(11,'11'); 1 row created. zx@MYDB>insert into t1 values(13,'13'); 1 row created. zx@MYDB>insert into t1 values(15,'15'); 1 row created. zx@MYDB>insert into t1 values(17,'17'); 1 row created. zx@MYDB>insert into t1 values(19,'19'); 1 row created. zx@MYDB>insert into t1 values(2,'2'); 1 row created. zx@MYDB>insert into t1 values(4,'4'); 1 row created. zx@MYDB>insert into t1 values(6,'6'); 1 row created. zx@MYDB>insert into t1 values(8,'8'); 1 row created. zx@MYDB>insert into t1 values(10,'10'); 1 row created. zx@MYDB>insert into t1 values(12,'12'); 1 row created. zx@MYDB>insert into t1 values(14,'14'); 1 row created. zx@MYDB>insert into t1 values(16,'16'); 1 row created. zx@MYDB>insert into t1 values(18,'18'); 1 row created. zx@MYDB>insert into t1 values(20,'20'); 1 row created. zx@MYDB>commit; Commit complete. zx@MYDB>create index idx_t1 on t1(id); Index created. zx@MYDB>col location for a10 zx@MYDB>select id,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location from t1 order by location,id; ID LOCATION ---------- ---------- 1 4_300 3 4_300 5 4_300 7 4_300 9 4_300 11 4_301 13 4_301 15 4_301 17 4_301 19 4_301 2 4_302 4 4_302 6 4_302 8 4_302 10 4_302 12 4_303 14 4_303 16 4_303 18 4_303 20 4_303 20 rows selected.
從上述顯示結果可以看出1、3、5、7、9在4號文件的300號數據塊內，11、13、15、17、19在4號文件的301號數據塊內，2、4、6、8、10在4號文件的第302號數據塊內，12、14、16、18、20在4號文件的第303號數據塊內。
收集統計信息并查看聚簇因子的值
#收集統計信息并查看聚簇因子的值 zx@MYDB>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T1',method_opt=>'for all columns size auto',cascade=>true,estimate_percent=>100); PL/SQL procedure successfully completed. zx@MYDB>select index_name,clustering_factor from dba_indexes where index_name='IDX_T1'; INDEX_NAME CLUSTERING_FACTOR ------------------------------------------------------------------------------------------ ----------------- IDX_T1 20
在Oracle數據庫中，能夠降低目標索引的聚簇因子的唯一方法就是對表中的數據按照目標索引的索引鍵值排序后重新存儲。需要注意的是，這種按某一個目標索引的索引鍵值排序后重新存儲表中數據的方法確實可以降低該目標索引聚簇因子的值 ，但可能會同時增加該表上存在的其他索引值的聚簇因子的值。
將表T1的數據原封不動的照搬到表T2中，只不過表T2的數據在存儲時已經按id列排好序了
zx@MYDB>create table t2 as select * from t1 order by id; Table created. zx@MYDB>create index idx_t2 on t2(id); Index created. zx@MYDB>select id,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location from t2 order by location,id; ID LOCATION ---------- ---------- 1 4_171 2 4_171 3 4_171 4 4_171 5 4_171 6 4_172 7 4_172 8 4_172 9 4_172 10 4_172 11 4_173 12 4_173 13 4_173 14 4_173 15 4_173 16 4_174 17 4_174 18 4_174 19 4_174 20 4_174 20 rows selected. zx@MYDB>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T1',method_opt=>'for all columns size auto',cascade=>true,estimate_percent=>100); PL/SQL procedure successfully completed. zx@MYDB>select index_name,clustering_factor from dba_indexes where index_name='IDX_T2'; INDEX_NAME CLUSTERING_FACTOR ------------------------------------------------------------------------------------------ ----------------- IDX_T2 4
重復與表T1相同的一系列的操作，從結果可以看出索引IDX_T2的聚簇因子降為了4。而相鄰的數據也都在同一數據塊中。
在Oracle數據庫里，CBO在計算索引范圍掃描(Index Range Scan)的成本時會使用如下公式：
IRS Cost = I/O Cost + CPU Cost
而I/O Cost的計算公式為：
I/O Cost = Index Access I/O Cost + Table Access I/O Cost
Index Access I/O Cost = BLEVEL + CEIL(#LEAF_BLOCKS * IX_SEL)
Table Access I/O Cost = CEIL(CLUSTERING_FACTOR * IX_SEL_WITH_FILTERS)
從這個公式可以推斷走索引范圍掃描的成本可以近似看作是和聚簇因子成正比，因此，聚簇因子值的大小實際上對CBO判斷是否走相關的索引起著至關重要的作用。
演示一個例子，通過修改聚簇索引的值就讓原本走索引范圍掃描的執行計劃變成了走全表掃描：
zx@MYDB>create table t1 as select * from dba_objects; Table created. zx@MYDB>create index idx_t1 on t1(object_id); Index created. zx@MYDB>select clustering_factor from dba_indexes where index_name='IDX_T1'; CLUSTERING_FACTOR ----------------- 1063 zx@MYDB>select /*+ cluster_factor_expmple_1 */ object_id,object_name from t1 where object_id between 103 and 108; OBJECT_ID OBJECT_NAME ---------- ------------------------------ 103 MIGRATE$ 104 DEPENDENCY$ 105 ACCESS$ 106 I_DEPENDENCY1 107 I_DEPENDENCY2 108 I_ACCESS1 6 rows selected. zx@MYDB>select * from table(dbms_xplan.display_cursor(null,null,'all')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID ga3jv3kwwwmx5, child number 0 ------------------------------------- select /*+ cluster_factor_expmple_1 */ object_id,object_name from t1 where object_id between 103 and 108 Plan hash value: 50753647 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 6 | 474 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T1 | 6 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------- ......省略部分輸出
SQL走了索引范圍掃描，成本值為3
使用Hint強制SQL走全表掃描：
zx@MYDB>select /*+ full(t1) */ object_id,object_name from t1 where object_id between 103 and 108; OBJECT_ID OBJECT_NAME ---------- ------------------------------ 103 MIGRATE$ 104 DEPENDENCY$ 105 ACCESS$ 106 I_DEPENDENCY1 107 I_DEPENDENCY2 108 I_ACCESS1 6 rows selected. zx@MYDB>select * from table(dbms_xplan.display_cursor(null,null,'all')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID b7hjwuvmg2ncy, child number 0 ------------------------------------- select /*+ full(t1) */ object_id,object_name from t1 where object_id between 103 and 108 Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 287 (100)| | |* 1 | TABLE ACCESS FULL| T1 | 6 | 474 | 287 (1)| 00:00:04 | -------------------------------------------------------------------------- ......省略部分輸出
現在SQL走全表掃描，成本值為287。
我們已經知道走索引范圍掃描的成本可以近似看作是和聚簇因子成正比，所以如果想讓上述SQL的執行計劃從索引范圍掃描變為全表掃描，那么只需要調整聚簇因子的值，使走索引范圍掃描的成本值大于走全表掃描的成本值346即可達到目的。
先將索引IDX_T1的聚簇因子的值手工調整為100萬：
zx@MYDB>exec dbms_stats.set_index_stats(ownname=>'ZX',indname=>'IDX_T1',clstfct=>1000000,no_invalidate=>false); PL/SQL procedure successfully completed. zx@MYDB>select clustering_factor from dba_indexes where index_name='IDX_T1'; CLUSTERING_FACTOR ----------------- 1000000 zx@MYDB>select /*+ cluster_factor_expmple_2 */ object_id,object_name from t1 where object_id between 103 and 108; OBJECT_ID OBJECT_NAME ---------- ------------------------------ 103 MIGRATE$ 104 DEPENDENCY$ 105 ACCESS$ 106 I_DEPENDENCY1 107 I_DEPENDENCY2 108 I_ACCESS1 6 rows selected. zx@MYDB>select * from table(dbms_xplan.display_cursor(null,null,'all')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 1ucqsj4j0j432, child number 0 ------------------------------------- select /*+ cluster_factor_expmple_2 */ object_id,object_name from t1 where object_id between 103 and 108 Plan hash value: 50753647 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 105 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 6 | 474 | 105 (0)| 00:00:02 | |* 2 | INDEX RANGE SCAN | IDX_T1 | 6 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------- ......省略部分輸出
從執行計劃中可以看出，成本已經由3變為105(即增加了102)，這說明我們對索引IDX_T1的聚簇因子的調整生效了。
要使成本值大于287，只需要把聚簇因子的值調整到400萬。
zx@MYDB>exec dbms_stats.set_index_stats(ownname=>'ZX',indname=>'IDX_T1',clstfct=>4000000,no_invalidate=>false); PL/SQL procedure successfully completed. zx@MYDB>select clustering_factor from dba_indexes where index_name='IDX_T1'; CLUSTERING_FACTOR ----------------- 4000000 zx@MYDB>select /*+ cluster_factor_expmple_3 */ object_id,object_name from t1 where object_id between 103 and 108; OBJECT_ID OBJECT_NAME ---------- ------------------------------ 103 MIGRATE$ 104 DEPENDENCY$ 105 ACCESS$ 106 I_DEPENDENCY1 107 I_DEPENDENCY2 108 I_ACCESS1 6 rows selected. zx@MYDB>select * from table(dbms_xplan.display_cursor(null,null,'all')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID cwkc8q61bypa6, child number 0 ------------------------------------- select /*+ cluster_factor_expmple_3 */ object_id,object_name from t1 where object_id between 103 and 108 Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 287 (100)| | |* 1 | TABLE ACCESS FULL| T1 | 6 | 474 | 287 (1)| 00:00:04 | --------------------------------------------------------------------------
從上面顯示的內容可以看出執行計劃從索引范圍掃描變成了全表掃描。
參考《基于Oracle的SQL優化》
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
