oracle 叢集因子

叢集因子(CLUSTERING FACTOR)

索引idx_id的葉子塊中有序的存儲了索引的鍵值以及鍵值對應行所在的ROWID：

示例：

create table scott.test as select * from dba_object;

create index scott.idx_object_id on scott.test(object_id);

SCOTT@PROD>select owner,index_name,table_name,clustering_factor from dba_indexes where table_name='TEST';

OWNER INDEX_NAME TABLE_NAME CLUSTERING_FACTOR

SCOTT IDX_OBJECT_ID TEST 1192

SCOTT@PROD>select count(*) from scott.test;

*** 索引idx_id的葉子塊中有序的存儲了索引的鍵值以及鍵值對應行所在的ROWID(預設情況下索引是升序的)：

SCOTT@PROD> select * from (

2 select object_id, rowid

3 from test

4 where object_id is not null

5 order by object_id) where rownum<=5;

OBJECT_ID ROWID

ROWID:6363 對象号-檔案号-塊号-行号

rowid 是僞列， oracle 專用的虛拟列，每個表的每一行都有 rowid。

每一行的 rowid 是全局唯一的。（行的×××号）

包括：所在表的對象号 資料檔案号 塊号 塊行号

6 位對象号， 3 位檔案号， 6 位塊号， 3 位行号

叢集因子的算法如下：

首先比較2,3 對應的ROWID是否在同一個資料塊，如果在同一個資料塊，Clustering Factor +0,如果不在同一個資料塊，那麼Clustering Factor +1。

然後比較3,4對應的ROWID是否在同一個資料塊，如果在同一個資料塊，Clustering Factor +0,如果不在同一個資料塊，那麼Clustering Factor +1。

再然後比較4,5對應的ROWID是否在同一個資料塊，如果在同一個資料塊，Clustering Factor +0,如果不在同一個資料塊，那麼Clustering Factor +1。

結論：

如果叢集因子接近于塊數，說明表的資料基本上是有序的，而且其順序基本與索引順序一樣。這樣在索引範圍或者索引全掃描的時候，

回表隻需要讀取少量的資料塊就能完成。

如果叢集因子接近表記錄數，說明表的資料和索引順序差異很大，在索引範圍掃描或者索引全掃描的時候，回表會讀取更多的資料塊。

思考？索引唯一掃描(index unique scan) 受不受 叢集因子 影響？

下面是根據叢集因子算法，人工計算叢集因子的SQL腳本

select sum(case

when block#1 = block#2 and file#1 = file#2 then

else

1

end) CLUSTERING_FACTOR

from (select dbms_rowid.rowid_relative_fno(rowid) file#1,

lead(dbms_rowid.rowid_relative_fno(rowid), 1, null) over(order by object_id) file#2,

dbms_rowid.rowid_block_number(rowid) block#1,

lead(dbms_rowid.rowid_block_number(rowid), 1, null) over(order by object_id) block#2

from test

where object_id is not null);

SCOTT@PROD>select sum(case

2 when block#1 = block#2 and file#1 = file#2 then

3 0

4 else

5 1

6 end) CLUSTERING_FACTOR

7 from (select dbms_rowid.rowid_relative_fno(rowid) file#1,

8 lead(dbms_rowid.rowid_relative_fno(rowid), 1, null) over(order by object_id) file#2,

10 lead(dbms_rowid.rowid_block_number(rowid), 1, null) over(order by object_id) block#2

11 from test

12 where object_id is not null);

和上面的一樣

表的總塊數為：

select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from scott.test;

SCOTT@PROD>select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from scott.test;

叢集因子非常接近表的總塊兒數的

實驗：

set arraysize 5000

set autot trace

select * from scott.test where object_id < 1000;

SCOTT@PROD>select * from scott.test where object_id < 1000;

997 rows selected.

Plan hash value: 985375477

2 - access("OBJECT_ID"<1000)

解析：傳回997條資料，(index range scan) 傳回一條資料要回表一次，也就意味着要回表997次，但是邏輯讀consistent gets(21個)

因為叢集因子很小，是以它回表邏輯讀很小。

實驗：現在建立一個測試表test2，并且對資料随機排序：

SCOTT@PROD>select owner,index_name,table_name,clustering_factor from dba_indexes where owner='SCOTT' and table_name='TEST2';

SCOTT IDX_ID2 TEST2 75209

叢集因子非常大，接近于表的總行數(75275)

再來看看相同的SQL，它的執行執行計劃還有邏輯讀...

select * from scott.test2 where object_id < 1000;

SCOTT@PROD>set arraysize 5000

SCOTT@PROD>set autot trace

SCOTT@PROD>select * from scott.test2 where object_id < 1000;

Plan hash value: 300966803

1 - filter("OBJECT_ID"<1000)

dynamic sampling used for this statement (level=2)

解析：同樣是傳回997條資料，但是它的邏輯讀(consistent gets 1177個)，差不多每一行都要對應一個塊兒。叢集因子越大它回表要更多的邏輯讀，以及實體讀

通過上面實驗得知，叢集因子太大會嚴重影響索引回表的性能。

叢集因子究竟影響的是什麼性能呢？叢集因子影響的是索引回表的實體I/O次數。假設索引範圍掃描傳回了1000行資料，

假設buffer cache中沒有緩存表的資料塊，假設這1000行資料都在同一個資料塊中，那麼回表需要耗費的實體I/O就隻需要一個，

假設這1000行資料都在不同的資料塊中，那麼回表就需要耗費1000個實體I/O。是以，叢集因子影響索引回表的實體I/O次數。

思考？ 

怎麼才能避免叢集因子對SQL查詢性能産生影響呢？其實前文已經有了答案，叢集因子隻影響索引範圍掃描和索引全掃描，

當索引範圍掃描，索引全掃描不回表或者傳回資料量很少的時候，不管叢集因子多大，對SQL查詢性能幾乎沒有任何影響。

再次強調一遍，在進行SQL優化的時候，往往會建立合适的組合索引消除回表，或者建立組合索引盡量減少回表次數。

如果無法避免回表，怎麼做才能消除回表對SQL查詢性能産生影響呢？當我們把表中所有的資料塊緩存在buffer cache中，

這個時候不管叢集因子多大，對SQL查詢性能也沒有多大影響，因為這時不需要實體I/O，資料塊全在記憶體中通路速度是非常快的。

本文轉自 wangergui 51CTO部落格，原文連結：http://blog.51cto.com/wangergui/2050046，如需轉載請自行聯系原作者