本文任何觀點為作者觀點，水平有限難免有誤
關於組合索引不需要多談就是多個列一起建立的索引，關於組合索引很常見的一個問題就是當謂詞中出現了前導列才能夠使用索引，如果
沒有出現前導列是不能使用索引，當然index skip scan和index full scan除外。
理論如此，但是為什麼謂詞中沒有前導列就不能使用索引，接下來透過DUMP來看看組合索引如何存放資料
建立測試表，為了方便論述和區別這裡使用全數字同時組合索引的兩個列完全是反序的:
create table testt1 (id1 number(10),id2 number(10),name varchar2(10));

declare
i number(10);
a number(10);
begin
for i in 1..100000
loop
select 100000-i into a from dual;
insert into testt1
values(i,a,'gaopeng');
end loop;
end;

create index testt1_c on testt1(id1,id2);

首先找到其OBJECT_ID，進行DUMP 索引結構

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/ora11g/diag/rdbms/test/test/trace/test_ora_14088.trc
SQL> alter session set events 'immediate trace name treedump level 76511';

檢視其結構：
branch: 0x1000a43 16779843 (0: nrow: 290, level: 1)
leaf: 0x1000a44 16779844 (-1: nrow: 364 rrow: 364)
leaf: 0x1000a45 16779845 (0: nrow: 359 rrow: 359)
leaf: 0x1000a46 16779846 (1: nrow: 359 rrow: 359)
..................
leaf: 0x1000a6d 16779885 (38: nrow: 342 rrow: 342)
leaf: 0x1000a6e 16779886 (39: nrow: 342 rrow: 342)
leaf: 0x1000a6f 16779887 (40: nrow: 342 rrow: 342)
leaf: 0x1000a71 16779889 (41: nrow: 342 rrow: 342)
..................
leaf: 0x1000c19 16780313 (139: nrow: 342 rrow: 342)
leaf: 0x1000c1a 16780314 (140: nrow: 342 rrow: 342)
leaf: 0x1000c1b 16780315 (141: nrow: 342 rrow: 342)
leaf: 0x1000c1c 16780316 (142: nrow: 342 rrow: 342)
..................
leaf: 0x1000cad 16780461 (285: nrow: 359 rrow: 359)
leaf: 0x1000cae 16780462 (286: nrow: 359 rrow: 359)
leaf: 0x1000caf 16780463 (287: nrow: 359 rrow: 359)
leaf: 0x1000cb0 16780464 (288: nrow: 221 rrow: 221)
限於篇幅這裡不能給出完整的DUMP，不過已經足夠說明問題
這裡可以看到本索引只有一個根節點然後就是葉節點
然後我們DUMP根節點：
進行DBA換算
SQL> select dbms_utility.data_block_address_file(16779843),
2 dbms_utility.data_block_address_block(16779843) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
4 2627
進行DUMP
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/ora11g/diag/rdbms/test/test/trace/test_ora_14103.trc
SQL> alter system dump datafile 4 block 2627;

System altered.
這裡去掉塊的CACHE LAYER,TRASACTION LAYER,TABLE DIRECTORY，剩下可以說明問題的部分
同時去頭去尾部，因為限於篇幅但是足夠說明問題
......
kdxbrlmc 16779844=0x1000a44
......
row#0[8047] dba: 16779845=0x1000a45
col 0; len 3; (3): c2 04 42
col 1; TERM
row#1[8038] dba: 16779846=0x1000a46
col 0; len 3; (3): c2 08 19
col 1; TERM
row#2[8029] dba: 16779847=0x1000a47
col 0; len 3; (3): c2 0b 54
col 1; TERM
row#3[8020] dba: 16779848=0x1000a48
col 0; len 3; (3): c2 0f 2b
col 1; TERM
.........
row#284[5234] dba: 16780460=0x1000cac
col 0; len 4; (4): c3 0a 54 2d
col 1; TERM
row#285[5224] dba: 16780461=0x1000cad
col 0; len 4; (4): c3 0a 58 04
col 1; TERM
row#286[5214] dba: 16780462=0x1000cae
col 0; len 4; (4): c3 0a 5b 3f
col 1; TERM
row#287[5204] dba: 16780463=0x1000caf
col 0; len 4; (4): c3 0a 5f 16
col 1; TERM
row#288[5194] dba: 16780464=0x1000cb0
col 0; len 4; (4): c3 0a 62 51
col 1; TERM
這裡可以看到這裡排列是按照ID1進行的升序的排列，而根節點中壓根就沒有ID2，這個可以透過，查詢索引狀態看到是升序的
COLUMN_NAME DESCEND
-------------------------------------------------------------------------------- -------
ID1 ASC
ID2 ASC
對開頭和結尾的2個頁節點的值進行分析，這個值實際上葉節點的開始位置：
row#0[8047] dba: 16779845=0x1000a45
col 0; len 3; (3): c2 04 42
col 1; TERM
row#288[5194] dba: 16780464=0x1000cb0
col 0; len 4; (4): c3 0a 62 51
col 1; TERM

c2 04 42=(4-1)*100^(2-1)+(66-1)*100^(1-1)=300+65=365
c3 0a 62 51=(10-1)*100^(2-0)+(98-1)*100^(2-1)+(81-1)*100^(2-2)=99780
因為索引是排序好的所以這裡快16779845的啟始值是365，而結束值（不包含）是16779846的開始值
而16780464作為最後一個塊99780是其起始值，結束值就是最後。

如果注意到COL 1這裡是TERM，實際上非葉節點會有此標示

接下來我們對16779845和16780464塊進行DUMP看看組合索引到底如何儲存的,這裡的16779845實際是索引葉節點的第二個塊，第一個塊
實際上是kdxbrlmc 16779844=0x1000a44

16779845進行DUMP：
row#0[8014] flag: ------, lock: 0, len=18
col 0; len 3; (3): c2 04 42
col 1; len 4; (4): c3 0a 61 24
col 2; len 6; (6): 01 00 09 c7 00 1e
row#1[7996] flag: ------, lock: 0, len=18
col 0; len 3; (3): c2 04 43
col 1; len 4; (4): c3 0a 61 23
col 2; len 6; (6): 01 00 09 c7 00 1f
.......
row#358[1578] flag: ------, lock: 0, len=18
col 0; len 3; (3): c2 08 18
col 1; len 4; (4): c3 0a 5d 4e
col 2; len 6; (6): 01 00 09 c3 00 3a
可以看到這裡的第一個值是c2 04 42和根節點的DUMP出來的值是一致的，他們實際上是
ID1,ID2，ROWID的排列
16780464進行DUMP
row#0[8014] flag: ------, lock: 0, len=18
col 0; len 4; (4): c3 0a 62 51
col 1; len 3; (3): c2 03 15
col 2; len 6; (6): 01 00 0b 32 00 1e
row#1[7996] flag: ------, lock: 0, len=18
col 0; len 4; (4): c3 0a 62 52
col 1; len 3; (3): c2 03 14
col 2; len 6; (6): 01 00 0b 32 00 1f
...........
row#220[4161] flag: ------, lock: 0, len=14
col 0; len 2; (2): c3 0b
col 1; len 1; (1): 80
col 2; len 6; (6): 01 00 0b 32 00 fa
同樣的可以看到第一個值c3 0a 62 51和根節點的DUMP出來的值也是一致的。

那麼現在我們可以回答為什麼當謂詞中出現了前導列才能夠使用索引，如果
沒有出現前導列是不能使用索引了。
因為在根節點乃至分支節點中，壓根就沒有儲存非前導列的值，B-TREE的結構完全
取決於前導列。

接下來我們來回答另外一個問題，當前導列值相同的情況下，其他非前導列是否進行了排序
我們在檢視索引列資訊的時候有如下標示
COLUMN_NAME DESCEND
-------------------------------------------------------------------------------- -------
ID1 ASC
ID2 ASC
可以看到ID2也是升序排列的，接下來我們我們來進行驗證。
建立測試資料
create table testt2 (id1 number(10),id2 number(10),id3 number(10),name varchar2(10));

declare
i number(10);
begin
for i in 1..1000
loop
insert into testt2
values(i,round(dbms_random.value(1,5)),round(dbms_random.value(1,5)),'gaopeng');
insert into testt2
values(i,round(dbms_random.value(1,5)),round(dbms_random.value(1,5)),'gaopeng');
insert into testt2
values(i,round(dbms_random.value(1,5)),round(dbms_random.value(1,5)),'gaopeng');
insert into testt2
values(i,round(dbms_random.value(1,5)),round(dbms_random.value(1,5)),'gaopeng');
insert into testt2
values(i,round(dbms_random.value(1,5)),round(dbms_random.value(1,5)),'gaopeng');
insert into testt2
values(i,round(dbms_random.value(1,5)),round(dbms_random.value(1,5)),'gaopeng');
insert into testt2
values(i,round(dbms_random.value(1,5)),round(dbms_random.value(1,5)),'gaopeng');
insert into testt2
values(i,round(dbms_random.value(1,5)),round(dbms_random.value(1,5)),'gaopeng');
insert into testt2
values(i,round(dbms_random.value(1,5)),round(dbms_random.value(1,5)),'gaopeng');
insert into testt2
values(i,round(dbms_random.value(1,5)),round(dbms_random.value(1,5)),'gaopeng');
end loop;
end;

create index testt2_c on testt2(id1,id2,id3);

如此我們對每個資料都插入10次，同時ID2，ID3取隨機值，然後建立ID1，ID2，ID3的聯合索引。
我們取出其中一個片段進行分析
SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_row_number(rowid),testt2.*from testt2 where id1=5 order by dbms_rowid.rowid_row_number(rowid);
DBMS_ROWID.ROWID_BLOCK_NUMBER( DBMS_ROWID.ROWID_ROW_NUMBER(RO ID1 ID2 ID3 NAME
------------------------------ ------------------------------ ----------- ----------- ----------- ----------
3014 40 5 1 2 gaopeng
3014 41 5 4 4 gaopeng
3014 42 5 3 4 gaopeng
3014 43 5 4 2 gaopeng
3014 44 5 3 3 gaopeng
3014 45 5 2 2 gaopeng
3014 46 5 5 3 gaopeng
3014 47 5 4 1 gaopeng
3014 48 5 5 3 gaopeng
3014 49 5 4 4 gaopeng
可以看到在塊中ID2和ID3的資料排列實際上是雜亂無章的。本來嘛就是隨機輸出的。
但是如果我們不用dbms_rowid.rowid_row_number(rowid)排序得出的結果如下：
SQL> select * from testt2 where id1=5;
ID1 ID2 ID3 NAME
----------- ----------- ----------- ----------
5 1 2 gaopeng
5 2 2 gaopeng
5 3 3 gaopeng
5 3 4 gaopeng
5 4 1 gaopeng
5 4 2 gaopeng
5 4 4 gaopeng
5 4 4 gaopeng
5 5 3 gaopeng
5 5 3 gaopeng
大概分析為什麼得到這樣的結果呢，ID2都是排序好的 ID3也是ID2相同值的排序，猶如ORDER BY ID1,ID2,ID3
，其實沒什麼奇怪的這裡ID1=5走了索引掃描，索引掃描是排序好了，同時這也從側面反映了一個事實，
當ID1相同的情況ID2排序，ID2相同的情況下ID3排序，可以看看如下的執行計劃，這裡根本就沒有ORDER的執行步驟
說明排序已經完成消除。

記住堆表示不排序的，任何時候都不，索引是排序的，當透過索引ROWID回表取資料的時候當然也就排好了順序。

接下來我們還是進行一次DUMP說明，透過DUMP取出關於ID1=5的記錄
row#40[7294] flag: ------, lock: 0, len=18
col 0; len 2; (2): c1 06
col 1; len 2; (2): c1 02
col 2; len 2; (2): c1 03
col 3; len 6; (6): 01 00 0b c6 00 28
row#41[7276] flag: ------, lock: 0, len=18
col 0; len 2; (2): c1 06
col 1; len 2; (2): c1 03
col 2; len 2; (2): c1 03
col 3; len 6; (6): 01 00 0b c6 00 2d
row#42[7258] flag: ------, lock: 0, len=18
col 0; len 2; (2): c1 06
col 1; len 2; (2): c1 04
col 2; len 2; (2): c1 04
col 3; len 6; (6): 01 00 0b c6 00 2c
row#43[7240] flag: ------, lock: 0, len=18
col 0; len 2; (2): c1 06
col 1; len 2; (2): c1 04
col 2; len 2; (2): c1 05
col 3; len 6; (6): 01 00 0b c6 00 2a
row#44[7222] flag: ------, lock: 0, len=18
col 0; len 2; (2): c1 06
col 1; len 2; (2): c1 05
col 2; len 2; (2): c1 02
col 3; len 6; (6): 01 00 0b c6 00 2f
row#45[7204] flag: ------, lock: 0, len=18
col 0; len 2; (2): c1 06
col 1; len 2; (2): c1 05
col 2; len 2; (2): c1 03
col 3; len 6; (6): 01 00 0b c6 00 2b
row#46[7186] flag: ------, lock: 0, len=18
col 0; len 2; (2): c1 06
col 1; len 2; (2): c1 05
col 2; len 2; (2): c1 05
col 3; len 6; (6): 01 00 0b c6 00 29
row#47[7168] flag: ------, lock: 0, len=18
col 0; len 2; (2): c1 06
col 1; len 2; (2): c1 05
col 2; len 2; (2): c1 05
col 3; len 6; (6): 01 00 0b c6 00 31
row#48[7150] flag: ------, lock: 0, len=18
col 0; len 2; (2): c1 06
col 1; len 2; (2): c1 06
col 2; len 2; (2): c1 04
col 3; len 6; (6): 01 00 0b c6 00 2e
row#49[7132] flag: ------, lock: 0, len=18
col 0; len 2; (2): c1 06
col 1; len 2; (2): c1 06
col 2; len 2; (2): c1 04
col 3; len 6; (6): 01 00 0b c6 00 30
這已經不用太多分析透過換算實際上他的順序就是
ID1 ID2 ID3
----------- ----------- -----------
5 1 2
5 2 2
5 3 3
5 3 4
5 4 1
5 4 2
5 4 4
5 4 4
5 5 3
5 5 3
如此我們又證明的一個問題當前導列相同的情況下組合索引的其他的列實際上
按照ORDER的方式進行排序實際上就是order by col1,col2,col3
,可以猜想這樣的構架為index skip scan提供了可能，在索引跳躍掃描的情況下
索引實際上被分為多個按照前導列分割的多個片段，然後進行分別掃描，因為是
後續的列是排序好的，這樣代價就大大減少，在崔華的基於ORACLE的SQL最佳化一書
中也描述為對前導列做DISTINCT值遍歷，可以想象這樣的方式只適合前導列不同值
很少，而且非前導列選擇率高的情況。

最後我們進行總結：
1、因為在根節點乃至分支節點中，壓根就沒有儲存非前導列的值，B-TREE的結構完全
取決於前導列，所以普通索引掃描依賴前導列來透過B-TREE結構進行快速定位搜尋
2、當前導列相同的情況下組合索引的其他的列實際上按照ORDER的方式進行排序
實際上就是order by col1,col2,col3
3、index skip scan提供了可能，在索引跳躍掃描的情況下
索引實際上被分為多個按照前導列分割的多個片段，然後進行分別掃描，因為是
後續的列是排序好的，這樣代價就大大減少
4、由於這樣一種結構透過索引回表後的資料是排序好的，為透過索引訪問回表消除排序提供了支援

關於ORACLE組合索引內部儲存淺談

相關文章