本帖最后由 sunyunyi 于 2018-11-10 10:51 编辑
作者简介:
----------------------------------------------------
@ 孙显鹏,海天起点oracle技术专家,十年从业经验,
@ 拥有OCP 11G认证,精通oracle内部原理,擅长调优
@ 和解决疑难问题,致力于帮助客户解决生产过程中出
@ 现的问题,提高生产效率。
@ 爱好书法,周易,中医!
@ 微信号:sunyunyi_sun
@ 电 话--18629679269
-----------------------------------------------------
0:准备环境和测试数据
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
conn to pluggable database :
alter pluggable database testpdb open;
sqlplus zfy/zfy@serdb:1521/testpdb
SQL> show con_name;
CON_NAME
------------------------------
TESTPDB
SQL> sho user;
USER is "ZFY"
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
这里我们首先建立一个测试表和复合索引,看看复合索引的内部存储:
create table tb_ti as select rownum id,'wolf' col0,'wits' col1 from dual connect by level<=10000;
create index idx_tb_ti on tb_ti(id,col0);
exec sys.dbms_stats.gather_table_stats ( OwnName => 'ZFY',TabName => 'TB_TI',Estimate_Percent => NULL,
Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);
HEADER_FILE HEADER_BLOCK
----------- ------------
13 100802
select object_name,object_id from dba_objects where object_name in ('TB_TI','IDX_TB_TI');
OBJECT_NAM OBJECT_ID
---------- ----------
IDX_TB_TI 125994
TB_TI 125993
alter session set events 'immediate trace name treedump level 125994';
*** 2018-03-02T18:04:04.853663+08:00 (TESTPDB(3))
----- begin tree dump
branch: 0x18189c3 25266627 (0: nrow: 28, level: 1)
leaf: 0x18189c4 25266628 (-1: row:364.364 avs:818)
leaf: 0x18189c5 25266629 (0: row:359.359 avs:820)
leaf: 0x18189c6 25266630 (1: row:359.359 avs:819)
leaf: 0x18189c7 25266631 (2: row:359.359 avs:820)
leaf: 0x18189c8 25266632 (3: row:359.359 avs:820)
leaf: 0x18189c9 25266633 (4: row:359.359 avs:819)
leaf: 0x18189ca 25266634 (5: row:359.359 avs:820)
leaf: 0x18189cb 25266635 (6: row:359.359 avs:819)
leaf: 0x18189cc 25266636 (7: row:359.359 avs:820)
leaf: 0x18189cd 25266637 (8: row:359.359 avs:819)
leaf: 0x18189ce 25266638 (9: row:359.359 avs:820)
leaf: 0x18189cf 25266639 (10: row:359.359 avs:820)
leaf: 0x18189d1 25266641 (11: row:359.359 avs:819)
leaf: 0x18189d2 25266642 (12: row:359.359 avs:820)
leaf: 0x18189d3 25266643 (13: row:359.359 avs:819)
leaf: 0x18189d4 25266644 (14: row:359.359 avs:820)
leaf: 0x18189d5 25266645 (15: row:359.359 avs:820)
leaf: 0x18189d6 25266646 (16: row:359.359 avs:819)
leaf: 0x18189d7 25266647 (17: row:359.359 avs:820)
leaf: 0x18189d8 25266648 (18: row:359.359 avs:819)
leaf: 0x18189d9 25266649 (19: row:359.359 avs:820)
leaf: 0x18189da 25266650 (20: row:359.359 avs:820)
leaf: 0x18189db 25266651 (21: row:359.359 avs:819)
leaf: 0x18189dc 25266652 (22: row:359.359 avs:820)
leaf: 0x18189dd 25266653 (23: row:359.359 avs:819)
leaf: 0x18189de 25266654 (24: row:359.359 avs:820)
leaf: 0x18189df 25266655 (25: row:359.359 avs:819)
leaf: 0x18189e1 25266657 (26: row:302.302 avs:1960)
----- end tree dump
首先分析枝节点
select to_number('189c3','xxxxxxx') from dual;
alter system dump datafile 13 block 100803
Block header dump: 0x018189c3
Object id on Block? Y
seg/obj: 0x1ec2a csc: 0x000000000207fb9b itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x18189c0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x000000000207fb9b
Branch block dump
=================
header address 139743629553740=0x7f189966304c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 0
kdxconro 27---这个枝节点包含27行,从row[0]--row[26],改索引总共28个叶
kdxcofbo 82=0x52
kdxcofeo 7813=0x1e85
kdxcoavs 7731
kdxbrlmc 25266628=0x18189c4
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8047] dba: 25266629=0x18189c5
col 0; len 3; (3): c2 04 42 --结合下面第一个叶节点的dump知道该值为第二个叶节点的最小值。
col 1; TERM --也就是说id<365,365
c2 04 42)在0x18189c5块的前面的叶块中,
--id>=365从叶块0x18189c5开始向右扫描。col1无意思。
row#1[8038] dba: 25266630=0x18189c6
col 0; len 3; (3): c2 08 19
col 1; TERM
row#2[8029] dba: 25266631=0x18189c7
col 0; len 3; (3): c2 0b 54
col 1; TERM
row#3[8020] dba: 25266632=0x18189c8
col 0; len 3; (3): c2 0f 2b
col 1; TERM
row#4[8011] dba: 25266633=0x18189c9
col 0; len 3; (3): c2 13 02
col 1; TERM
row#5[8002] dba: 25266634=0x18189ca
col 0; len 3; (3): c2 16 3d
col 1; TERM
row#6[7993] dba: 25266635=0x18189cb
col 0; len 3; (3): c2 1a 14
col 1; TERM
row#7[7984] dba: 25266636=0x18189cc
col 0; len 3; (3): c2 1d 4f
col 1; TERM
row#8[7975] dba: 25266637=0x18189cd
col 0; len 3; (3): c2 21 26
col 1; TERM
row#9[7966] dba: 25266638=0x18189ce
col 0; len 3; (3): c2 24 61
col 1; TERM
row#10[7957] dba: 25266639=0x18189cf
col 0; len 3; (3): c2 28 38
col 1; TERM
row#11[7948] dba: 25266641=0x18189d1
col 0; len 3; (3): c2 2c 0f
col 1; TERM
row#12[7939] dba: 25266642=0x18189d2
col 0; len 3; (3): c2 2f 4a
col 1; TERM
row#13[7930] dba: 25266643=0x18189d3
col 0; len 3; (3): c2 33 21
col 1; TERM
row#14[7921] dba: 25266644=0x18189d4
col 0; len 3; (3): c2 36 5c
col 1; TERM
row#15[7912] dba: 25266645=0x18189d5
col 0; len 3; (3): c2 3a 33
col 1; TERM
row#16[7903] dba: 25266646=0x18189d6
col 0; len 3; (3): c2 3e 0a
col 1; TERM
row#17[7894] dba: 25266647=0x18189d7
col 0; len 3; (3): c2 41 45
col 1; TERM
row#18[7885] dba: 25266648=0x18189d8
col 0; len 3; (3): c2 45 1c
col 1; TERM
row#19[7876] dba: 25266649=0x18189d9
col 0; len 3; (3): c2 48 57
col 1; TERM
row#20[7867] dba: 25266650=0x18189da
col 0; len 3; (3): c2 4c 2e
col 1; TERM
row#21[7858] dba: 25266651=0x18189db
col 0; len 3; (3): c2 50 05
col 1; TERM
row#22[7849] dba: 25266652=0x18189dc
col 0; len 3; (3): c2 53 40
col 1; TERM
row#23[7840] dba: 25266653=0x18189dd
col 0; len 3; (3): c2 57 17
col 1; TERM
row#24[7831] dba: 25266654=0x18189de
col 0; len 3; (3): c2 5a 52
col 1; TERM
row#25[7822] dba: 25266655=0x18189df
col 0; len 3; (3): c2 5e 29
col 1; TERM
row#26[7813] dba: 25266657=0x18189e1
col 0; len 3; (3): c2 61 64 --结合下面第27个叶节点的dump知道该值为第27个叶节点的最大键值+1。
col 1; TERM --也就是第28个也是最后一个叶节点id的最小值,大于该id的值在该叶中。
----- end of branch block dump -----
End dump data blocks tsn: 6 file#: 13 minblk 100803 maxblk 100803
然后打印第一个叶节点的信息:
select to_number('189c4','xxxxxxx') from dual;
alter system dump datafile 13 block 100804
Block header dump: 0x018189c4
Object id on Block? Y
seg/obj: 0x1ec2a csc: 0x000000000207fb9b itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x18189c0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x000000000207fb9b
Leaf block dump
===============
header address 139743629553764=0x7f1899663064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 0
kdxconro 364
kdxcofbo 764=0x2fc
kdxcofeo 1582=0x62e
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 25266629=0x18189c5
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8015] flag: -------, lock: 0, len=17
col 0; len 2; (2): c1 02
col 1; len 4; (4): 77 6f 6c 66
col 2; len 6; (6): 01 81 89 a3 00 00
row#1[7998] flag: -------, lock: 0, len=17
col 0; len 2; (2): c1 03
col 1; len 4; (4): 77 6f 6c 66
col 2; len 6; (6): 01 81 89 a3 00 01
row#2[7981] flag: -------, lock: 0, len=17
col 0; len 2; (2): c1 04
col 1; len 4; (4): 77 6f 6c 66
col 2; len 6; (6): 01 81 89 a3 00 02
row#3[7964] flag: -------, lock: 0, len=17
col 0; len 2; (2): c1 05
col 1; len 4; (4): 77 6f 6c 66
col 2; len 6; (6): 01 81 89 a3 00 03
row#4[7947] flag: -------, lock: 0, len=17
col 0; len 2; (2): c1 06
col 1; len 4; (4): 77 6f 6c 66
col 2; len 6; (6): 01 81 89 a3 00 04
。。。。。中间部分省略
row#362[1600] flag: -------, lock: 0, len=18
col 0; len 3; (3): c2 04 40
col 1; len 4; (4): 77 6f 6c 66
col 2; len 6; (6): 01 81 89 a3 01 6a
row#363[1582] flag: -------, lock: 0, len=18
col 0; len 3; (3): c2 04 41 ---该叶节点的最大值
col 1; len 4; (4): 77 6f 6c 66 ---复合索引的第二个键值
col 2; len 6; (6): 01 81 89 a3 01 6b ---数据块的地址和块内的位置
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 6 file#: 13 minblk 100804 maxblk 100804
BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
---------- ----------- ------------- ----------------------- ----------------------- -----------------
1 28 10000 1 1 27
该索引的BLEVEL=1那么也就是说根节点同时也是枝节点,
然后打印第27个叶节点的信息:
select to_number('189df','xxxxxxx') from dual;
alter system dump datafile 13 block 100831
0x18189df:
row#358[1573] flag: -------, lock: 0, len=18
col 0; len 3; (3): c2 61 63
col 1; len 4; (4): 77 6f 6c 66
col 2; len 6; (6): 01 81 89 bd 00 ab
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 6 file#: 13 minblk 100831 maxblk 100831
从上面的分析我们可以知道,复合索引的分支节点只存储一个键值的信息,叶节点中存在所有列的信息。
你可以依据上面的信息画出该复合索引的逻辑图,根节点就是分支节点里面存储了27个行,分别指向不同的叶。
接下来我们已经改索引看看不同的扫描方式:
先采取跳跃扫描:
set autotrace on;
update tb_ti set col0='sun' where id<123; commit;
select /*+ index_ss(a idx_tb_id)*/ a.col0 from tb_ti a where col0 ='sun'
Execution Plan
----------------------------------------------------------
Plan hash value: 1529110159
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 9 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_TB_TI | 1 | 5 | 9 (0)| 00:00:01 |
ORACLE 选择了索引快速全表扫描,并不是我们期待的跳跃扫描。
update tb_ti set id=1 where id<5000;
update tb_ti set id=2 where id>=5000;
commit;
select count( distinct id) from tb_ti;
COUNT(DISTINCTID)
-----------------
2
select a.col0 from tb_ti a where col0 ='sun'
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 9 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_TB_TI | 1 | 5 | 9 (0)| 00:00:01 |
ORACLE 还是选择了索引快速全表扫描;
接下来我们重新构造表结构:
drop table tb_ti;
create table tb_ti as select 'wolf' col0,rownum id,'wits' col1 from dual connect by level<=10000;
update tb_ti set col0='sun' where id<5000;
commit;
create index idx_tb_ti on tb_ti(col0,id);
select count( distinct col0) from tb_ti;
COUNT(DISTINCTCOL0)
-------------------
2
exec sys.dbms_stats.gather_table_stats ( OwnName => 'ZFY',TabName => 'TB_TI',Estimate_Percent => NULL,
Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);
select object_name,object_id from dba_objects where object_name in ('TB_TI','IDX_TB_TI');
OBJECT_NAM OBJECT_ID
---------- ----------
IDX_TB_TI 126003
TB_TI 126002
select col0,col1 from tb_ti a where id<123
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 122 | 1708 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TB_TI | 122 | 1708 | 4 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_TB_TI | 122 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
现在走了跳跃扫描,因为前导列的值较集中只有两个不同值,且第二个列值选择性很好,所以选择跳跃扫描,
从这里看出来跳跃扫描的方式,接下来我们验证实际oracle扫描那些块。
先打出索引结构:
alter session set events 'immediate trace name treedump level 126003';
----- begin tree dump
branch: 0x1818a4b 25266763 (0: nrow: 28, level: 1)
leaf: 0x1818a4c 25266764 (-1: row:364.364 avs:818)
leaf: 0x1818a4d 25266765 (0: row:359.359 avs:820)
leaf: 0x1818a4e 25266766 (1: row:359.359 avs:819)
leaf: 0x1818a4f 25266767 (2: row:359.359 avs:820)
leaf: 0x1818a50 25266768 (3: row:359.359 avs:820)
leaf: 0x1818a51 25266769 (4: row:359.359 avs:819)
leaf: 0x1818a52 25266770 (5: row:359.359 avs:820)
leaf: 0x1818a53 25266771 (6: row:359.359 avs:819)
leaf: 0x1818a54 25266772 (7: row:359.359 avs:820)
leaf: 0x1818a55 25266773 (8: row:359.359 avs:819)
leaf: 0x1818a56 25266774 (9: row:359.359 avs:820)
leaf: 0x1818a57 25266775 (10: row:359.359 avs:820)
leaf: 0x1818a59 25266777 (11: row:359.359 avs:819)
leaf: 0x1818a5a 25266778 (12: row:359.359 avs:820)
leaf: 0x1818a5b 25266779 (13: row:359.359 avs:819)
leaf: 0x1818a5c 25266780 (14: row:359.359 avs:820)
leaf: 0x1818a5d 25266781 (15: row:359.359 avs:820)
leaf: 0x1818a5e 25266782 (16: row:359.359 avs:819)
leaf: 0x1818a5f 25266783 (17: row:359.359 avs:820)
leaf: 0x1818a60 25266784 (18: row:359.359 avs:819)
leaf: 0x1818a61 25266785 (19: row:359.359 avs:820)
leaf: 0x1818a62 25266786 (20: row:359.359 avs:820)
leaf: 0x1818a63 25266787 (21: row:359.359 avs:819)
leaf: 0x1818a64 25266788 (22: row:359.359 avs:820)
leaf: 0x1818a65 25266789 (23: row:359.359 avs:819)
leaf: 0x1818a66 25266790 (24: row:359.359 avs:820)
leaf: 0x1818a67 25266791 (25: row:359.359 avs:819)
leaf: 0x1818a69 25266793 (26: row:302.302 avs:1960)
----- end tree dump
接下来看看分支节点:
select to_number('18a4b','xxxxxxx') from dual;
alter system dump datafile 13 block 100939
Block header dump: 0x01818a4b
Object id on Block? Y
seg/obj: 0x1ec33 csc: 0x0000000002083dc0 itc: 1 flg: E typ: 2 - INDEX
Branch block dump
=================
header address 140622310096972=0x7fe52ed7404c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 0
kdxconro 27
kdxcofbo 82=0x52
kdxcofeo 7678=0x1dfe
kdxcoavs 7596
kdxbrlmc 25266764=0x1818a4c
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 3
row#0[8042] dba: 25266765=0x1818a4d
col 0; len 4; (4): 73 75 6e 20 --这里和上面的复合索引列值不同了,因为第一个索引第二个列值都一样oracle没有存储该值,
--这里两个列值都存储了,第一个就是前导列,第二个就是id的范围值,前面已经说了这里就不
--详细说明了。
这里需要重点说明,73 75 6e 这三个值为'sun',后面的20表示' '就是一个空字符串,这个你可以dump看看。
col 1; len 3; (3): c2 04 42 --这个就是第二个列值
col 2; TERM
row#1[8028] dba: 25266766=0x1818a4e
col 0; len 4; (4): 73 75 6e 20
col 1; len 3; (3): c2 08 19
col 2; TERM
。。。省略部分
row#25[7692] dba: 25266791=0x1818a67
col 0; len 4; (4): 77 6f 6c 66 ---77 6f 6c 66 代表'wolf'的值
col 1; len 3; (3): c2 5e 29
col 2; TERM
row#26[7678] dba: 25266793=0x1818a69
col 0; len 4; (4): 77 6f 6c 66
col 1; len 3; (3): c2 61 64
col 2; TERM
----- end of branch block dump -----
End dump data blocks tsn: 6 file#: 13 minblk 100939 maxblk 100939
接下来看看索引叶块信息:
0x1818a4c
select to_number('18a4c','xxxxxxx') from dual;
alter system dump datafile 13 block 100940
Leaf block dump
===============
header address 140622310096996=0x7fe52ed74064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 0
kdxconro 364
kdxcofbo 764=0x2fc
kdxcofeo 1582=0x62e
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 25266765=0x1818a4d
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8015] flag: -------, lock: 0, len=17
col 0; len 4; (4): 73 75 6e 20 --这里的信息和前面没什么两样
col 1; len 2; (2): c1 02
col 2; len 6; (6): 01 81 8a 2b 00 00
row#1[7998] flag: -------, lock: 0, len=17
col 0; len 4; (4): 73 75 6e 20
col 1; len 2; (2): c1 03
col 2; len 6; (6): 01 81 8a 2b 00 01
row#2[7981] flag: -------, lock: 0, len=17
col 0; len 4; (4): 73 75 6e 20
col 1; len 2; (2): c1 04
col 2; len 6; (6): 01 81 8a 2b 00 02
row#3[7964] flag: -------, lock: 0, len=17
col 0; len 4; (4): 73 75 6e 20
col 1; len 2; (2): c1 05
col 2; len 6; (6): 01 81 8a 2b 00 03
....
row#363[1582] flag: -------, lock: 0, len=18
col 0; len 4; (4): 73 75 6e 20
col 1; len 3; (3): c2 04 41
col 2; len 6; (6): 01 81 8a 2b 01 6b
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 6 file#: 13 minblk 100940 maxblk 100940
接下来跟踪索引跳跃扫描:
ALTER SESSION SET EVENTS '10200 TRACE NAME CONTEXT FOREVER, LEVEL 1';
select col0,col1 from tb_ti a where id<123
[root@serdb ~]# more /u02/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_ora_30884.trc | grep 'started'
ktrget2(): started for block <0x0006 : 0x01818a4b> objd: 0x0001ec33---分支节点
ktrget2(): started for block <0x0006 : 0x01818a4c> objd: 0x0001ec33---第一个叶节点
ktrget2(): started for block <0x0006 : 0x01818a2b> objd: 0x0001ec32---数据块
ktrget2(): started for block <0x0006 : 0x01818a4b> objd: 0x0001ec33---分支节点
ktrget2(): started for block <0x0006 : 0x01818a4c> objd: 0x0001ec33
ktrget2(): started for block <0x0006 : 0x01818a2b> objd: 0x0001ec32
ktrget2(): started for block <0x0006 : 0x01818a4b> objd: 0x0001ec33---分支节点
ktrget2(): started for block <0x0006 : 0x01818a4c> objd: 0x0001ec33
ktrget2(): started for block <0x0006 : 0x01818a2b> objd: 0x0001ec32
ktrget2(): started for block <0x0006 : 0x01818a4b> objd: 0x0001ec33---分支节点
ktrget2(): started for block <0x0006 : 0x01818a4c> objd: 0x0001ec33
ktrget2(): started for block <0x0006 : 0x01818a2b> objd: 0x0001ec32
ktrget2(): started for block <0x0006 : 0x01818a4b> objd: 0x0001ec33---分支节点
ktrget2(): started for block <0x0006 : 0x01818a4c> objd: 0x0001ec33
ktrget2(): started for block <0x0006 : 0x01818a2b> objd: 0x0001ec32
ktrget2(): started for block <0x0006 : 0x01818a4b> objd: 0x0001ec33---分支节点
ktrget2(): started for block <0x0006 : 0x01818a4c> objd: 0x0001ec33
ktrget2(): started for block <0x0006 : 0x01818a2b> objd: 0x0001ec32
ktrget2(): started for block <0x0006 : 0x01818a4b> objd: 0x0001ec33---分支节点
ktrget2(): started for block <0x0006 : 0x01818a4c> objd: 0x0001ec33
ktrget2(): started for block <0x0006 : 0x01818a2b> objd: 0x0001ec32
ktrget2(): started for block <0x0006 : 0x01818a4b> objd: 0x0001ec33---分支节点
ktrget2(): started for block <0x0006 : 0x01818a4c> objd: 0x0001ec33
ktrget2(): started for block <0x0006 : 0x01818a2b> objd: 0x0001ec32
ktrget2(): started for block <0x0006 : 0x01818a4b> objd: 0x0001ec33---分支节点
ktrget2(): started for block <0x0006 : 0x01818a4c> objd: 0x0001ec33
ktrget2(): started for block <0x0006 : 0x01818a2b> objd: 0x0001ec32
ktrget2(): started for block <0x0006 : 0x01818a4b> objd: 0x0001ec33---分支节点
ktrget2(): started for block <0x0006 : 0x01818a4c> objd: 0x0001ec33
ktrget2(): started for block <0x0006 : 0x01818a2b> objd: 0x0001ec32
ktrget2(): started for block <0x0006 : 0x01818a5a> objd: 0x0001ec33--第十四个叶节点
ktrget2(): started for block <0x0006 : 0x01818a69> objd: 0x0001ec33--最后一个叶节点
从上面的信息可以得知,每次都是要先访问分支节点,然后访问叶节点,再次回表取数据因为有col列需要列出。
我们的这个复合索引28个叶节点,其中col0='sun'占了14个叶节点,col0='wolf'占后面14个叶节点.按照我们的
理解应该只访问两次分支节点接可以了,第一次访问分支节点确定了col='sun' and id<123在第一个叶块中就可以了,
不需要再多次访问分支节点和第一个叶节点了,其实这个和arraysize是有关系的默认为15,123/15=8.2那么oracle就
需要多次循环访问分支节点和第一个叶节点了,比如当我们把arraysize设置为5时此时需要访问26次分支节点,如下:
(这个是我另外建立表分支节点块地址为0x0001eda3设置arraysize=5打印的trace)
[root@serdb ~]# more /u02/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_ora_17076.trc |grep 'started'|grep '0x01818c83'
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
ktrget2(): started for block <0x0006 : 0x01818c83> objd: 0x0001eda3
[root@serdb ~]# more /u02/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_ora_17076.trc |grep 'started'|grep '0x01818c83' |wc -l
26
关于为什么多出一个或者两个逻辑读这里不讨论,我觉得这个影响不大,至于为什么默认的arraysize=15这个
oracle自己作出的符合大多数应用的场景吧,arraysize不能设置太大对客户端的持续响应时间是有影响的。
另外我们也看到oracle访问了第十四个叶块就是col0='WOLF'的第一个叶节点,而且还访问了最后一个叶节点。
也就是oracle将SQL:
select col0,col1 from tb_ti a where id<123
改写为
select col0,col1 from tb_ti a where col0='sun' and id<123
union all
select col0,col1 from tb_ti a where col0='wolf' and id<123
到这里索引跳跃扫描就讲完了,所以索引跳跃扫描一定要前导列的值集中才是最高效的,也即是前导列值唯一值较少。
那么关于复合索引到底该怎么建立索引才是最高效的呢?这个就需要结合应用实际分析了,比如你的应用查询条件不
满足跳跃扫描的机制,采取的是范围扫描,那么最好将唯一值较多的列放在最前面。但是两种情况都存在我觉得按照
跳跃扫描机制建立索引较好。