Oracle Foundation Article---B-Tree index internal structure

Internal structure

The B-Tree index is dumped into a tree-like form and rendered:

alter session set events ‘immediate trace name treedump level INDEX_OBJECT_ID‘;

  sql> alter session SET events ' immediate trace name treedump level 126545 '; Session altered. [[email protected] trace]$ pwd/home/oracle/app/oracle/diag/rdbms/tonytest/tonytest/trace[[email  Protected] trace]$ VI tonytest_ora_5439.trc-----begin tree Dumpbranch:0x34000e3 54526179 (0:nrow:444, level:1) leaf : 0x34000e4 54526180 ( -1:nrow:393 rrow:393) leaf:0x34000e5 54526181 (0:nrow:377 rrow:377) leaf:0x34000e6 54526 182 (1:nrow:378 rrow:378) leaf:0x34000e7 54526183 (2:nrow:377 rrow:377) leaf:0x34000e8 54526184 (3:nrow:377  rrow:377) leaf:0x34000e9 54526185 (4:nrow:378 rrow:378) leaf:0x34000ea 54526186 (5:nrow:379 rrow:379) Leaf: 0x34000eb 54526187 (6:nrow:379 rrow:379) leaf:0x34000ec 54526188 (7:nrow:379 rrow:379) ...-----end of the tree dump, and so on .... 

• The first column of each row indicates that the node type >:branch represents the branch node (including the root node), while the leaf represents the leaf node;
• The second column represents the address of the node represented by hexadecimal;
• The third column represents the address of the node represented by decimal;
• The fourth column represents the position relative to the previous node, the root node is calculated starting from 0, and the other branch nodes and leaf nodes are calculated from 1.
• The nrow of the fifth column represents the number of index entries contained in the current node. For example, we can see that the root node contains a nrow of 444, indicating that the root node contains 444 index entries, pointing to 444 branch nodes respectively;
• The Rrow in the sixth column represents a valid index entry (because the index entry is not immediately purged from the index block if it is deleted). So the number of Nrow minus Rrow indicates the number of indexed entries that have been deleted, for example, for the first Leaf, whose rrow is 393, which means that the leaf node holds 393 usable index entries, pointing to 393 Records of the table Emp3, respectively.

Dump an index node

alter system dump datafile file# block block#;

  Select Dbms_utility.data_block_address_file (54526179), Dbms_utility.data_block_address_block (54526179 ) from Dual;dbms_utility. Data_block_address_file (54526179) dbms_utility. Data_block_address_block (54526179)---------------------------------------------------------------------------- -----------------227sql> Alt Er system dump datafile block 227; System altered. Branch block Dump=================header address 140301252250188=0x7f9a6e4d4a4ckdxcolev 1KDXCOLEV Flags =---kdxcolok 0 KDXCOOPC 0x80:opcode=0:iot flags=---is Converted=ykdxconco 2kdxcosdc 0kdxconro 443kdxcofbo 914=0x392kdxcofeo 2739=0xab 3kdxcoavs 1825KDXBRLMC 54526180=0x34000e4kdxbrsno 49kdxbrbksz 8056KDXBR2URRC 3  

The

• Kdxcolev represents the index hierarchy number, because we dump the root node, so its level number is 1. The value is 0 for the leaf node, and
• Kdxcolok indicates whether the transaction that modifies the block structure is occurring on the index, and
• KDXCOOPC represents the internal operation code;
• Kdxconco represents the number of columns in the index entry;
The
• KDXCOSDC represents the number of changes in the index structure, which increases when you modify an index key value in the table.
The
• Kdxconro represents the number of index entries in the current index node, but note that the KDXBRLMC pointer is not included;
• KDXCOFBO represents the amount of displacement of the starting point of the available space in the current index node relative to the current block;
• Kdxcofeo represents the amount of displacement relative to the current block at the end of the available space in the current index node;
The
• Kdxcoavs represents the total amount of free space in the current index block, which is obtained by subtracting KDXCOFBO with Kdxcofeo. The
• KDXBRLMC represents the address of the branch node, and
• Kdxbrsno represents the last modified index entry number, if = 0, indicating that the index is a new index;
• Kdxbrbksz represents the space size of the available data blocks. As you can see from here, even if the pctfree is set to 0, you cannot use 8192 bytes.

row#0[8043] dba: 54526181=0x34000e5col 0; len 7; (7):  64 65 76 32 32 34 39col 1; TERMrow#1[8030] dba: 54526182=0x34000e6col 0; len 7; (7):  64 65 76 32 36 32 36col 1; TERMrow#2[8017] dba: 54526183=0x34000e7col 0; len 7; (7):  64 65 76 33 30 30 33col 1; TERMrow#3[8005] dba: 54526184=0x34000e8col 0; len 6; (6):  64 65 76 33 33 38col 1; TERM

Each index entry points to a branch node.
Col 1 represents the address of the linked branch node, which, after a certain conversion, is actually the value of the DBA for the row where the row# is located. If there are no other branch nodes under the root node, Col 1 is a term;
Col 0 represents the minimum key value that the branch node is linked to.

#索引高度为1-----Begin tree dump*** 2015-07-10 07:05:03.971branch:0x34000e3 54526179 (0:nrow:479, level:1) leaf:0x34000 E4 54526180 ( -1:nrow:272 rrow:272) leaf:0x34004c9 54527177 (0:nrow:272 rrow:272) leaf:0x34004d3 54527187 (1:n row:272 rrow:272) leaf:0x34004ab 54527147 (2:nrow:272 rrow:272) leaf:0x34004af 54527151 (3:nrow:272 rrow:272 ) ... leaf:0x34004a9 54527145 (473:nrow:420 rrow:420) leaf:0x34004ad 54527149 (474:nrow:420 rrow:420) L  Eaf:0x34004b1 54527153 (475:nrow:420 rrow:420) leaf:0x34004b5 54527157 (476:nrow:420 rrow:420) leaf:0x34004e2 54527202 (477:nrow:96 rrow:96)-----End Tree dump# simulates inserting data, then splits the root node, the index height is 2sql> alter session set events ' immediate TRA Ce name treedump level 126545 '; Session altered.-----Begin tree Dumpbranch:0x34000e3 54526179 (0:nrow:3, Level:2) Branch:0x34005d7 54527447 ( -1:n row:253, Level:1) leaf:0x34000e4 54526180 ( -1:nrow:272 rrow:272) leaf:0x34005e1 54527457 (0:nrow: 272 rrow:272) Leaf:0x34005f1 54527473 (1:nrow:272 rrow:272) leaf:0x34005fa 54527482 (2:nrow:272 rrow:2 Leaf:0x34005fe 54527486 (3:nrow:272 rrow:272) .... leaf:0x3400779 54527865 (246:nrow:272 rrow:27      2) leaf:0x3400766 54527846 (247:nrow:272 rrow:272) leaf:0x340076a 54527850 (248:nrow:310 rrow:310) leaf:0x340076d 54527853 (249:nrow:379 rrow:379) leaf:0x34005b9 54527417 (250:nrow:271 rrow:271) leaf:0x 34005BD 54527421 (251:nrow:272 rrow:272) branch:0x34007e2 54527970 (0:nrow:256, level:1) leaf:0x34005a7 545 27399 ( -1:nrow:272 rrow:272) leaf:0x34005a8 54527400 (0:nrow:265 rrow:265) leaf:0x34005ac 54527404 (1:n row:272 rrow:272) leaf:0x3400597 54527383 (2:nrow:471 rrow:471) leaf:0x3400582 54527362 (3:nrow:271 Rro w:271) leaf:0x3400586 54527366 (4:nrow:267 rrow:267) ... leaf:0x3400273 54526579 (249:nrow:212 rrow : 212) leaf:0x340022c 54526508 (250:nrow:212 rrow:212) leaf:0x3400230 54526512 (251:nrow:212 rrow:212) leaf:0x3400277 5452 6583 (252:nrow:212 rrow:212) leaf:0x340027b 54526587 (253:nrow:213 rrow:213) leaf:0x340027f 54526591 (25  4:nrow:393 rrow:393) Branch:0x34005da 54527450 (1:nrow:278, level:1) leaf:0x34000ff 54526207 ( -1:nrow:213      rrow:213) leaf:0x3400246 54526534 (0:nrow:212 rrow:212) leaf:0x3400240 54526528 (1:nrow:212 rrow:212) leaf:0x3400244 54526532 (2:nrow:212 rrow:212) leaf:0x3400266 54526566 (3:nrow:212 rrow:212) leaf:0 x340026a 54526570 (4:nrow:212 rrow:212) ... leaf:0x34004a5 54527141 (271:nrow:420 rrow:420) leaf:0x 34004a9 54527145 (272:nrow:420 rrow:420) leaf:0x34004ad 54527149 (273:nrow:420 rrow:420) leaf:0x34004b1  54527153 (274:nrow:420 rrow:420) leaf:0x34004b5 54527157 (275:nrow:420 rrow:420) leaf:0x34004e2 54527202 (276:nrow:96 rrow:96)-----End Tree Dumpbranch block Dump=================header address 140408996839500=0x7fb38461404ckdxcolev 2KDXCOLEV Flags =---kdxcolok 0kdxcoopc 0x80:opcode=0:iot flags=---is Converted=ykdxconco 2kdxcosdc 1kdxconro 2kdxcofbo 32=0x20 Kdxcofeo 8030=0x1f5ekdxcoavs 7998KDXBRLMC 54527447=0x34005d7kdxbrsno 0kdxbrbksz 8056KDXBR2URRC 3row#0[8030] DBA: 54527970=0x34007e2col 0; Len 3; (3): 76col 1; Len 6; (6): Dba:54527450=0x34005dacol 0, DC 92row#1[8045] Len 5; (5): 39col 1; Term-----end of Branch block dump-----End dump Data blocks tsn:19 file#: minblk 227 maxblk 227

Indexed access

When Oracle needs to access an index entry in the index, how does Oracle find the block of data that the index entry resides in?

Oracle has two types of I/O operations when Oracle processes need to access data blocks in data files:

• Random access, which reads a block of data each time (by waiting for the event "DB file sequential read" manifests).
• Sequential access, which reads multiple blocks of data each time (by waiting for the event "db file scattered read" to manifest).

The first approach is to access the data blocks in the index, while the second way of I/O operations is a full table scan. Here comes the question of why random access corresponds to the DB file sequential read wait event, while sequential access corresponds to the db file scattered read wait event? This seems to be the reverse, the random access should be scattered (scattered), and sequential access should be sequential (sequential). In fact, wait events are primarily named based on how physical I/O blocks are actually acquired, rather than in the logical way that they are in the I/O subsystem. This is explained in the way that you can get an indexed block of data.

We see the previous description of the architecture of the B-tree index, which can be known as a tree-like stereoscopic structure. It corresponds to the data file.

The arrangement is, of course, a flat form, as in the following. Therefore, when Oracle needs to access an index block, it is bound to jump on this structure.

/roots/Branches/branches/leaves/.../leaves/branches/leaves/leaves/.../leaves/branches/leaves/leaves/.../leaves/branches/...

When Oracle needs to obtain an index block, first starting from the root node, according to the key value to be found, so that the next level of the branch node, and then access the next level of the branch node, again according to the key value of the next level of access to the branch node, and so on, eventually access to the lowest leaf node. It can be seen that when the physical I/O block is obtained, it is one after the other, in sequence, serially. In the process of obtaining the final physical block, we cannot read more than one block at a time because we do not know which block to access next if we do not get the current block. Therefore, when the data block is accessed on the index, it corresponds to the DB file sequential read wait event, which is rooted in the fact that we jump from one index block to another in order to find the final block of the index.

Then for a full table scan, there is no case to access the previous block before accessing the next block. When the full table is scanned, Oracle knows to access all the data blocks, so the only problem is to access them as efficiently as possible. As a result, Oracle can be synchronized in several batches, while acquiring multiple blocks of data. These batches of data blocks may be physically dispersed in the table, so they correspond to the db file scattered read wait event.

Oracle Foundation Article---B-Tree index internal structure