联合索引知多少？trouble shooting on 联合索引
转自 http://blog.itpub.net/21818314/viewspace-693214
这是一个实验总结帖，包括联合索引的实验 和总结。
我们是以邮件的形式发送和回复的 所以只能隐去真实姓名 请见谅。
请自底到顶阅读 。
=============================================================
我X ，这个帖子好，前一段时间还因为判断数据的问题伤神呢，算来算去都算不明白。
发件人: A(运维部\DBA\XXX)
发送时间: 2011年3月10日 17:43
收件人: B（开发部\XXX）; C(运维部\DBA\XXX); D(运维部\DBA\XXX); DBA(数据库管理员组)
主题: 答复: 组合索引 小实验
还原ORACLE DUMP 的值
还原DUMP出来的数字
SQL> select dump(2000,16) from dual;
DUMP(2000,16)
------------------
Typ=2 Len=2: c2,15
SQL> declare n number;
2 begin
3 dbms_stats.convert_raw_value('c215',n);
4 dbms_output.put_line(n);
5 end;
6 /
2000
declare n number;
begin
dbms_stats.convert_raw_value('c215',n);
dbms_output.put_line(n);
end;
/
还原DUMP的字符串
SQL> select dump('robinson',16) from dual;
DUMP('ROBINSON',16)
-------------------------------------
Typ=96 Len=8: 72,6f,62,69,6e,73,6f,6e
SQL> declare n varchar2(2000);
2 begin
3 dbms_stats.convert_raw_value('726f62696e736f6e',n);
4 dbms_output.put_line(n);
5 end;
6 /
robinson
SQL> select dump('robinson') from dual;
DUMP('ROBINSON')
--------------------------------------------
Typ=96 Len=8: 114,111,98,105,110,115,111,110
其实这个时候DUMP出来的是以10进制显示的，也就是说114相对于72，也就是r,后面的字幕同样
SQL> select to_char(114,'xxx') from dual;
TO_C
----
72
SQL> declare n varchar2(2000);
2 begin
3 dbms_stats.convert_raw_value('72',n);
4 dbms_output.put_line(n);
5 end;
6 /
发件人: B（开发部\XXX）
发送时间: 2011年3月10日 17:40
收件人: A(运维部\DBA\XXX); C(运维部\DBA\XXX); D(运维部\DBA\XXX); DBA(数据库管理员组)
主题: 答复: 组合索引 小实验
其实这个吧就是leading column的问题，
由于后面的3列都是唯一的 所以ORACLE 认为 只需要取2列的column 就可以定位row id。
为了明确分清这一点 所以把N2 column 改为了varchar 这样在dump中就可以很清晰的看出。
create table test1(n1 number, n2 VARCHAR2(50), n3 number, n4 number)
create index t_i11 on test1(n1,n2,n3,n4)
create index t_i21 on test1(n1,n3,n4,n2)
所以 这是T1 的branch记录 其中并没有包含 n3 column 的数据
T1 INDEX
header address 46975231222860=0x2ab945f8644c
kdxcolev 1
kdxbr2urrc 0
row#0[7996] dba: 21264925=0x1447a1d
col 0; len 1; (1): 80
col 1; len 3; (3): 31 31 34 这个是很明显的字符型数据
col 2; TERM
row#1[7985] dba: 21264926=0x1447a1e
col 0; len 1; (1): 80
col 1; len 3; (3): 31 32 38
col 2; TERM
row#2[7974] dba: 21264927=0x1447a1f
col 0; len 1; (1): 80
col 1; len 3; (3): 31 34 32
col 2; TERM
T2 INDEX
*** 2011-03-10 17:54:19.632
----- begin tree dump
branch: 0x10d2b7c 17640316 (0: nrow: 61, level: 1)
leaf: 0x10d2b7f 17640319 (-1: nrow: 158 rrow: 158)
leaf: 0x1447a1d 21264925 (0: nrow: 155 rrow: 155)
kdxbrlmc 17640327=0x10d2b87
kdxbrsno 31
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8045] dba: 17640328=0x10d2b88
col 0; len 1; (1): 80
col 1; len 3; (3): c2 04 22 这个则是很明显的number 数据。
col 2; TERM
row#1[8034] dba: 17640325=0x10d2b85
col 0; len 1; (1): 80
col 1; len 3; (3): c2 07 36
col 2; TERM
row#2[8023] dba: 17640326=0x10d2b86
col 0; len 1; (1): 80
col 1; len 3; (3): c2 0a 4a
col 2; TERM
发件人: A(运维部\DBA\XXX)
发送时间: 2011年3月10日 17:04
收件人: C(运维部\DBA\XXX); B（开发部\XXX）; D(运维部\DBA\XXX); DBA(数据库管理员组)
主题: 答复: 组合索引 小实验
因为索引时有序的，INDEX RANGE SCAN ，会从小到大一个一个进行扫描，
猜想t_i2只进行一次扫描就完成（从 root>leaf>datablock 3 consistent gets）。
而t_i1需进行多次扫描：
两个索引的level 都是1 ，但是
T_I2 tree dump:
----- begin tree dump
branch: 0x28aa13c 42639676 (0: nrow: 35, level: 1)
leaf: 0x28aa13d 42639677 (-1: nrow: 299 rrow: 299)
leaf: 0x28aa13e 42639678 (0: nrow: 287 rrow: 287)
leaf: 0x28aa13f 42639679 (1: nrow: 287 rrow: 287)
leaf: 0x28aa140 42639680 (2: nrow: 287 rrow: 287)
leaf: 0x3416978 54618488 (3: nrow: 287 rrow: 287)
leaf: 0x3416979 54618489 (4: nrow: 287 rrow: 287)
leaf: 0x341697a 54618490 (5: nrow: 287 rrow: 287)
leaf: 0x341697b 54618491 (6: nrow: 287 rrow: 287)
leaf: 0x341697c 54618492 (7: nrow: 287 rrow: 287)
leaf: 0x341697d 54618493 (8: nrow: 287 rrow: 287)
leaf: 0x341697e 54618494 (9: nrow: 287 rrow: 287)
leaf: 0x341697f 54618495 (10: nrow: 287 rrow: 287)
leaf: 0x10d0f8a 17633162 (11: nrow: 287 rrow: 287)
leaf: 0x10d0f8b 17633163 (12: nrow: 287 rrow: 287)
leaf: 0x10d0f8c 17633164 (13: nrow: 287 rrow: 287)
*** 2011-03-10 16:44:38.865
leaf: 0x10d0f8d 17633165 (14: nrow: 287 rrow: 287)
leaf: 0x10d0f8e 17633166 (15: nrow: 287 rrow: 287)
leaf: 0x10d0f8f 17633167 (16: nrow: 287 rrow: 287)
leaf: 0x10d0f90 17633168 (17: nrow: 287 rrow: 287)
leaf: 0x1447791 21264273 (18: nrow: 287 rrow: 287)
leaf: 0x1447792 21264274 (19: nrow: 287 rrow: 287)
leaf: 0x1447793 21264275 (20: nrow: 287 rrow: 287)
leaf: 0x1447794 21264276 (21: nrow: 287 rrow: 287)
leaf: 0x1447795 21264277 (22: nrow: 287 rrow: 287)
leaf: 0x1447796 21264278 (23: nrow: 287 rrow: 287)
leaf: 0x1447797 21264279 (24: nrow: 287 rrow: 287)
leaf: 0x1447798 21264280 (25: nrow: 287 rrow: 287)
leaf: 0x1849b5a 25467738 (26: nrow: 287 rrow: 287)
leaf: 0x1849b5b 25467739 (27: nrow: 287 rrow: 287)
leaf: 0x1849b5c 25467740 (28: nrow: 287 rrow: 287)
leaf: 0x1849b5d 25467741 (29: nrow: 287 rrow: 287)
leaf: 0x1849b5e 25467742 (30: nrow: 287 rrow: 287)
leaf: 0x1849b5f 25467743 (31: nrow: 287 rrow: 287)
leaf: 0x1849b60 25467744 (32: nrow: 287 rrow: 287)
leaf: 0x28aa141 42639681 (33: nrow: 230 rrow: 230)
----- end tree dump
T_I1 tree dump :
*** 2011-03-10 16:44:41.786
----- begin tree dump
branch: 0x1847504 25457924 (0: nrow: 32, level: 1)
leaf: 0x1847505 25457925 (-1: nrow: 332 rrow: 332)
leaf: 0x1847506 25457926 (0: nrow: 320 rrow: 320)
leaf: 0x1847507 25457927 (1: nrow: 320 rrow: 320)
leaf: 0x1847508 25457928 (2: nrow: 320 rrow: 320)
leaf: 0x28aa129 42639657 (3: nrow: 320 rrow: 320)
leaf: 0x28aa12a 42639658 (4: nrow: 320 rrow: 320)
leaf: 0x28aa12d 42639661 (5: nrow: 320 rrow: 320)
leaf: 0x28aa12e 42639662 (6: nrow: 320 rrow: 320)
leaf: 0x28aa12f 42639663 (7: nrow: 320 rrow: 320)
leaf: 0x28aa130 42639664 (8: nrow: 320 rrow: 320)
leaf: 0x28aa12b 42639659 (9: nrow: 320 rrow: 320)
leaf: 0x28aa12c 42639660 (10: nrow: 320 rrow: 320)
leaf: 0x341696c 54618476 (11: nrow: 320 rrow: 320)
leaf: 0x341696d 54618477 (12: nrow: 320 rrow: 320)
leaf: 0x341696e 54618478 (13: nrow: 320 rrow: 320)
leaf: 0x341696f 54618479 (14: nrow: 320 rrow: 320)
leaf: 0x3416969 54618473 (15: nrow: 320 rrow: 320)
leaf: 0x341696a 54618474 (16: nrow: 320 rrow: 320)
leaf: 0x341696b 54618475 (17: nrow: 320 rrow: 320)
leaf: 0x104e9f1 17099249 (18: nrow: 320 rrow: 320)
leaf: 0x104e9f2 17099250 (19: nrow: 320 rrow: 320)
leaf: 0x104e9f5 17099253 (20: nrow: 320 rrow: 320)
leaf: 0x104e9f6 17099254 (21: nrow: 320 rrow: 320)
leaf: 0x104e9f7 17099255 (22: nrow: 320 rrow: 320)
leaf: 0x104e9f8 17099256 (23: nrow: 320 rrow: 320)
leaf: 0x104e9f3 17099251 (24: nrow: 320 rrow: 320)
leaf: 0x104e9f4 17099252 (25: nrow: 320 rrow: 320)
leaf: 0x1446f25 21262117 (26: nrow: 320 rrow: 320)
leaf: 0x1446f26 21262118 (27: nrow: 320 rrow: 320)
leaf: 0x1446f27 21262119 (28: nrow: 320 rrow: 320)
leaf: 0x1446f28 21262120 (29: nrow: 320 rrow: 320)
leaf: 0x1446f22 21262114 (30: nrow: 68 rrow: 68)
----- end tree dump
row#30[7716] dba: 25467742=0x1849b5e
col 0; len 1; (1): 80
col 1; len 3; (3): c2 5a 0b
col 2; TERM
row#31[7705] dba: 25467743=0x1849b5f
col 0; len 1; (1): 80
col 1; len 3; (3): c2 5c 62
col 2; TERM
row#32[7694] dba: 25467744=0x1849b60
col 0; len 1; (1): 80
col 1; len 3; (3): c2 5f 55
col 2; TERM
row#33[7683] dba: 42639681=0x28aa141
col 0; len 1; (1): 80
col 1; len 3; (3): c2 62 48
col 2; TERM
----- end of branch block dump -----
End dump data blocks tsn: 4 file#: 10 minblk 696633 maxblk 696636
Block header dump: 0x01847504
Object id on Block? Y
seg/obj: 0x13f7f csc: 0x02.733b0d3f itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1847501 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000b.021.0001b43b 0x00c28adc.116e.02 C--- 0 scn 0x0002.733b0d3f
Branch block dump
=================
header address 47791955346508=0x2b776e85844c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 5
kdxcosdc 1
kdxconro 31
kdxcofbo 90=0x5a
kdxcofeo 7715=0x1e23
kdxcoavs 7625
kdxbrlmc 25457925=0x1847505
kdxbrsno 30
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8045] dba: 25457926=0x1847506
col 0; len 1; (1): 80
col 1; len 3; (3): c2 04 22
col 2; TERM
row#1[8034] dba: 25457927=0x1847507
col 0; len 1; (1): 80
col 1; len 3; (3): c2 07 36
col 2; TERM
row#2[8023] dba: 25457928=0x1847508
col 0; len 1; (1): 80
col 1; len 3; (3): c2 0a 4a
col 2; TERM
row#3[8012] dba: 42639657=0x28aa129
col 0; len 1; (1): 80
col 1; len 3; (3): c2 0d 5e
col 2; TERM
发件人: C(运维部\DBA\XXX)
发送时间: 2011年3月10日 16:25
收件人: B（开发部\XXX）; D(运维部\DBA\XX); DBA(数据库管理员组)
主题: 答复: 组合索引 小实验
Excellent test!
Thanks B
发件人: B（开发部\XXX）
发送时间: 2011年3月10日 16:15
收件人: D(运维部\DBA\XXX); DBA(XXX)
主题: 答复: 组合索引 小实验
JI guess column n1,n2 on the branch block for index t_i1 ,and n1,n3 on the branch of index_t2 .Cuz two columns can locate the rowid for this style(0,i,i,i).
does it？
发件人: D(运维部\DBA\XXX)
发送时间: 2011年3月10日 16:06
收件人: DBA(数据库管理员组)
主题: 组合索引 小实验
create table t(n1 number, n2 number, n3 number, n4 number);
create index t_i1 on t (n1,n2,n3,n4);
create index t_i2 on t (n1,n3,n4,n2);
Table has 10000 rows, row like ( 0, i ,i , i ) i = 1.. 10000
select * from t where n1=0 and n2 between 1 and 10000 and n3=1 and n4=1;
Use index t_i1
-- 37 buffer gets
Use index t_i2
-- 3 buffer gets
验证小实验
SQL> create table test(n1 number, n2 number, n3 number, n4 number);
Table created
SQL> create index t_i1 on test(n1,n2,n3,n4);
Index created
SQL> create index t_i2 on test (n1,n3,n4,n2);
Index created.
SQL> declare
2 v_cnt number := 0;
3 begin
4 for i in 1..10000 loop
5 insert into test values(0,i,i,i);
6 v_cnt := v_cnt + 1;
7 if mod(v_cnt,2000) = 0 then
8 commit;
9 end if;
10 end loop;
11 commit;
12 end;
13 /
PL/SQL procedure successfully completed
实验结论： 与ppt描述的十分吻合，而采用t_i1索引的cost比全表扫描还要高些
使用索引t_i1的执行代价
SQL> select /*+ index(test,t_i1) */* from test where n1=0 and n2 between 1 and 10000 and n3=1 and n4=1;
----------------------------------------------------------
0 recursive calls
0 db block gets
34 consistent gets
0 physical reads
0 redo size
720 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
全表扫描的代价
SQL> select /*+ full(test) */* from test where n1=0 and n2 between 1 and 10000 and n3=1 and n4=1;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 52 | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
720 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
使用索引t_i2的代价
SQL> select * from test where n1=0 and n2 between 1 and 10000 and n3=1 and n4=1;
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| T_I2 | 1 | 52 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads这是一个实验总结帖，包括联合索引的实验 和总结。
我们是以邮件的形式发送和回复的 所以只能隐去真实姓名 请见谅。
请自底到顶阅读 。
=============================================================
我X ，这个帖子好，前一段时间还因为判断数据的问题伤神呢，算来算去都算不明白。
发件人: A(运维部\DBA\XXX)
发送时间: 2011年3月10日 17:43
收件人: B（开发部\XXX）; C(运维部\DBA\XXX); D(运维部\DBA\XXX); DBA(数据库管理员组)
主题: 答复: 组合索引 小实验
还原ORACLE DUMP 的值
还原DUMP出来的数字
SQL> select dump(2000,16) from dual;
DUMP(2000,16)
------------------
Typ=2 Len=2: c2,15
SQL> declare n number;
2 begin
3 dbms_stats.convert_raw_value('c215',n);
4 dbms_output.put_line(n);
5 end;
6 /
2000
declare n number;
begin
dbms_stats.convert_raw_value('c215',n);
dbms_output.put_line(n);
end;
/
还原DUMP的字符串
SQL> select dump('robinson',16) from dual;
DUMP('ROBINSON',16)
-------------------------------------
Typ=96 Len=8: 72,6f,62,69,6e,73,6f,6e
SQL> declare n varchar2(2000);
2 begin
3 dbms_stats.convert_raw_value('726f62696e736f6e',n);
4 dbms_output.put_line(n);
5 end;
6 /
robinson
SQL> select dump('robinson') from dual;
DUMP('ROBINSON')
--------------------------------------------
Typ=96 Len=8: 114,111,98,105,110,115,111,110
其实这个时候DUMP出来的是以10进制显示的，也就是说114相对于72，也就是r,后面的字幕同样
SQL> select to_char(114,'xxx') from dual;
TO_C
----
72
SQL> declare n varchar2(2000);
2 begin
3 dbms_stats.convert_raw_value('72',n);
4 dbms_output.put_line(n);
5 end;
6 /
发件人: B（开发部\XXX）
发送时间: 2011年3月10日 17:40
收件人: A(运维部\DBA\XXX); C(运维部\DBA\XXX); D(运维部\DBA\XXX); DBA(数据库管理员组)
主题: 答复: 组合索引 小实验
其实这个吧就是leading column的问题，
由于后面的3列都是唯一的 所以ORACLE 认为 只需要取2列的column 就可以定位row id。
为了明确分清这一点 所以把N2 column 改为了varchar 这样在dump中就可以很清晰的看出。
create table test1(n1 number, n2 VARCHAR2(50), n3 number, n4 number)
create index t_i11 on test1(n1,n2,n3,n4)
create index t_i21 on test1(n1,n3,n4,n2)
所以 这是T1 的branch记录 其中并没有包含 n3 column 的数据
T1 INDEX
header address 46975231222860=0x2ab945f8644c
kdxcolev 1
kdxbr2urrc 0
row#0[7996] dba: 21264925=0x1447a1d
col 0; len 1; (1): 80
col 1; len 3; (3): 31 31 34 这个是很明显的字符型数据
col 2; TERM
row#1[7985] dba: 21264926=0x1447a1e
col 0; len 1; (1): 80
col 1; len 3; (3): 31 32 38
col 2; TERM
row#2[7974] dba: 21264927=0x1447a1f
col 0; len 1; (1): 80
col 1; len 3; (3): 31 34 32
col 2; TERM
T2 INDEX
*** 2011-03-10 17:54:19.632
----- begin tree dump
branch: 0x10d2b7c 17640316 (0: nrow: 61, level: 1)
leaf: 0x10d2b7f 17640319 (-1: nrow: 158 rrow: 158)
leaf: 0x1447a1d 21264925 (0: nrow: 155 rrow: 155)
kdxbrlmc 17640327=0x10d2b87
kdxbrsno 31
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8045] dba: 17640328=0x10d2b88
col 0; len 1; (1): 80
col 1; len 3; (3): c2 04 22 这个则是很明显的number 数据。
col 2; TERM
row#1[8034] dba: 17640325=0x10d2b85
col 0; len 1; (1): 80
col 1; len 3; (3): c2 07 36
col 2; TERM
row#2[8023] dba: 17640326=0x10d2b86
col 0; len 1; (1): 80
col 1; len 3; (3): c2 0a 4a
col 2; TERM
发件人: A(运维部\DBA\XXX)
发送时间: 2011年3月10日 17:04
收件人: C(运维部\DBA\XXX); B（开发部\XXX）; D(运维部\DBA\XXX); DBA(数据库管理员组)
主题: 答复: 组合索引 小实验
因为索引时有序的，INDEX RANGE SCAN ，会从小到大一个一个进行扫描，
猜想t_i2只进行一次扫描就完成（从 root>leaf>datablock 3 consistent gets）。
而t_i1需进行多次扫描：
两个索引的level 都是1 ，但是
T_I2 tree dump:
----- begin tree dump
branch: 0x28aa13c 42639676 (0: nrow: 35, level: 1)
leaf: 0x28aa13d 42639677 (-1: nrow: 299 rrow: 299)
leaf: 0x28aa13e 42639678 (0: nrow: 287 rrow: 287)
leaf: 0x28aa13f 42639679 (1: nrow: 287 rrow: 287)
leaf: 0x28aa140 42639680 (2: nrow: 287 rrow: 287)
leaf: 0x3416978 54618488 (3: nrow: 287 rrow: 287)
leaf: 0x3416979 54618489 (4: nrow: 287 rrow: 287)
leaf: 0x341697a 54618490 (5: nrow: 287 rrow: 287)
leaf: 0x341697b 54618491 (6: nrow: 287 rrow: 287)
leaf: 0x341697c 54618492 (7: nrow: 287 rrow: 287)
leaf: 0x341697d 54618493 (8: nrow: 287 rrow: 287)
leaf: 0x341697e 54618494 (9: nrow: 287 rrow: 287)
leaf: 0x341697f 54618495 (10: nrow: 287 rrow: 287)
leaf: 0x10d0f8a 17633162 (11: nrow: 287 rrow: 287)
leaf: 0x10d0f8b 17633163 (12: nrow: 287 rrow: 287)
leaf: 0x10d0f8c 17633164 (13: nrow: 287 rrow: 287)
*** 2011-03-10 16:44:38.865
leaf: 0x10d0f8d 17633165 (14: nrow: 287 rrow: 287)
leaf: 0x10d0f8e 17633166 (15: nrow: 287 rrow: 287)
leaf: 0x10d0f8f 17633167 (16: nrow: 287 rrow: 287)
leaf: 0x10d0f90 17633168 (17: nrow: 287 rrow: 287)
leaf: 0x1447791 21264273 (18: nrow: 287 rrow: 287)
leaf: 0x1447792 21264274 (19: nrow: 287 rrow: 287)
leaf: 0x1447793 21264275 (20: nrow: 287 rrow: 287)
leaf: 0x1447794 21264276 (21: nrow: 287 rrow: 287)
leaf: 0x1447795 21264277 (22: nrow: 287 rrow: 287)
leaf: 0x1447796 21264278 (23: nrow: 287 rrow: 287)
leaf: 0x1447797 21264279 (24: nrow: 287 rrow: 287)
leaf: 0x1447798 21264280 (25: nrow: 287 rrow: 287)
leaf: 0x1849b5a 25467738 (26: nrow: 287 rrow: 287)
leaf: 0x1849b5b 25467739 (27: nrow: 287 rrow: 287)
leaf: 0x1849b5c 25467740 (28: nrow: 287 rrow: 287)
leaf: 0x1849b5d 25467741 (29: nrow: 287 rrow: 287)
leaf: 0x1849b5e 25467742 (30: nrow: 287 rrow: 287)
leaf: 0x1849b5f 25467743 (31: nrow: 287 rrow: 287)
leaf: 0x1849b60 25467744 (32: nrow: 287 rrow: 287)
leaf: 0x28aa141 42639681 (33: nrow: 230 rrow: 230)
----- end tree dump
T_I1 tree dump :
*** 2011-03-10 16:44:41.786
----- begin tree dump
branch: 0x1847504 25457924 (0: nrow: 32, level: 1)
leaf: 0x1847505 25457925 (-1: nrow: 332 rrow: 332)
leaf: 0x1847506 25457926 (0: nrow: 320 rrow: 320)
leaf: 0x1847507 25457927 (1: nrow: 320 rrow: 320)
leaf: 0x1847508 25457928 (2: nrow: 320 rrow: 320)
leaf: 0x28aa129 42639657 (3: nrow: 320 rrow: 320)
leaf: 0x28aa12a 42639658 (4: nrow: 320 rrow: 320)
leaf: 0x28aa12d 42639661 (5: nrow: 320 rrow: 320)
leaf: 0x28aa12e 42639662 (6: nrow: 320 rrow: 320)
leaf: 0x28aa12f 42639663 (7: nrow: 320 rrow: 320)
leaf: 0x28aa130 42639664 (8: nrow: 320 rrow: 320)
leaf: 0x28aa12b 42639659 (9: nrow: 320 rrow: 320)
leaf: 0x28aa12c 42639660 (10: nrow: 320 rrow: 320)
leaf: 0x341696c 54618476 (11: nrow: 320 rrow: 320)
leaf: 0x341696d 54618477 (12: nrow: 320 rrow: 320)
leaf: 0x341696e 54618478 (13: nrow: 320 rrow: 320)
leaf: 0x341696f 54618479 (14: nrow: 320 rrow: 320)
leaf: 0x3416969 54618473 (15: nrow: 320 rrow: 320)
leaf: 0x341696a 54618474 (16: nrow: 320 rrow: 320)
leaf: 0x341696b 54618475 (17: nrow: 320 rrow: 320)
leaf: 0x104e9f1 17099249 (18: nrow: 320 rrow: 320)
leaf: 0x104e9f2 17099250 (19: nrow: 320 rrow: 320)
leaf: 0x104e9f5 17099253 (20: nrow: 320 rrow: 320)
leaf: 0x104e9f6 17099254 (21: nrow: 320 rrow: 320)
leaf: 0x104e9f7 17099255 (22: nrow: 320 rrow: 320)
leaf: 0x104e9f8 17099256 (23: nrow: 320 rrow: 320)
leaf: 0x104e9f3 17099251 (24: nrow: 320 rrow: 320)
leaf: 0x104e9f4 17099252 (25: nrow: 320 rrow: 320)
leaf: 0x1446f25 21262117 (26: nrow: 320 rrow: 320)
leaf: 0x1446f26 21262118 (27: nrow: 320 rrow: 320)
leaf: 0x1446f27 21262119 (28: nrow: 320 rrow: 320)
leaf: 0x1446f28 21262120 (29: nrow: 320 rrow: 320)
leaf: 0x1446f22 21262114 (30: nrow: 68 rrow: 68)
----- end tree dump
row#30[7716] dba: 25467742=0x1849b5e
col 0; len 1; (1): 80
col 1; len 3; (3): c2 5a 0b
col 2; TERM
row#31[7705] dba: 25467743=0x1849b5f
col 0; len 1; (1): 80
col 1; len 3; (3): c2 5c 62
col 2; TERM
row#32[7694] dba: 25467744=0x1849b60
col 0; len 1; (1): 80
col 1; len 3; (3): c2 5f 55
col 2; TERM
row#33[7683] dba: 42639681=0x28aa141
col 0; len 1; (1): 80
col 1; len 3; (3): c2 62 48
col 2; TERM
----- end of branch block dump -----
End dump data blocks tsn: 4 file#: 10 minblk 696633 maxblk 696636
Block header dump: 0x01847504
Object id on Block? Y
seg/obj: 0x13f7f csc: 0x02.733b0d3f itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1847501 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000b.021.0001b43b 0x00c28adc.116e.02 C--- 0 scn 0x0002.733b0d3f
Branch block dump
=================
header address 47791955346508=0x2b776e85844c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 5
kdxcosdc 1
kdxconro 31
kdxcofbo 90=0x5a
kdxcofeo 7715=0x1e23
kdxcoavs 7625
kdxbrlmc 25457925=0x1847505
kdxbrsno 30
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8045] dba: 25457926=0x1847506
col 0; len 1; (1): 80
col 1; len 3; (3): c2 04 22
col 2; TERM
row#1[8034] dba: 25457927=0x1847507
col 0; len 1; (1): 80
col 1; len 3; (3): c2 07 36
col 2; TERM
row#2[8023] dba: 25457928=0x1847508
col 0; len 1; (1): 80
col 1; len 3; (3): c2 0a 4a
col 2; TERM
row#3[8012] dba: 42639657=0x28aa129
col 0; len 1; (1): 80
col 1; len 3; (3): c2 0d 5e
col 2; TERM
发件人: C(运维部\DBA\XXX)
发送时间: 2011年3月10日 16:25
收件人: B（开发部\XXX）; D(运维部\DBA\XX); DBA(数据库管理员组)
主题: 答复: 组合索引 小实验
Excellent test!
Thanks B
发件人: B（开发部\XXX）
发送时间: 2011年3月10日 16:15
收件人: D(运维部\DBA\XXX); DBA(XXX)
主题: 答复: 组合索引 小实验
JI guess column n1,n2 on the branch block for index t_i1 ,and n1,n3 on the branch of index_t2 .Cuz two columns can locate the rowid for this style(0,i,i,i).
does it？
发件人: D(运维部\DBA\XXX)
发送时间: 2011年3月10日 16:06
收件人: DBA(数据库管理员组)
主题: 组合索引 小实验
create table t(n1 number, n2 number, n3 number, n4 number);
create index t_i1 on t (n1,n2,n3,n4);
create index t_i2 on t (n1,n3,n4,n2);
Table has 10000 rows, row like ( 0, i ,i , i ) i = 1.. 10000
select * from t where n1=0 and n2 between 1 and 10000 and n3=1 and n4=1;
Use index t_i1
-- 37 buffer gets
Use index t_i2
-- 3 buffer gets
验证小实验
SQL> create table test(n1 number, n2 number, n3 number, n4 number);
Table created
SQL> create index t_i1 on test(n1,n2,n3,n4);
Index created
SQL> create index t_i2 on test (n1,n3,n4,n2);
Index created.
SQL> declare
2 v_cnt number := 0;
3 begin
4 for i in 1..10000 loop
5 insert into test values(0,i,i,i);
6 v_cnt := v_cnt + 1;
7 if mod(v_cnt,2000) = 0 then
8 commit;
9 end if;
10 end loop;
11 commit;
12 end;
13 /
PL/SQL procedure successfully completed
实验结论： 与ppt描述的十分吻合，而采用t_i1索引的cost比全表扫描还要高些
使用索引t_i1的执行代价
SQL> select /*+ index(test,t_i1) */* from test where n1=0 and n2 between 1 and 10000 and n3=1 and n4=1;
----------------------------------------------------------
0 recursive calls
0 db block gets
34 consistent gets
0 physical reads
0 redo size
720 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
全表扫描的代价
SQL> select /*+ full(test) */* from test where n1=0 and n2 between 1 and 10000 and n3=1 and n4=1;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 52 | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
720 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
使用索引t_i2的代价
SQL> select * from test where n1=0 and n2 between 1 and 10000 and n3=1 and n4=1;
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| T_I2 | 1 | 52 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
720 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
0 redo size
720 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
