oraclebtreeindex索引块结构初探

浏览： ℃

字体：大中小

发布时间：2013-12-17 09:37:18

来源：

通过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----------    364492SQL> 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                            MANUALUSERS                          AUTOSQL> col segment_name for a20SQL> col tablespace_name for a20SQL> 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       714411I2                   TS1                           46           17SQL> 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            17I1                                0        714409SQL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(192938002) FROM DUAL;DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(192938002)------------------------------------------------                                              18SQL> 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       44SQL> oradebug setmypid已处理的语句SQL> oradebug tracefile_name/oracle/admin/orcl/udump/orcl_ora_73926.trcSQL> alter system dump datafile 44 block 714412;系统已更改。

trace file 内容

*** 2013-12-13 11:52:38.766Start dump data blocks tsn: 4 file#: 44 minblk 714412 maxblk 714412buffer tsn: 4 rdba: 0x0b0ae6ac (44/714412)scn: 0x0012.4a18d046 seq: 0x01 flg: 0x06 tail: 0xd0460601frmt: 0x02 chkval: 0xacfe type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x0000000110432000 to 0x0000000110434000110432000 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/Fsc0x01   0x0006.006.000492f3  0x008130ca.aee2.01  -BU-    1  fsc 0x0000.4a18d046 Branch block dump=================header address 4567801932=0x11043204ckdxcolev 2KDXCOLEV Flags = - - -kdxcolok 1kdxcoopc 0x83: opcode=3: iot flags=--- is converted=Ykdxconco 2kdxcosdc 2kdxconro 1kdxcofbo 30=0x1ekdxcofeo 8048=0x1f70kdxcoavs 8018--kdxbrlmc 185672316=0xb11227ckdxbrsno 491kdxbrbksz 8056 kdxbr2urrc 0--row#0[8048] dba: 185672320=0xb112280col 0; len 2; (2):  4e 4bcol 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)------------------------------------------------                                          714413SQL> alter system dump datafile 44 block 714413;系统已更改。

tracefile 内容：

*** 2013-12-13 12:20:29.107Start dump data blocks tsn: 4 file#: 44 minblk 714413 maxblk 714413buffer tsn: 4 rdba: 0x0b0ae6ad (44/714413)scn: 0x0012.4a19790f seq: 0x01 flg: 0x06 tail: 0x790f0601frmt: 0x02 chkval: 0xcff9 type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x0000000110432000 to 0x0000000110434000110432000 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/Fsc0x01   0x0006.000.00049288  0x008128ac.aee1.01  CB--    0  scn 0x0012.4a184c240x02   0x0008.025.0003bd64  0x00813810.8882.06  --U-    1  fsc 0x0000.4a19790f Leaf block dump===============header address 4567801956=0x110432064kdxcolev 0KDXCOLEV Flags = - - -kdxcolok 0kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Ykdxconco 2kdxcosdc 10kdxconro 317kdxcofbo 670=0x29ekdxcofeo 2960=0xb90kdxcoavs 2290kdxlespl 0kdxlende 0kdxlenxt 185672019=0xb112153kdxleprv 0=0x0kdxledsz 0kdxlebksz 8032row#0[3712] flag: ------, lock: 0, len=16col 0; len 6; (6):  41 41 41 42 43 53col 1; len 6; (6):  0b 11 22 f9 00 95row#1[4464] flag: ------, lock: 0, len=16col 0; len 6; (6):  41 41 41 43 47 4ccol 1; len 6; (6):  0b 0a e8 94 00 20...row#315[8000] flag: ------, lock: 0, len=16col 0; len 6; (6):  41 41 56 58 58 44col 1; len 6; (6):  0b 0a e8 bb 01 ccrow#316[8016] flag: ------, lock: 0, len=16col 0; len 6; (6):  41 41 56 5a 4f 45col 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 entityRow 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

>更多相关文章