Understanding of Oracle B * tree indexes

1. Dump index block information explanation

1.1 General Index Header

Kdxcolev: index level (0 represents leaf blocks) index level. 0 indicates that it is a leaf block.

Kdxcolok: denotes whether structural block transaction is occurring indicates whether the structure block event occurs;

Kdxcoopc: internal operation code of internal operation code

Kdxconco: Index column count Number of index columns, including rowid

Kdxcosdc: Count of index structural changes involving block

Kdxconro: Number of index entries (does not include kdxbrlmc pointer) Number of index entries, excluding the kdxbrlmc pointer

Kdxcofbo: offset to beginning of free space within Block starting position of free space

Kdxcofeo: offset to the end of free space (ie. First portion of block containing index data)

Kdxcoavs: number of available spaces (kdxcofbo-kdxcofeo) in the specified tively area between the two fields above) block)

1.2 branch header Area

Kdxbrlmc: if the index value is smaller than the first value (row #0), it is the block address of the index value;

Kdxbrsno: The last modified index entry;

Kdxbrbksz: available block space.

1.3 leaf block header Area

Kdxlespl: number of bytes of uncommitted data cleared during block splitting;

Kdxlende: Number of deleted entries;

Kdxlenxt: rba of the next leaf block;

Kdxleprv: RBA of the previous leaf block;

Kdxlebksz: available Block Space (less than the available branch space by default ).

2. Use treedump

Today, a csdn User told me that you can use treedump to view the index structure more conveniently. Based on the original example, continue to learn.

2.1 labs

SQL> select object_id from dba_objects where object_name = 'ti ';

Object_id

----------

53432

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

Session altered

2.2 view the dump file

----- Begin tree dump

Branch: 0x28213c 41943052 (0: nrow: 3, level: 1) corresponding to block 12

Leaf: 0x281_d 41943053 (-1: nrow: 6 rrow: 6) corresponds to 13, 6 rows of data)

Leaf: 0x280000f 41943055 (0: nrow: 2 rrow: 2) corresponds to 15, 2 rows of data (5, 6)

Leaf: 0x280000e 41943054 (1: nrow: 1 rrow: 1) corresponding block rows (7)

----- End tree dump

Nrow: Number of index entries (including delete items );

Rrow: Number of index entries in the current block (excluding delete items );

Level: branch block level (the branch block is 1, and the leaf block is 0 ).

2.3 analyze leaf Information

The decimal number of 02810000f is 41943055.

SQL> select to_number ('0280000f', 'xxxxxxxx') decnum from dual;

Decnum

----------

41943055

From leaf to file #, block #.

SQL> select dbms_utility.data_block_address_file (to_number ('0280000f', 'xxxxxxxx') file #,

2 dbms_utility.data_block_address_block (to_number ('0280000f', 'xxxxxx') block #

3 from dual;

File # block #

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

10 15

3. Dump the branch block and compare it with the above conclusion.

Branch Block: 12

Leaf block: 13, 14, 15

SQL> alter system dump datafile 10 block 12;

System altered

 

Row #0 [8042] DBA: 41943055 = 0x280000f File No. 10, Block No. 15

Col 0; Len 1; (1): 35 the smallest key value in this block is 5

COL 1; term

Row #1 [8049] DBA: 41943054 = 0x2820.e File No. 10, Block No. 14

Col 0; Len 1; (1): 37 the smallest key value in this block is 7

COL 1; term