|
|
本帖最后由 sunyunyi 于 2018-11-10 10:58 编辑
ORACLE 倒叙索引：
作者简介：
----------------------------------------------------
@ 孙显鹏，海天起点oracle技术专家，十年从业经验
@ 精通oracle内部原理，擅长调优和解决疑难问题
@ 致力于帮助客户解决生产中的问题，提高生产效率。
@ 爱好：书法，周易，中医。微信：sunyunyi_sun
@ 易曰：精义入神，以致用也！
@ 调优乃燮理阴阳何其难也！
-----------------------------------------------------
下面我们看看倒叙索引内部结构是什么样子，是不是索引键值就是倒叙排列呢？
答案肯定不是，因为oracle不可能为索引建立两个不同的结构。
create user zfy identified by zfy default tablespace test;
grant dba to zfy;
conn zfy/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 desc);
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
---------- ----------
TB_TI 73553
IDX_TB_TI 73554
alter session set events 'immediate trace name treedump level 73554';
/u02/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_642.trc
----- begin tree dump
branch: 0x14000a3 20971683 (0: nrow: 23, level: 1)
leaf: 0x14000a4 20971684 (-1: row:449.449 avs:817)
leaf: 0x14000a5 20971685 (0: row:449.449 avs:816)
leaf: 0x14000a6 20971686 (1: row:449.449 avs:817)
leaf: 0x14000a7 20971687 (2: row:449.449 avs:816)
leaf: 0x14000a8 20971688 (3: row:449.449 avs:817)
leaf: 0x14000a9 20971689 (4: row:449.449 avs:816)
leaf: 0x14000aa 20971690 (5: row:449.449 avs:817)
leaf: 0x14000ab 20971691 (6: row:449.449 avs:816)
leaf: 0x14000ac 20971692 (7: row:449.449 avs:817)
leaf: 0x14000ad 20971693 (8: row:449.449 avs:816)
leaf: 0x14000ae 20971694 (9: row:449.449 avs:817)
leaf: 0x14000af 20971695 (10: row:449.449 avs:816)
leaf: 0x14000b1 20971697 (11: row:449.449 avs:817)
leaf: 0x14000b2 20971698 (12: row:449.449 avs:816)
leaf: 0x14000b3 20971699 (13: row:449.449 avs:817)
leaf: 0x14000b4 20971700 (14: row:449.449 avs:816)
leaf: 0x14000b5 20971701 (15: row:449.449 avs:817)
leaf: 0x14000b6 20971702 (16: row:449.449 avs:816)
leaf: 0x14000b7 20971703 (17: row:449.449 avs:817)
leaf: 0x14000b8 20971704 (18: row:449.449 avs:816)
leaf: 0x14000b9 20971705 (19: row:449.449 avs:817)
leaf: 0x14000ba 20971706 (20: row:449.449 avs:816)
leaf: 0x14000bb 20971707 (21: row:122.122 avs:6144)
----- end tree dump
首先分析枝节点
select to_number('000a3','xxxxxxx') from dual; --163
alter system dump datafile 5 block 163;
Block header dump: 0x014000a3
Object id on Block? Y
seg/obj: 0x11f52 csc: 0x0000000000169aa6 itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x14000a0 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 0x0000000000169aa6
Branch block dump
=================
header address 140043605704780=0x7f5e715f004c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 22
kdxcofbo 72=0x48
kdxcofeo 7858=0x1eb2
kdxcoavs 7786
kdxbrlmc 20971684=0x14000a4
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 8
row#0[8047] dba: 20971685=0x14000a5
col 0; len 3; (3): 3d 9f cb
col 1; TERM
row#1[8038] dba: 20971686=0x14000a6
col 0; len 3; (3): 3d a3 fc
col 1; TERM
row#2[8029] dba: 20971687=0x14000a7
col 0; len 3; (3): 3d a8 c9
col 1; TERM
row#3[8020] dba: 20971688=0x14000a8
col 0; len 3; (3): 3d ac fa
col 1; TERM
row#4[8011] dba: 20971689=0x14000a9
col 0; len 3; (3): 3d b1 c7
col 1; TERM
row#5[8002] dba: 20971690=0x14000aa
col 0; len 3; (3): 3d b5 f8
col 1; TERM
row#6[7993] dba: 20971691=0x14000ab
col 0; len 3; (3): 3d ba c5
col 1; TERM
row#7[7984] dba: 20971692=0x14000ac
col 0; len 3; (3): 3d be f6
col 1; TERM
row#8[7975] dba: 20971693=0x14000ad
col 0; len 3; (3): 3d c3 c3
col 1; TERM
row#9[7966] dba: 20971694=0x14000ae
col 0; len 3; (3): 3d c7 f4
col 1; TERM
row#10[7957] dba: 20971695=0x14000af
col 0; len 3; (3): 3d cc c1
col 1; TERM
row#11[7948] dba: 20971697=0x14000b1
col 0; len 3; (3): 3d d0 f2
col 1; TERM
row#12[7939] dba: 20971698=0x14000b2
col 0; len 3; (3): 3d d5 bf
col 1; TERM
row#13[7930] dba: 20971699=0x14000b3
col 0; len 3; (3): 3d d9 f0
col 1; TERM
row#14[7921] dba: 20971700=0x14000b4
col 0; len 3; (3): 3d de bd
col 1; TERM
row#15[7912] dba: 20971701=0x14000b5
col 0; len 3; (3): 3d e2 ee
col 1; TERM
row#16[7903] dba: 20971702=0x14000b6
col 0; len 3; (3): 3d e7 bb
col 1; TERM
row#17[7894] dba: 20971703=0x14000b7
col 0; len 3; (3): 3d eb ec
col 1; TERM
row#18[7885] dba: 20971704=0x14000b8
col 0; len 3; (3): 3d f0 b9
col 1; TERM
row#19[7876] dba: 20971705=0x14000b9
col 0; len 3; (3): 3d f4 ea
col 1; TERM
row#20[7867] dba: 20971706=0x14000ba
col 0; len 3; (3): 3d f9 b7
col 1; TERM
row#21[7858] dba: 20971707=0x14000bb
col 0; len 3; (3): 3d fd e8
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 6 file#: 5 minblk 163 maxblk 163
我们分析下：
从分支节点可以看到叶节点还是按照升序排列的，但是分支节点临界值并不是id列的实际值，
3d 9f cb 这些值是怎来的呢？我还不清楚。
我们看看第一个叶节点：
select to_number('14','xxxxxxx')/4 from dual; --5
select to_number('000a4','xxxxxxx') from dual; --164
alter system dump datafile 5 block 164;
row#0[8019] flag: -------, lock: 0, len=13
col 0; len 3; (3): 3c fd ff -10000 -id的最大值，怎么变成3c fd ff
col 1; len 6; (6): 01 40 00 9e 00 5c
row#1[8005] flag: -------, lock: 0, len=14
col 0; len 4; (4): 3d 9b 9b ff -9999
col 1; len 6; (6): 01 40 00 9e 00 5b
row#2[7991] flag: -------, lock: 0, len=14
col 0; len 4; (4): 3d 9b 9c ff -9998
col 1; len 6; (6): 01 40 00 9e 00 5a
row#3[7977] flag: -------, lock: 0, len=14
col 0; len 4; (4): 3d 9b 9d ff
col 1; len 6; (6): 01 40 00 9e 00 59
row#4[7963] flag: -------, lock: 0, len=14
col 0; len 4; (4): 3d 9b 9e ff
col 1; len 6; (6): 01 40 00 9e 00 58
row#5[7949] flag: -------, lock: 0, len=14
col 0; len 4; (4): 3d 9b 9f ff
col 1; len 6; (6): 01 40 00 9e 00 57
row#6[7935] flag: -------, lock: 0, len=14
col 0; len 4; (4): 3d 9b a0 ff
col 1; len 6; (6): 01 40 00 9e 00 56
row#7[7921] flag: -------, lock: 0, len=14
col 0; len 4; (4): 3d 9b a1 ff
col 1; len 6; (6): 01 40 00 9e 00 55
row#8[7907] flag: -------, lock: 0, len=14
col 0; len 4; (4): 3d 9b a2 ff
col 1; len 6; (6): 01 40 00 9e 00 54
row#9[7893] flag: -------, lock: 0, len=14
col 0; len 4; (4): 3d 9b a3 ff
col 1; len 6; (6): 01 40 00 9e 00 53
row#10[7879] flag: -------, lock: 0, len=14
col 0; len 4; (4): 3d 9b a4 ff
col 1; len 6; (6): 01 40 00 9e 00 52
row#11[7865] flag: -------, lock: 0, len=14
col 0; len 4; (4): 3d 9b a5 ff
col 1; len 6; (6): 01 40 00 9e 00 51
row#12[7851] flag: -------, lock: 0, len=14
col 0; len 4; (4): 3d 9b a6 ff
我们查看第一个叶子信息：
row#0[8019] flag: -------, lock: 0, len=13
col 0; len 3; (3): 3c fd ff
col 1; len 6; (6): 01 40 00 9e 00 5c
select to_number('0009e','xxxxxxx') from dual; --158
select to_number('0005c','xxxxxxx') from dual; --92
alter system dump datafile 5 block 158;
tab 0, row 92, @0x1954
tl: 16 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c3 02
col 1: [ 4] 77 6f 6c 66
col 2: [ 4] 77 69 74 73
end_of_block_dump
End dump data blocks tsn: 6 file#: 5 minblk 158 maxblk 158
select dump (10000,16) from dual;
Typ=2 Len=2: c3,2
10000的dump值为 c3 02 ，但是在倒叙索引下的键值却变成了3c fd ff，oracle内部做了加工处理。
但是加工处理的结构3c fd ff还是最小值，
select to_number('000bb','xxxxxxx') from dual; --187
select to_number('000ba','xxxxxxx') from dual; --186
alter system dump datafile 5 block 187;
row#0[8018] flag: -------, lock: 0, len=14
col 0; len 4; (4): 3d fd e8 ff ---3d fd e8 就是最后一个叶块的开始id值，也就是分支节点最后一个值
col 1; len 6; (6): 01 40 00 83 00 79
row#1[8004] flag: -------, lock: 0, len=14
col 0; len 4; (4): 3d fd e9 ff
col 1; len 6; (6): 01 40 00 83 00 78
row#2[7990] flag: -------, lock: 0, len=14
col 0; len 4; (4): 3d fd ea ff
col 1; len 6; (6): 01 40 00 83 00 77
row#3[7976] flag: -------, lock: 0, len=14
......
row#113[6528] flag: -------, lock: 0, len=13
col 0; len 3; (3): 3e f5 ff
col 1; len 6; (6): 01 40 00 83 00 08
row#114[6515] flag: -------, lock: 0, len=13
col 0; len 3; (3): 3e f6 ff
col 1; len 6; (6): 01 40 00 83 00 07
row#115[6502] flag: -------, lock: 0, len=13
col 0; len 3; (3): 3e f7 ff
col 1; len 6; (6): 01 40 00 83 00 06
row#116[6489] flag: -------, lock: 0, len=13
col 0; len 3; (3): 3e f8 ff
col 1; len 6; (6): 01 40 00 83 00 05
row#117[6476] flag: -------, lock: 0, len=13
col 0; len 3; (3): 3e f9 ff
col 1; len 6; (6): 01 40 00 83 00 04
row#118[6463] flag: -------, lock: 0, len=13
col 0; len 3; (3): 3e fa ff
col 1; len 6; (6): 01 40 00 83 00 03
row#119[6450] flag: -------, lock: 0, len=13
col 0; len 3; (3): 3e fb ff
col 1; len 6; (6): 01 40 00 83 00 02
row#120[6437] flag: -------, lock: 0, len=13
col 0; len 3; (3): 3e fc ff
col 1; len 6; (6): 01 40 00 83 00 01
row#121[6424] flag: -------, lock: 0, len=13
col 0; len 3; (3): 3e fd ff
col 1; len 6; (6): 01 40 00 83 00 00
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 6 file#: 5 minblk 187 maxblk 187
dump 最后一个id=3e fd ff col1=01 40 00 83 00 00
select to_number('00083','xxxxxxx') from dual; --131
alter system dump datafile 5 block 131;
tab 0, row 0, @0x1f70
tl: 16 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 02 id=1
col 1: [ 4] 77 6f 6c 66
col 2: [ 4] 77 69 74 73
也就是说id=1的c1 02通过倒叙索引id变成了3e fd ff，而3e fd ff在所有索引键值中却变成了最大值。
我们看看这个值是什么意思：
3c fd ff
select to_number('3d','xxxxxxx') from dual; --61
select to_number('fd','xxxxxxx') from dual; --253
select to_number('ff','xxxxxxx') from dual; --255
61 开头应该是负数，计算方式为：
指数=62-61=1
数字1= (101-253)*100^(1-0) = -152*100=-15200
FF 应该有特殊含义，其实注意到col 0; len 3; (3):这个括号里的3不是number型，
number型应该为2才对，这个可能有特殊含义。到目前为止我还不清楚该计算方式。
负数应该是以102结尾。
比如正常-10000的oracle内部 100进制表示方式应该是：
select dump(-10000) from dual;
Typ=2 Len=3: 60,100,102
指数=62-60=2
数字1 =(101-100)*100^2=1*10000
102 代表-号
这个问题有待于深入研究！
下面是正常索引的信息，可以作为对比参考。
create table tb_tia as select rownum id,'wolf' col0,'wits' col1 from dual connect by level<=10000;
create index idx_tb_tia on tb_tia(id);
exec sys.dbms_stats.gather_table_stats ( OwnName => 'ZFY',TabName => 'TB_TIA',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_TIA','IDX_TB_TIA');
----------
IDX_TB_TIA 73702
TB_TIA 73701
alter session set events 'immediate trace name treedump level 73702';
----- begin tree dump
branch: 0x14000e3 20971747 (0: nrow: 21, level: 1)
leaf: 0x14000e4 20971748 (-1: row:485.485 avs:824)
leaf: 0x14000e5 20971749 (0: row:479.479 avs:816)
leaf: 0x14000e6 20971750 (1: row:479.479 avs:816)
leaf: 0x14000e7 20971751 (2: row:479.479 avs:816)
leaf: 0x14000e8 20971752 (3: row:479.479 avs:816)
leaf: 0x14000e9 20971753 (4: row:478.478 avs:830)
leaf: 0x14000ea 20971754 (5: row:479.479 avs:816)
leaf: 0x14000eb 20971755 (6: row:479.479 avs:816)
leaf: 0x14000ec 20971756 (7: row:479.479 avs:816)
leaf: 0x14000ed 20971757 (8: row:478.478 avs:830)
leaf: 0x14000ee 20971758 (9: row:479.479 avs:816)
leaf: 0x14000ef 20971759 (10: row:479.479 avs:816)
leaf: 0x14000f1 20971761 (11: row:479.479 avs:816)
leaf: 0x14000f2 20971762 (12: row:479.479 avs:816)
leaf: 0x14000f3 20971763 (13: row:478.478 avs:830)
leaf: 0x14000f4 20971764 (14: row:479.479 avs:816)
leaf: 0x14000f5 20971765 (15: row:479.479 avs:816)
leaf: 0x14000f6 20971766 (16: row:479.479 avs:816)
leaf: 0x14000f7 20971767 (17: row:479.479 avs:816)
leaf: 0x14000f8 20971768 (18: row:478.478 avs:830)
leaf: 0x14000f9 20971769 (19: row:418.418 avs:1731)
----- end tree dump
select to_number('14','xxxxxxx')/4 from dual; --5
select to_number('000e3','xxxxxxx') from dual; --227
alter system dump datafile 5 block 227;
Block header dump: 0x014000e3
Object id on Block? Y
seg/obj: 0x11fe6 csc: 0x000000000016fc2a itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x14000e0 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 0x000000000016fc2a
Branch block dump
=================
header address 140466161524812=0x7fc0d3a9204c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 20
kdxcofbo 68=0x44
kdxcofeo 7876=0x1ec4
kdxcoavs 7808
kdxbrlmc 20971748=0x14000e4
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8047] dba: 20971749=0x14000e5
col 0; len 3; (3): c2 05 57
col 1; TERM
row#1[8038] dba: 20971750=0x14000e6
col 0; len 3; (3): c2 0a 42
col 1; TERM
row#2[8029] dba: 20971751=0x14000e7
col 0; len 3; (3): c2 0f 2d
col 1; TERM
row#3[8020] dba: 20971752=0x14000e8
col 0; len 3; (3): c2 14 18
col 1; TERM
row#4[8011] dba: 20971753=0x14000e9
col 0; len 3; (3): c2 19 03
col 1; TERM
row#5[8002] dba: 20971754=0x14000ea
col 0; len 3; (3): c2 1d 51
col 1; TERM
row#6[7993] dba: 20971755=0x14000eb
col 0; len 3; (3): c2 22 3c
col 1; TERM
row#7[7984] dba: 20971756=0x14000ec
col 0; len 3; (3): c2 27 27
col 1; TERM
row#8[7975] dba: 20971757=0x14000ed
col 0; len 3; (3): c2 2c 12
col 1; TERM
row#9[7966] dba: 20971758=0x14000ee
col 0; len 3; (3): c2 30 60
col 1; TERM
row#10[7957] dba: 20971759=0x14000ef
col 0; len 3; (3): c2 35 4b
col 1; TERM
row#11[7948] dba: 20971761=0x14000f1
col 0; len 3; (3): c2 3a 36
col 1; TERM
row#12[7939] dba: 20971762=0x14000f2
col 0; len 3; (3): c2 3f 21
col 1; TERM
row#13[7930] dba: 20971763=0x14000f3
col 0; len 3; (3): c2 44 0c
col 1; TERM
row#14[7921] dba: 20971764=0x14000f4
col 0; len 3; (3): c2 48 5a
col 1; TERM
row#15[7912] dba: 20971765=0x14000f5
col 0; len 3; (3): c2 4d 45
col 1; TERM
row#16[7903] dba: 20971766=0x14000f6
col 0; len 3; (3): c2 52 30
col 1; TERM
row#17[7894] dba: 20971767=0x14000f7
col 0; len 3; (3): c2 57 1b
col 1; TERM
row#18[7885] dba: 20971768=0x14000f8
col 0; len 3; (3): c2 5c 06
col 1; TERM
row#19[7876] dba: 20971769=0x14000f9
col 0; len 3; (3): c2 60 54
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 6 file#: 5 minblk 227 maxblk 227
select to_number('000e4','xxxxxxx') from dual; --228
alter system dump datafile 5 block 228;
Block header dump: 0x014000e4
Object id on Block? Y
seg/obj: 0x11fe6 csc: 0x000000000016fc2a itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x14000e0 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 0x000000000016fc2a
Leaf block dump
===============
header address 140466161524836=0x7fc0d3a92064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 485
kdxcofbo 1006=0x3ee
kdxcofeo 1830=0x726
kdxcoavs 824
kdxlespl 0
kdxlende 0
kdxlenxt 20971749=0x14000e5
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8020] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 40 00 c3 00 00
row#1[8008] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 40 00 c3 00 01
row#2[7996] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 40 00 c3 00 02
row#3[7984] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 40 00 c3 00 03
row#4[7972] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 40 00 c3 00 04
row#5[7960] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 07
col 1; len 6; (6): 01 40 00 c3 00 05
row#6[7948] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 40 00 c3 00 06
row#7[7936] flag: -------, lock: 0, len=12
col 0; len 2; (2): c1 09
col 1; len 6; (6): 01 40 00 c3 00 07
row#8[7924] flag: -------, lock: 0, len=12
...
row#480[1882] flag: -------, lock: 0, len=13
col 0; len 3; (3): c2 05 52
col 1; len 6; (6): 01 40 00 c4 00 5e
row#481[1869] flag: -------, lock: 0, len=13
col 0; len 3; (3): c2 05 53
col 1; len 6; (6): 01 40 00 c4 00 5f
row#482[1856] flag: -------, lock: 0, len=13
col 0; len 3; (3): c2 05 54
col 1; len 6; (6): 01 40 00 c4 00 60
row#483[1843] flag: -------, lock: 0, len=13
col 0; len 3; (3): c2 05 55
col 1; len 6; (6): 01 40 00 c4 00 61
row#484[1830] flag: -------, lock: 0, len=13
col 0; len 3; (3): c2 05 56
col 1; len 6; (6): 01 40 00 c4 00 62
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 6 file#: 5 minblk 228 maxblk 228
|
|
IP卡
狗仔卡
发表于 2018-4-17 10:40
收藏
提升卡
置顶卡
变色卡
显身卡
楼主
