oracle 復合索引,Dump索引结构--复合索引--跳跃扫描

最新推荐文章于 2024-07-14 21:55:08 发布

weixin_39755712

最新推荐文章于 2024-07-14 21:55:08 发布

阅读量232

点赞数

文章标签： oracle 復合索引

本帖最后由 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

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 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)

-----------------

select a.col0 from tb_ti a where col0 ='sun'

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 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)

-------------------

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

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------

| 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'