ORACLE存储之INDEX解析

听过不少次关于ORACLE基础的课程, 但是每次都印象不深, 想来主要是对那些概念等没有一个感性的认识, 而且平时实际开发的时候, 也不会怎么去关心那些底层的东西.

前面, 有大段时间的study, 所以对一些基本的东西稍微研究了一下. 这篇主要是关于实际index在ORACLE中存储的.

[@more@]

现有表

XX_TIM_TEST, 对第一个栏位shipment_line_id建有非唯一索引XX_TIM_TREST_N1

利用SQL

SELECT * FROM dba_segments WHERE segment_name = 'ZZ_TIM_TEST_N1'

知该索引存储所在的索引段有1个extents, 8个block, 并且可以看到第一个block为:

File: 379 block: 106225

SELECT * FROM dba_extents WHERE segment_name = 'ZZ_TIM_TEST_N1'

可知索引存储在1个extents中, 每个extents中有8个block, 同时也可以查知第一个block的位置.

导出第一个block

ALTER SYSTEM DUMP DATAFILE 379 BLOCK 106225

Start dump data blocks tsn: 370 file#: 379 minblk 106225 maxblk 106225 buffer tsn: 370 rdba: 0x5ec19ef1 (379/106225) scn: 0x074e.396bfdfd seq: 0x02 flg: 0x00 tail: 0xfdfd2002 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 2 nranges: 1 parent dba: 0x5ec19ef2 poffset: 0 unformatted: 0 total: 8 first useful block: 3 owning instance : 1 instance ownership changed at 05/22/2007 16:43:40 Last successful Search 05/22/2007 16:43:40 Freeness Status: nf1 0 nf2 1 nf3 0 nf4 0 Extent Map Block Offset: 4294967295 First free datablock : 7 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x5ec19ef9 ext#: 0 blk#: 8 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x5ec19ef1 Length: 8 Offset: 0 0:Metadata 1:Metadata 2:Metadata 3:FULL 4:FULL 5:FULL 6:FULL 7:25-50% free -------------------------------------------------------- End dump data blocks tsn: 370 file#: 379 minblk 106225 maxblk 106225

可以看到,这个block中存储的并不是索引信息.

我们使用以下SQL导出该索引的结构:

SELECT object_id FROM dba_objects WHERE object_name='ZZ_TIM_TEST_I1'; ALTER SESSION SET EVENTS 'immediate trace name TREEDUMP level 388895';

大致如下:

----- begin tree dump branch: 0x5ec19ef4 1589747444 (0: nrow: 3, level: 1) leaf: 0x5ec19ef5 1589747445 (-1: nrow: 532 rrow: 532) leaf: 0x5ec19ef6 1589747446 (0: nrow: 532 rrow: 532) leaf: 0x5ec19ef7 1589747447 (1: nrow: 316 rrow: 316) ----- end tree dump

可以看到其第一个节点其实就是跟节点的物理位置: 0x5ec19ef4 与索引段的第一个block位置0x5ec19ef1相差3, 我们分别看者接下来2个block的内容

ALTER SYSTEM DUMP DATAFILE 379 BLOCK 106226

Start dump data blocks tsn: 370 file#: 379 minblk 106226 maxblk 106226 buffer tsn: 370 rdba: 0x5ec19ef2 (379/106226) scn: 0x074e.396ba414 seq: 0x01 flg: 0x00 tail: 0xa4142101 frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 1 nfree: 1 ffree: 0 pdba: 0x5ec19ef3 opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x5ec19ef1 Free: 3 Inst: 1 -------------------------------------------------------- End dump data blocks tsn: 370 file#: 379 minblk 106226 maxblk 106226

ALTER SYSTEM DUMP DATAFILE 379 BLOCK 106227

Start dump data blocks tsn: 370 file#: 379 minblk 106227 maxblk 106227 buffer tsn: 370 rdba: 0x5ec19ef3 (379/106227) scn: 0x074e.396ba414 seq: 0x02 flg: 0x00 tail: 0xa4142302 frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8 last map 0x00000000 #maps: 0 offset: 2720 Highwater:: 0x5ec19ef9 ext#: 0 blk#: 8 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x5ec19ef9 ext#: 0 blk#: 8 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x5ec19ef1 Level 1 BMB for Low HWM block: 0x5ec19ef1 -------------------------------------------------------- Segment Type: 2 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001438 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x5ec19ef2 Last Level 1 BMB: 0x5ec19ef1 Last Level II BMB: 0x5ec19ef2 Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 1 obj#: 388895 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 0x5ec19ef1 length: 8 Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x5ec19ef1 Data dba: 0x5ec19ef4 -------------------------------------------------------- Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x5ec19ef2 End dump data blocks tsn: 370 file#: 379 minblk 106227 maxblk 106227

可以看到这3个block存储的都是meta data.

直到第四个block, 才开始真正存储索引内容, 也就是我们在索引树中看到的第一个block, 根节点block.

而我们使用以下命令:

SELECT DBMS_UTILITY.data_block_address_file (1589747444) "file", DBMS_UTILITY.data_block_address_block (1589747444) "block" FROM DUAL;

可以得到结果 379, 106228

我们导出该根节点block, 内容大致如下:

Start dump data blocks tsn: 370 file#: 379 minblk 106228 maxblk 106228 buffer tsn: 370 rdba: 0x5ec19ef4 (379/106228) scn: 0x074e.396bfdfe seq: 0x01 flg: 0x02 tail: 0xfdfe0601 frmt: 0x02 chkval: 0x0000 type: 0x06=trans data Block header dump: 0x5ec19ef4 Object id on Block? Y seg/obj: 0x5ef1f csc: 0x74e.396ba41d itc: 1 flg: E typ: 2 - INDEX brn: 0 bdba: 0x5ec19ef1 ver: 0x01 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0003.014.0007711b 0x64c00218.2804.03 --U- 1 fsc 0x0000.396bfdfe Branch block dump ================= header address 9223372041150905420=0x800000010011bc4c kdxcolev 1 KDXCOLEV Flags = - - - kdxcolok 1 kdxcoopc 0x81: opcode=1: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 1 kdxconro 3 kdxcofbo 34=0x22 kdxcofeo 8013=0x1f4d kdxcoavs 7979 kdxbrlmc 1589747445=0x5ec19ef5—第一个叶子节点 kdxbrsno 0 kdxbrbksz 8040 row#0[8013] dba: 1589747448=0x5ec19ef8—第二个叶子节点 col 0; len 3; (3): c2 0b 37—第二个叶子节点开始索引值 col 1; TERM row#1[8031] dba: 1589747446=0x5ec19ef6 col 0; len 3; (3): c2 14 44 col 1; TERM row#2[8022] dba: 1589747447=0x5ec19ef7 col 0; len 3; (3): c2 25 3a col 1; TERM ----- end of branch block dump ----- End dump data blocks tsn: 370 file#: 379 minblk 106228 maxblk 106228

我们导出第一个叶子节点block, 内容大致如下:

Leaf block dump =============== header address 9223372041150905444=0x800000010011bc64 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 1 kdxconro 272 kdxcofbo 580=0x244 kdxcofeo 4490=0x118a kdxcoavs 3910 kdxlespl 14 kdxlende 2 kdxlenxt 1589747448=0x5ec19ef8 kdxleprv 0=0x0 kdxledsz 0 kdxlebksz 8016 row#0[4502] flag: ---D-, lock: 2 col 0; len 2; (2): c1 02 col 1; len 6; (6): 5e c1 9e e7 00 00 row#1[4514] flag: ---DS, lock: 0 col 0; len 2; (2): c1 03 col 1; len 6; (6): 5e c1 9e e7 00 01 row#2[4490] flag: -----, lock: 2—第一条有效记录 col 0; len 2; (2): c1 04 col 1; len 6; (6): 5e c1 9e e7 00 00 row#3[4526] flag: -----, lock: 0 col 0; len 2; (2): c1 04 col 1; len 6; (6): 5e c1 9e e7 00 01 row#4[4538] flag: -----, lock: 0 col 0; len 2; (2): c1 16 col 1; len 6; (6): 5e c1 9e e7 00 02 row#5[4550] flag: -----, lock: 0 col 0; len 2; (2): c1 2a col 1; len 6; (6): 5e c1 9e e7 00 03 ----- end of leaf block dump ----- End dump data blocks tsn: 370 file#: 379 minblk 106229 maxblk 106229

我们可以查SQL可以看到,表中第一条记录的索引值为: 3

而通过以下SQL

SELECT DUMP(3,16) FROM dual

Typ=2 Len=2: c1,4

可以看到正好可以与索引表的第一个记录匹配: c1 04

我们找到了第一条记录的地址: 5e c1 9e e7 00 00

前8位为block地址,

SELECT TO_NUMBER('5ec19ee7','XXXXXXXX') FROM DUAL

导出该block, 可以完整的看到第一条记录:

block_row_dump: tab 0, row 0, @0x1ebe tl: 218 fb: --H-FL-- lb: 0x1 cc: 96 col 0: [ 2] c1 04 –第一列值,也即索引值 col 1: [ 7] 78 68 07 06 0a 1d 10 col 2: [ 3] c2 0c 02 col 3: [ 7] 78 68 07 06 0a 1d 10 col 4: [ 3] c2 0c 02 col 5: [ 3] c2 0c 02 col 6: [ 2] c1 02 col 7: [ 2] c1 02 col 8: [ 3] c2 02 54 col 9: [ 2] c1 07 col 10: [ 2] c1 07

这个例子中的情况比较简单, 只有一个索引,也不涉及复合索引, 且索引数据类型为最简单的number类型.其他复杂情况,有兴趣者可自行研究.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/767125/viewspace-925129/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/767125/viewspace-925129/