Index Block Dump: Index Only Section Part I (TVC 15)

Having already covered general block header details relevant to several different types of Oracle blocks (Block Dumps Part I and Part II), the next part of the block dump is relevant only to index blocks.

Below is a dump of the index only section of an index leaf block dump:

Leaf block dump
===============
header address 200514140=0xbf39a5c
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 3
kdxcofbo 42=0x2a
kdxcofeo 7987=0x1f33
kdxcoavs 7945
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036

 
First a note identifies the block as being a Leaf block dump or a Branch block dump.

All index block dumps (whether an index leaf or branch) have the details listed above up to and including kdxcoavs. Specific points of interest for details included in any index block dump:

kdxcolev 0 – index level (a value of 0 denotes this as a leaf block)

kdxconco 2 – number of columns in the index. Note: although the index was only defined on the single NAME column, the index actually includes the rowid as an additional column, as the index was defined as Non-Unique (hence the value 2). All Oracle indexes are effectively unique as Oracle makes them so by adding the rowid to the column list for all Non-Unique defined indexes.

kdxconro 3 – number of index entries in the index block (this block dump was taken when the index only had the 3 entries, prior to the insertion of the additional rows in Part II in this series)

kdxcofbo 42=0x2a – offset to the beginning of free space within the block

kdxcofeo 7987=0x1f33 – offset to the end of free space within the block. Note index entries get added “from the bottom” of the index free space and so it’s from this offset that any subsequent index entries will be added.

kdxcoavs 7945 – available free space within the block (effectively the space between kdxcofbo and kdxcofbe)

The following details are only included if the block is an index leaf block.

kdxlende 0 – number of index entries that have been marked as deleted within the block (this will be discussed more fully later). When an index entry is deleted (or indeed updated which is effectively a delete of an index entry followed by an insert), the entry is not physically deleted but is only marked as deleted. Generally these deleted index entries are subsequently cleaned out by Oracle but kdxlende keeps a count of those deleted index entries that have yet to be cleaned out.

kdxlenxt 0=0x0 – pointer to the next index leaf block within the logical index structure (as this is the only leaf block within the index, there is no pointer set although this is usually only unset for the last or “right most” leaf block within the index structure). During a larger index range scan, all the required index entries may not be found within a single index leaf block and Oracle may need to quickly find the next logical leaf block within the index structure. This pointer basically contains the Relative Block Address (RBA) of the next logical leaf block which can be accessed directly as necessary during such index range scans.

kdxleprv 0=0x0 – pointer to the previous index leaf block within the logical index structure (as this is the first and only leaf block within the index, again there is no pointer set although this is usually only unset for the first or “left most” leaf block within the index structure). Again, during a larger index range scan in which the data is required in descending order, all the required index entries may not be found within a single index leaf block and Oracle may need to quickly find the previous logical leaf block within the index structure. This pointer basically contains the RBA of the previous logical leaf block which can be accessed directly as necessary. As such, it’s used by Oracle for index range scans that require the data to be extracted in logically reverse order (eg. to avoid an order by desc). Note only a leaf block associated with a blevel 0 index has neither the kdxlenxt or kdxleprv assigned.

These two pointers provide the linked list mechanism for all such index range scan operations involving more than one leaf block. Note that a branch block doesn’t have such pointers between branch blocks but it does have a pointer to the first block it references below itself in the index structure (kdxbrlmc).

kdxlebksz 8036 – actual maximum useable space within the index block (basically the block size less the block header “overheads”). Note that an index branch block has an equivalent value called kdxbrbksz which typically has more useable space as a branch block only has the one ITL entry by default.

  
Next, we’ll look at the index entries themselves.