oracle btree index 索引块结构初探

通过treedump查看索引结构

语法格式：alter session set events 'immediate trace name treedump level index_object_id'

SQL> create table t1(c1 varchar2(10));

表已创建。

SQL> create index i1 on t1(c1);

索引已创建。

SQL> select object_id from user_objects where object_name='I1';

 OBJECT_ID
----------
    364492

SQL> alter session set events 'immediate trace name treedump level 364492';

trace file 内容：

branch: 0xb0ae6ac 185263788 (0: nrow: 2, level: 2)
   branch: 0xb11227c 185672316 (-1: nrow: 460, level: 1)
      leaf: 0xb0ae6ad 185263789 (-1: nrow: 317 rrow: 317)
      leaf: 0xb112153 185672019 (0: nrow: 295 rrow: 295)
      leaf: 0xb0ae82f 185264175 (1: nrow: 281 rrow: 281)
      ...
      leaf: 0xb11211e 185671966 (457: nrow: 334 rrow: 334)
      leaf: 0xb0ae83c 185264188 (458: nrow: 338 rrow: 338)
   branch: 0xb112280 185672320 (0: nrow: 454, level: 1)
      leaf: 0xb112127 185671975 (-1: nrow: 303 rrow: 303)
      leaf: 0xb0ae6d6 185263830 (0: nrow: 304 rrow: 304)
      leaf: 0xb112165 185672037 (1: nrow: 300 rrow: 300)
      ...
      leaf: 0xb0ae810 185264144 (451: nrow: 398 rrow: 398)
      leaf: 0xb112143 185672003 (452: nrow: 401 rrow: 401)
----- end tree dump

块的类别：分支块和叶子块

块的rdba地址：十六进制和十进制
相对于上一级块结构的位置，从-1开始，root块从 0开始
nrows: 所有的记录数量（包括已删除的）
rrows: 当前包含的记录数量
level : 分支块的级别，叶子节点隐式为0

注意：oracle的btree index几乎总是平衡的。

怎样获取索引块的地址信息

oracle为我们提供了两个视图：dba_segments dba_extents. 通过这两个视图，我们可以查询索引的root块地址.

SQL> l
  1* select tablespace_name,segment_space_management from dba_tablespaces where tablespace_name in ('TS1','USERS')
SQL> /

TABLESPACE_NAME                SEGMEN
------------------------------ ------
TS1                            MANUAL
USERS                          AUTO

SQL> col segment_name for a20
SQL> col tablespace_name for a20
SQL> select segment_name,tablespace_name,header_file,header_block from dba_segments where owner='EASYPOINT' and segment_name like 'I%'
  2  /

SEGMENT_NAME         TABLESPACE_NAME      HEADER_FILE HEADER_BLOCK
-------------------- -------------------- ----------- ------------
I1                   USERS                         44       714411
I2                   TS1                           46           17

SQL> select segment_name,min(extent_id),min(block_id) from dba_extents where owner='EASYPOINT' and segment_name like 'I%' group by segment_name;

SEGMENT_NAME         MIN(EXTENT_ID) MIN(BLOCK_ID)
-------------------- -------------- -------------
I2                                0            17
I1                                0        714409

SQL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(192938002) FROM DUAL;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(192938002)
------------------------------------------------
                                              18

SQL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(185263788) FROM DUAL;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(185263788)
------------------------------------------------
                                          714412

从这个地方我们可以看出，root索引块的地址为dba_segments 中 header_block加1，在ASSM下，首extent的前2个数据块保存位图信息，第三个数据块为header_block. 在mssm下，首extents的第一个数据块即为HEADER_BLOCK.

查看索引块信息

语法格式：alter system dump datafile file_id block block_id;

                    alter system dump datafile file_id block min block_id block max block_id;

dump root branch：

SQL> select dbms_utility.data_block_address_block(185263788) c1,dbms_utility.data_block_address_file(185263788) c2 from dual;

      C1       C2
-------- --------
  714412       44

SQL> oradebug setmypid
已处理的语句
SQL> oradebug tracefile_name
/oracle/admin/orcl/udump/orcl_ora_73926.trc
SQL> alter system dump datafile 44 block 714412;

系统已更改。

trace file 内容

*** 2013-12-13 11:52:38.766
Start dump data blocks tsn: 4 file#: 44 minblk 714412 maxblk 714412
buffer tsn: 4 rdba: 0x0b0ae6ac (44/714412)
scn: 0x0012.4a18d046 seq: 0x01 flg: 0x06 tail: 0xd0460601
frmt: 0x02 chkval: 0xacfe type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000110432000 to 0x0000000110434000
110432000 06A20000 0B0AE6AC 4A18D046 00120106  [........J..F....]
110432010 ACFE0000 02030022 00058FCC 4A18D044  [......."....J..D]
110433FB0 024846FE 0B0AE6AF 02545AFE 0B112280  [.HF......TZ...".]
110433FC0 024E4BFE 31322F31 32303639 07787005  [.NK.12/12069.xp.]
110433FD0 19101A35 272F6F72 61636C65 2F61646D  [...5'/oracle/adm]
110433FE0 696E2F6F 72636C2F 6264756D 702F616C  [in/orcl/bdump/al]
110433FF0 6572745F 6F72636C 2E6C6F67 D0460601  [ert_orcl.log.F..]
Block header dump:  0x0b0ae6ac
 Object id on Block? Y
 seg/obj: 0x58fcc  csc: 0x12.4a18d044  itc: 1  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0xb0ae6a9 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.006.000492f3  0x008130ca.aee2.01  -BU-    1  fsc 0x0000.4a18d046
 
Branch block dump
=================
header address 4567801932=0x11043204c
kdxcolev 2
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x83: opcode=3: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 2
kdxconro 1
kdxcofbo 30=0x1e
kdxcofeo 8048=0x1f70
kdxcoavs 8018
--kdxbrlmc 185672316=0xb11227c
kdxbrsno 491
kdxbrbksz 8056 
kdxbr2urrc 0
--row#0[8048] dba: 185672320=0xb112280
col 0; len 2; (2):  4e 4b
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 4 file#: 44 minblk 714412 maxblk 714412

dump leaf block：

SQL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(185263789) FROM DUAL;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(185263789)
------------------------------------------------
                                          714413

SQL> alter system dump datafile 44 block 714413;

系统已更改。

tracefile 内容：

*** 2013-12-13 12:20:29.107
Start dump data blocks tsn: 4 file#: 44 minblk 714413 maxblk 714413
buffer tsn: 4 rdba: 0x0b0ae6ad (44/714413)
scn: 0x0012.4a19790f seq: 0x01 flg: 0x06 tail: 0x790f0601
frmt: 0x02 chkval: 0xcff9 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000110432000 to 0x0000000110434000
110432000 06A20000 0B0AE6AD 4A19790F 00120106  [........J.y.....]
110432010 CFF90000 02000000 00058FCC 4A19790E  [............J.y.]
110432020 00120058 00023200 0B0AE6A9 00060000  [...X..2.........]
110432030 00049288 008128AC AEE10100 C0000012  [......(.........]
...
110433FB0 E8BB01CC 00000641 41565A4F 45060B0A  [.......AAVZOE...]
110433FC0 E74200A1 0717021C 3C020F02 C10602C1  [.B......<.......]
110433FD0 120D7871 0B081701 0B21A1DD 001C3C02  [..xq.....!....<.]
110433FE0 C10B02C1 0B018001 80018001 80FFFF01  [................]
110433FF0 800180FF 0778710B 0717021C 790F0601  [.....xq.....y...]
Block header dump:  0x0b0ae6ad
 Object id on Block? Y
 seg/obj: 0x58fcc  csc: 0x12.4a19790e  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0xb0ae6a9 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.000.00049288  0x008128ac.aee1.01  CB--    0  scn 0x0012.4a184c24
0x02   0x0008.025.0003bd64  0x00813810.8882.06  --U-    1  fsc 0x0000.4a19790f
 
Leaf block dump
===============
header address 4567801956=0x110432064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 10
kdxconro 317
kdxcofbo 670=0x29e
kdxcofeo 2960=0xb90
kdxcoavs 2290
kdxlespl 0
kdxlende 0
kdxlenxt 185672019=0xb112153
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[3712] flag: ------, lock: 0, len=16
col 0; len 6; (6):  41 41 41 42 43 53
col 1; len 6; (6):  0b 11 22 f9 00 95
row#1[4464] flag: ------, lock: 0, len=16
col 0; len 6; (6):  41 41 41 43 47 4c
col 1; len 6; (6):  0b 0a e8 94 00 20
...
row#315[8000] flag: ------, lock: 0, len=16
col 0; len 6; (6):  41 41 56 58 58 44
col 1; len 6; (6):  0b 0a e8 bb 01 cc
row#316[8016] flag: ------, lock: 0, len=16
col 0; len 6; (6):  41 41 56 5a 4f 45
col 1; len 6; (6):  0b 0a e7 42 00 a1
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 44 minblk 714413 maxblk 714413

tracefile格式解析：

块头区域：

rdba:相对数据块地址
scn : 修改块的最后scn号
seq:  在当前scn号下，数据块改变的次序
tail: scn的最后两个字节
frmt: 数据块的格式 (02 represents a post Oracle8 block format, A2 10g format)
chkval: 校验和
type: 06 – transactional data block type (table/index/cluster)
Hex dump of block: only displayed on later versions of Oracle

seg/obj – object id
csc: commit/cleanout SCN
itc: interested transaction count (defaults 1 branch block, 2 leaf blocks)
typ – block type (2 – index)
Itl – Interested Transaction Location:
Itl: slot id,
Xid: transaction id, 
Uba: undo block address, 
Flag : state of current transaction (C – Committed)
Lck : number of locks help by current transaction
Scn/Fsc: scn /fsc of current transaction

kdxcolev: index level (0 represents leaf blocks)
kdxcolok: denotes whether structural block transaction is occurring
kdxcoopc: internal operation code
kdxconco: index column count
kdxcosdc: count of index structural changes involving block
kdxconro: number of index entries (does not include kdxbrlmc pointer)
kdxcofbo: offset to beginning of free space within block
kdxcofeo: offset to the end of free space (i.e.. first portion of block containing index data)
kdxcoavs: available space in block (effectively area between kdxcofbo and kdxcofeo)

分支块专用头信息

kdxbrlmc: block address if index value is less than the first (row#0) value（例如在root块中,明明有两条记录，却只包含一个row，这是因为第一个可以通过dkxbrlmc来确定）
kdxbrsno: last index entry to be modified
kdxbrbksz: size of usable block space

叶子块专有头信息

kdxlespl: bytes of uncommitted data at time of block split that have been cleaned out
kdxlende: number of deleted entries
kdxlenxt: pointer to the next leaf block in the index structure via corresponding rba
kdxleprv: pointer to the previous leaf block in the index structure via corresponding rba
Kdxledsz: deleted space
kdxlebksz: usable block space (by default less than branch due to the additional ITL entry)

记录区域：

branch entity

Row number (starting at #0) followed by [starting location in block] followed by the dba
Column number (starting at 0) followed by column length followed by column value
Repeated for each indexed column
Repeated for each branch entry
Note: column value is abbreviated to smallest value that uniquely defines path

leaf entity （unique）

Row number (starting at #0) followed by [starting location within block] 
followed by various flags (deletion flag, locking information etc.) followed by 
total length of index entry followed by the rowid
Index column number (starting at 0) followed by column length followed by 
column value
Repeated for each indexed column
Repeated for each index entry
Note: Total overhead is 3 bytes for each leaf index entry (unique index)

leaf entity（ununique）

Row number (starting at 0) followed by [starting location within block] followed by various 
flags (deletion flag, etc locking information) followed by length of index entry 
Index column number (starting at 0) followed by column length followed by column value
Repeated for each indexed column with last column in non-unique index being the rowid of 
index entry (hence making the index entry effectively unique anyways)
Repeated for each index entry
Note: Total overhead is 4 bytes, 1 more than unique index