如何構建高BLEVEL的INDEX？

[i=s] 本帖最後由 wei-xh 於 2012-5-9 13:00 編輯

思考一個問題：有一個查詢select * from table where id=:1 and name=:2;
單從這一個SQL來看，該如何建立索引。ID區分度足夠好，NAME區分度稍微差點。
是ID,NAME好呢？還是NAME,ID好？為什麼？
實驗一把，看看。

CODE:

test@CRMD>create table test (id number ,name varchar2(4000));
Table created.
test@CRMD>insert into test select rownum,lpad('test',4000) from dba_source;
103602 rows created.
test@CRMD>commit;
Commit complete.上面建立了一張擁有103602條記錄的表。表的第一個欄位唯一，第二個欄位全部都是一樣的、4K個位元組。

CODE:

test@CRMD>create index t_1 on test(id,name);

Index created.

test@CRMD>create index t_2 on test(name,id);

Index created.建立了兩個索引。兩個索引的建立方式，唯一的區別就是鍵值的順序做了調換。
檢視統計資訊。

CODE:

test@CRMD>tabstat
Please enter Name of Table Owner: test
Please enter Table Name : test
Index                                      Leaf       Distinct         Number      AV      Av      Cluster Date
Name                           BLV         Blks           Keys        of Rows     LEA    Data       Factor MM-DD-YYYY
------------------------------ --- ------------ -------------- -------------- ------- ------- ------------ ----------
T_1                              2      103,602        103,602        103,602       1       1      103,602 05-08-2012
T_2                             11      103,602        103,602        103,602       1       1      103,602 05-08-2012
Index                          Column                          Col Column
Name                           Name                            Pos Details
------------------------------ ------------------------------ ---- ------------------------
T_1                            ID                                1 NUMBER(22)
                               NAME                              2 VARCHAR2(4000)
T_2                            NAME                              1 VARCHAR2(4000)
                               ID                                2 NUMBER(22)發現兩個索引的統計資訊除了BLEVEL（上面的BLV)外，其他的資訊完全一樣。而且BLEVEL出現了“天壤之別”。
T_1的BLV為2。
T_2的BLV為12。

記得我剛開始做DBA的時候，老師告訴我一條準則，就是兩個謂詞如果都做等值查詢，那麼區分度好的要放在前面，也就是distinct值大的放前面。當時我並不知道為什麼。
後面看LEWIS的COST成本演算法。
按照走索引的COST的演算法：
COST=索引成本+回表成本
索引成本=BLEVEL+BLOCKS*選擇率
回表成本=聚簇因子*表的有效選擇率

經過排除發現，貌似老師告訴我的準則是錯誤的，因為不管你建立的索引的鍵值順序是怎麼樣的，索引成本的選擇率和回表成本的表有效選擇率都是維持不變的。索引葉子的BLOCKS是不變的。聚簇因子雖然有可能不一樣，但是它完全依賴與索引相對與表的有序程度，與鍵值的區分度好壞完全沒關係。
那麼就差BLEVEL了。經過實驗發現BLEVEL確實是不一樣的。
為什麼呢？

CODE:

test@CRMD>COL OBJECT_NAME FOR A20
test@CRMD>select object_name,object_id from dba_objects where object_name in ('T_1','T_2');

OBJECT_NAME OBJECT_ID
-------------------- ----------
T_1 10834
T_2 10835

test@CRMD>alter session set events 'immediate trace name treedump level 10834';


Session altered.

test@CRMD>alter session set events 'immediate trace name treedump level 10835';


Session altered.上面的步驟可以把索引的內部結果DUMP出來。方便我們找到分支節點的物理位置。我們對葉子節點的物理結構不關心，因為兩種情況下建立的索引，葉子節點數目是一樣的，儲存的內容也是一樣的，只是鍵值順序不一樣。但是之所以第二種方式建立的索引，BLEVEL高，就是因為分支節點的數目大大增多的緣故，因此我們透過比對分支節點儲存的內容，就能找到原因了。
檢視DUMP檔案：

CODE:

branch: 0x101980c 16881676 (0: nrow: 154, level: 2)--------------------------根節點地址
branch: 0x1019b03 16882435 (-1: nrow: 740, level: 1)-------------------------第一個分支節點的地址
leaf: 0x101980d 16881677 (-1: nrow: 1 rrow: 1)
leaf: 0x101980e 16881678 (0: nrow: 1 rrow: 1)上面的DUMP內容是第一個索引的，我只擷取了一小部分。第二個索引的DUMP內容我就不貼出來了。根據DUMP檔案，我們DUMP出具體的分支塊來看看裡面具體的儲存。

第一種方式建立的索引的分支塊DUMP.

CODE:

row#0[8047] dba: 16882436=0x1019b04
col 0; len 3; (3): c2 08 2b
col 1; TERM
row#1[8038] dba: 16882437=0x1019b05
col 0; len 3; (3): c2 08 2c
col 1; TERM
row#2[8029] dba: 16882438=0x1019b06
col 0; len 3; (3): c2 08 2d
col 1; TERM可以看到ORACLE只記錄了第一列的資料在分支塊裡，第二列的鍵值根本沒記錄！！！！

第二種方式建立的索引分支塊的DUMP.

CODE:

row#0[4046] dba: 16986126=0x103300e
col 0; len 4000; (4000):
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
省略非常長的內容
。
。
。
col 1; len 2; (2):  c1 03
col 2; TERM第二種方式建立的索引兩個列的內容都做了記錄。

到這裡基本就清楚了，ORACLE的分支塊裡，只記錄必需的索引鍵值內容，也就是具有足夠的區分度可以導航找到葉子塊就可以了。
對於我們這個例子，對於第一種建立索引的方式，由於第一列的值已經是唯一的了，具有足夠的區分度了，第二列就可以不在索引分支塊裡出現了。
而對於第二種建立索引的方式，由於第一列的值沒有區分度，只有把第二列也加進去才具有區分度，因此就導致分支塊裡要把兩個列的鍵值都加進去。
而我們的例子又比較的極端，第二列非常的大，有4K個位元組，就導致分支塊的數目非常的多，最終導致索引的BLEVEL非常的大，在我們的例子裡
在一個只有103602行記錄表的索引裡，索引的層數就達到了12層。
看來當年老師告訴我的準則是有效的：當兩個謂詞只做等值查詢，把區分度好的作為前導列建立索引比較好。但是可能這個準則並不是多麼的重要，因為畢竟我們這裡的例子太特殊了，第二個欄位非常的長，而且不具有任何區分度，可能實際我們的環境中，效果差異並不那麼大。

還有這個準則不是肯定有效的，因為聚簇因子的關係，如果欄位A的順序跟表的儲存順序極其一致，而欄位B的極其不一致，但是欄位A的區別度糟糕，欄位B的區別度非常好。這個時候，到底是A,B好還是B,A好，要經過測試。

如果你充分理解了上面所說的內容，可以看下，如果我對錶的資料做了如下的處理，那麼兩種方式建立的索引，還會有區別嗎？

CODE:

test@CRMD>truncate table test;

Table truncated.
test@CRMD>insert /*+ append */into test select rownum,rownum||lpad('test',3993) from dba_source;

103614 rows created.
test@CRMD>commit;

Commit complete.
test@CRMD>create index t_1 on test(id,name);
test@CRMD>create index t_2 on test(name,id);第二個欄位，還是4K個位元組，但是它的前導欄位的區分度是用ROWNUM填充的，也就是說，第二個欄位的前N個字元的區分度是唯一的。
在這種情況下，兩種方式建立的索引，就是一樣的，連分支塊裡存的內容也是一樣的，ORACLE只擷取了第二個欄位的前N個（ROWNUM佔的位元組數）就可以了

CODE:

sys@CRMD>@tabstat

Index                                      Leaf       Distinct         Number      AV      Av      Cluster Date
Name                           BLV         Blks           Keys        of Rows     LEA    Data       Factor MM-DD-YYYY
------------------------------ --- ------------ -------------- -------------- ------- ------- ------------ ----------
T_1                              2      103,614        103,614        103,614       1       1      103,614 05-09-2012
T_2                              2      103,614        103,614        103,614       1       1      103,614 05-09-2012
Index                          Column                          Col Column
Name                           Name                            Pos Details
------------------------------ ------------------------------ ---- ------------------------
T_1                            ID                                1 NUMBER(22)
                               NAME                              2 VARCHAR2(4000)
T_2                            NAME                              1 VARCHAR2(4000)
                               ID                                2 NUMBER(22)