SQL> create table test(id number,name char(1000));
Table created
SQL> insert into test select rownum,'name'||rownum from dba_users;
28 rows inserted
SQL> create index idx_test_name on test(name);
Index created
SQL> analyze index idx_test_name validate structure;
Index analyzed
SQL> select lf_rows,lf_blks,br_rows,br_blks,del_lf_rows from index_stats;
LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS
---------- ---------- ---------- ---------- -----------
28 4 3 1 0
有一个分支块，4个叶子块。
分支块中包含3条记录，将索引数据分成4分，分布在4个叶块中。
SQL> select header_file,header_block from dba_segments where segment_name='IDX_TEST_NAME';
HEADER_FILE HEADER_BLOCK
----------- ------------
7 526
SQL> alter system dump datafile 7 block 527;
System altered
SQL> alter system dump datafile 7 block min 528 block max 531;
System altered
以16进制 dump 列中的内容：
SQL> select dump('name11',16) from dual;
DUMP('NAME11',16)
-------------------------------
Typ=96 Len=6: 6e,61,6d,65,31,31
SQL> select dump('name10',16) from dual;
DUMP('NAME10',16)
-------------------------------
Typ=96 Len=6: 6e,61,6d,65,31,30
得到的转存文件分析：
第一分支块：0x01c0020f
*** 2011-12-22 10:45:09.478
Start dump data blocks tsn: 7 file#: 7 minblk 527 maxblk 527
buffer tsn: 7 rdba: 0x01c0020f (7/527)
scn: 0x0000.00123954 seq: 0x01 flg: 0x00 tail: 0x39540601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0xB7E2F000 to 0xB7E31000
Block header dump: 0x01c0020f
Object id on Block? Y
seg/obj: 0xce0b csc: 0x00.123944 itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1c00209 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 0x0000.00123944
Branch block dump
=================
header address 3085103180=0xb7e2f04c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 3
kdxcofbo 34=0x22
kdxcofeo 8021=0x1f55
kdxcoavs 7987
kdxbrlmc 29360656=0x1c00210 起始叶块（第一个叶块）：0x1c00210
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 10
row#0[8044] dba: 29360657=0x1c00211 第二个叶块：0x1c00211，存储数据从 6e 61 6d 65 31 36 开始
col 0; len 6; (6): 6e 61 6d 65 31 36 -- name16
col 1; TERM
row#1[8032] dba: 29360658=0x1c00212 第三个叶块：0x1c00212，存储数据从 6e 61 6d 65 32 32 开始
col 0; len 6; (6): 6e 61 6d 65 32 32 -- name22
col 1; TERM
row#2[8021] dba: 29360659=0x1c00213 第四个叶块：0x1c00213，存储数据从 6e 61 6d 65 33 开始
col 0; len 5; (5): 6e 61 6d 65 33 -- name3
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 7 file#: 7 minblk 527 maxblk 527
第一叶块：0x01c00210
*** 2011-12-22 10:46:31.950
Start dump data blocks tsn: 7 file#: 7 minblk 528 maxblk 531
buffer tsn: 7 rdba: 0x01c00210 (7/528)
scn: 0x0000.00123945 seq: 0x02 flg: 0x04 tail: 0x39450602
frmt: 0x02 chkval: 0xf09d type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0xB7E2F000 to 0xB7E31000
Block header dump: 0x01c00210
Object id on Block? Y
seg/obj: 0xce0b csc: 0x00.123944 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1c00209 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 0x0000.00123944
Leaf block dump
===============
header address 3085103204=0xb7e2f064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 7
kdxcofbo 50=0x32
kdxcofeo 955=0x3bb
kdxcoavs 905
kdxlespl 0
kdxlende 0
kdxlenxt 29360657=0x1c00211 下一个叶块（第二叶块）：0x1c00211
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[7021] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 31 -- name1
col 1; len 6; (6): 01 c0 01 8f 00 00
row#1[6010] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 31 30 -- name10
col 1; len 6; (6): 01 c0 01 90 00 02
row#2[4999] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 31 31 -- name11
col 1; len 6; (6): 01 c0 01 90 00 03
row#3[3988] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 31 32 -- name12
col 1; len 6; (6): 01 c0 01 90 00 04
row#4[2977] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 31 33 -- name13
col 1; len 6; (6): 01 c0 01 90 00 05
row#5[1966] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 31 34 -- name14
col 1; len 6; (6): 01 c0 01 90 00 06
row#6[955] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 31 35 -- name15
col 1; len 6; (6): 01 c0 01 93 00 00
----- end of leaf block dump -----
第二叶块：0x01c00211
buffer tsn: 7 rdba: 0x01c00211 (7/529)
scn: 0x0000.00123945 seq: 0x02 flg: 0x04 tail: 0x39450602
frmt: 0x02 chkval: 0xf844 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0xB7E2F000 to 0xB7E31000
Block header dump: 0x01c00211
Object id on Block? Y
seg/obj: 0xce0b csc: 0x00.123944 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1c00209 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 0x0000.00123944
Leaf block dump
===============
header address 3085103204=0xb7e2f064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 7
kdxcofbo 50=0x32
kdxcofeo 955=0x3bb
kdxcoavs 905
kdxlespl 0
kdxlende 0
kdxlenxt 29360658=0x1c00212 下一个叶块（第三叶块）：0x1c00212
kdxleprv 29360656=0x1c00210 前一个叶块（第一叶块）：0x1c00210
kdxledsz 0
kdxlebksz 8032
row#0[7021] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 31 36 -- name16
col 1; len 6; (6): 01 c0 01 93 00 01
row#1[6010] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 31 37 -- name17
col 1; len 6; (6): 01 c0 01 93 00 02
row#2[4999] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 31 38 -- name18
col 1; len 6; (6): 01 c0 01 93 00 03
row#3[3988] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 31 39 -- name19
col 1; len 6; (6): 01 c0 01 93 00 04
row#4[2977] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 32 -- name2
col 1; len 6; (6): 01 c0 01 8f 00 01
row#5[1966] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 32 30 -- name20
col 1; len 6; (6): 01 c0 01 93 00 05
row#6[955] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 32 31 -- name21
col 1; len 6; (6): 01 c0 01 93 00 06
----- end of leaf block dump -----
第三叶块：0x01c00212
buffer tsn: 7 rdba: 0x01c00212 (7/530)
scn: 0x0000.00123945 seq: 0x02 flg: 0x04 tail: 0x39450602
frmt: 0x02 chkval: 0xee53 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0xB7E2F000 to 0xB7E31000
Block header dump: 0x01c00212
Object id on Block? Y
seg/obj: 0xce0b csc: 0x00.123944 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1c00209 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 0x0000.00123944
Leaf block dump
===============
header address 3085103204=0xb7e2f064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 7
kdxcofbo 50=0x32
kdxcofeo 955=0x3bb
kdxcoavs 905
kdxlespl 0
kdxlende 0
kdxlenxt 29360659=0x1c00213 下一个叶块（第四叶块）：0x1c00213
kdxleprv 29360657=0x1c00211 前一个叶块（第二叶块）：0x1c00211
kdxledsz 0
kdxlebksz 8032
row#0[7021] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 32 32 -- name22
col 1; len 6; (6): 01 c0 01 94 00 00
row#1[6010] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 32 33 -- name23
col 1; len 6; (6): 01 c0 01 94 00 01
row#2[4999] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 32 34 -- name24
col 1; len 6; (6): 01 c0 01 94 00 02
row#3[3988] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 32 35 -- name25
col 1; len 6; (6): 01 c0 01 94 00 03
row#4[2977] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 32 36 -- name26
col 1; len 6; (6): 01 c0 01 94 00 04
row#5[1966] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 32 37 -- name27
col 1; len 6; (6): 01 c0 01 94 00 05
row#6[955] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 32 38 -- name28
col 1; len 6; (6): 01 c0 01 94 00 06
----- end of leaf block dump -----
第四叶块：0x01c00213
buffer tsn: 7 rdba: 0x01c00213 (7/531)
scn: 0x0000.00123945 seq: 0x02 flg: 0x04 tail: 0x39450602
frmt: 0x02 chkval: 0xea87 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0xB7E2F000 to 0xB7E31000
Block header dump: 0x01c00213
Object id on Block? Y
seg/obj: 0xce0b csc: 0x00.123944 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1c00209 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 0x0000.00123944
Leaf block dump
===============
header address 3085103204=0xb7e2f064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 7
kdxcofbo 50=0x32
kdxcofeo 955=0x3bb
kdxcoavs 905
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 29360658=0x1c00212 前一个叶块（第三叶块）：0x1c00212
kdxledsz 0
kdxlebksz 8032
row#0[7021] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 33 -- name3
col 1; len 6; (6): 01 c0 01 8f 00 02
row#1[6010] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 34 -- name4
col 1; len 6; (6): 01 c0 01 8f 00 03
row#2[4999] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 35 -- name5
col 1; len 6; (6): 01 c0 01 8f 00 04
row#3[3988] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 36 -- name6
col 1; len 6; (6): 01 c0 01 8f 00 05
row#4[2977] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 37 -- name7
col 1; len 6; (6): 01 c0 01 8f 00 06
row#5[1966] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 38 -- name8
col 1; len 6; (6): 01 c0 01 90 00 00
row#6[955] flag: ------, lock: 0, len=1011
col 0; len 1000; (1000):
6e 61 6d 65 39 -- name9
col 1; len 6; (6): 01 c0 01 90 00 01
----- end of leaf block dump -----
End dump data blocks tsn: 7 file#: 7 minblk 528 maxblk 531
根据这些转存信息，可以画出索引的结构为：
bruch
leaf1 leaf2 leaf3 leaf4
当 索引值 < "6e 61 6d 65 31 36"(name16) 时，查询 leaf1 叶块；
当 索引值 >="6e 61 6d 65 31 36"(name16) 且 < "6e 61 6d 65 32 32"(name22) 时，查询 leaf2 叶块；
当 索引值 >="6e 61 6d 65 32 32"(name22) 且 < "6e 61 6d 65 33"(name3) 时，查询 leaf3 叶块；
当 索引值 >="6e 61 6d 65 33"(name3) 时，查询 leaf4 叶块；
问题：究竟索引块内部记录是否有序？
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24465008/viewspace-713759/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
