oracle层级关系按列存储_ORACLE存储之INDEX解析

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

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

现有表

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

利用SQLSELECT*FROMdba_segmentsWHEREsegment_name='ZZ_TIM_TEST_N1'

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

File: 379 block: 106225SELECT*FROMdba_extentsWHEREsegment_name='ZZ_TIM_TEST_N1'

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

导出第一个blockALTERSYSTEMDUMPDATAFILE379BLOCK106225

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: 1parent dba:0x5ec19ef2poffset: 0

unformatted: 0total: 8first 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 0nf2 1nf3 0nf4 0

Extent Map Block Offset: 4294967295

First free datablock : 7

Bitmap block lock opcode 0

Locker xid::0x0000.000.00000000

Highwater::0x5ec19ef9ext#: 0blk#: 8ext size: 8

#blocks in seg. hdr's freelists: 0

#blocks below: 5

mapblk0x00000000offset: 0

HWM Flag: HWM Set

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

DBA Ranges :

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

0x5ec19ef1Length: 8Offset: 0

0:Metadata1:Metadata2:Metadata3:FULL

4:FULL5:FULL6:FULL7:25-50% free

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

End dump data blocks tsn: 370 file#: 379 minblk 106225 maxblk 106225

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

我们使用以下SQL导出该索引的结构:SELECTobject_idFROMdba_objectsWHEREobject_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的内容ALTERSYSTEMDUMPDATAFILE379BLOCK106226

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: 1nfree: 1ffree: 0pdba:0x5ec19ef3

opcode:0

xid:

L1 Ranges :

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

0x5ec19ef1Free: 3 Inst: 1

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

End dump data blocks tsn: 370 file#: 379 minblk 106226 maxblk 106226

ALTERSYSTEMDUMPDATAFILE379BLOCK106227

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: 0spare2: 0#extents: 1#blocks: 8

last map0x00000000#maps: 0offset: 2720

Highwater::0x5ec19ef9ext#: 0blk#: 8ext size: 8

#blocks in seg. hdr's freelists: 0

#blocks below: 5

mapblk0x00000000offset: 0

Unlocked

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

Low HighWater Mark :

Highwater::0x5ec19ef9ext#: 0blk#: 8ext size: 8

#blocks in seg. hdr's freelists: 0

#blocks below: 5

mapblk0x00000000offset: 0

Level 1 BMB for High HWM block: 0x5ec19ef1

Level 1 BMB for Low HWM block: 0x5ec19ef1

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

Segment Type: 2 nl2: 1blksz: 8192fbsz: 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:: next0x00000000#extents: 1obj#: 388895 flag: 0x20000000

Extent Map

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

0x5ec19ef1length: 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.

而我们使用以下命令:SELECTDBMS_UTILITY.data_block_address_file(1589747444)"file",

DBMS_UTILITY.data_block_address_block(1589747444)"block"

FROMDUAL;

可以得到结果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: 0x5ef1fcsc: 0x74e.396ba41ditc: 1flg: Etyp: 2 - INDEX

brn: 0bdba: 0x5ec19ef1 ver: 0x01

inc: 0exflg: 0

ItlXidUbaFlagLckScn/Fsc

0x010x0003.014.0007711b0x64c00218.2804.03--U-1fsc 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

而通过以下SQLSELECTDUMP(3,16)FROMdual

Typ=2 Len=2: c1,4

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

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

前8位为block地址,SELECTTO_NUMBER('5ec19ee7','XXXXXXXX')FROMDUAL

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

tab 0, row 0, @0x1ebe

tl: 218 fb: --H-FL-- lb: 0x1cc: 96

col0: [ 2]c1 04 –第一列值,也即索引值

col1: [ 7]78 68 07 06 0a 1d 10

col2: [ 3]c2 0c 02

col3: [ 7]78 68 07 06 0a 1d 10

col4: [ 3]c2 0c 02

col5: [ 3]c2 0c 02

col6: [ 2]c1 02

col7: [ 2]c1 02

col8: [ 3]c2 02 54

col9: [ 2]c1 07

col 10: [ 2]c1 07

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