Tips: 如何快速判断一个index的level
今天为blog新添增了一项分类: oracle tips。用于整理一些简单实用的tips
这里所说的level就是指dba_indexes中的blevel,不过blevel需要analyze后才存在
对于一些比较大的index,analyze需要很长时间，而且重新analyze可能会影响SQL的执行计划
这时我们可以通过直接dump root block块来得到index level
举例如下:
SQL> select segment_name,header_file,header_block from dba_segments where segment_name=’T_IDX’ and owner=’XFAN’;
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
——————————————————————————— ———– ————
T_IDX 8 37
block# 37为segment header,紧接着的就是root block , block# 38
SQL> alter system dump datafile 8 block 38;
System altered.
dump文件位于udump下，截取下面一段出来：
Branch block dump
=================
header address 206920260=0xc555a44
kdxcolev 2
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 2
kdxconro 18
kdxcofbo 64=0×40
kdxcofeo 7897=0x1ed9
kdxcoavs 7833
kdxbrlmc 33555635=0x20004b3
kdxbrsno 17
kdxbrbksz 8056
其中kdxcolev就是block所在的level,因为该block为root block，所以index的level为2
对于其他字段，Richard Foote在《oracle B-Tree Index Internals: Rebuilding The Truth》一文中有详细解释
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 (ie. first portion of block containing index data)
kdxcoavs: available space in block (effectively area between the two fields above)
kdxbrlmc: block address if index value is less than the first (row#0) value
kdxbrsno: last index entry to be modified
kdxbrbksz: size of usable block space
