Understand the internal structure of B * tree index and the internal structure of tree

Understand the internal structure of B * tree index and the internal structure of tree

Reprinted please indicate the source: http://write.blog.csdn.net/postedit/40589651

The B-tree index in the Oracle database is like an inverted-long tree, which contains two types of data blocks: one is the index branch block, the other is that the index leaf block index branch block contains pointers and index key columns pointing to the corresponding index branch block/leaf block (here the pointer refers to the relevant branch block/leaf block ). address: RDBA.

Each index branch block has two types of pointers. One is LMC, and the other is the pointer recorded by the index Row Records of the index branch. lmc is the abbreviation of Left Most Child. Each index branch block has only one lmc, the maximum value of all index key columns in the branch block/leaf block to which the lmc points must be smaller than the minimum value of all index key columns in the indexed branch block where the lmc is located; the minimum value in all index key-value columns of the branch/leaf block pointed to by the pointer recorded in the index row record of the index branch block must be greater than or equal to that of the index key-value column recorded in the row value ).

Note: This key-value column is not necessarily a complete indexed key value. It may only be the prefix of the indexed key value. As long as Oracle can distinguish the corresponding index branch blocks/leaf blocks through these prefixes, Oracle can save the storage space of the index branch, you can quickly locate the index branch/leaf block at the lower layer.

In fact, if you use an accurate name to describe the B-Tree index in a relational database, it cannot be called B-Tree index, but B *-Tree index.
Because it is not necessary to have a very accurate learning name, we generally use its Generalized name. The T-tree index structure is similar to a tree structure as its name indicates. It spreads from the trunk to the branches until the leaves.
 
Some people think it is the first letter of Binary.
Some people think it is the abbreviation of the person who first proposed the theory
But it is more common to say that it is the abbreviation of the word "balanced"-balanced

The "Lmc" in the branch block header refers to the address of the lower-level data block (DBA) that is smaller than the first data value in the branch block ), this representation not only reduces the number of rows to be stored in the block, but also indicates "not full. "Not full" means that the value in the index on the left of the branch block is smaller than the value in the first row of the branch block. The Term in the branch block represents a part of the column value that is not described in the lower-layer index block. Here, it is just to simplify the problem, it is somewhat similar to the comparison operator LIKE which can abbreviated 'abc % 'As 'abc '.

See the following index structure:

 

/+ * Drop table gyj_t1; create table gyj_t1 (id int, name varchar2 (100); begin for I in 1 .. 5000 loop insert into gyj_t1 values (I, 'gyj' | I); commit; end loop; end;/create index idx_gyj_t1 on gyj_t1 (id ); unique index drop index idx_gyj_t1; create unique index idx_gyj_t1 on gyj_t1 (id); gyj @ ZMDB> select object_id from dba_objects where object_name = 'idx _ GYJ_T1 '; OBJECT_ID ---------- 24515gyj @ ZMDB> select header_file, header_block from dba_segments where segment_name = 'idx _ GYJ_T1 '; HEADER_FILE HEADER_BLOCK ----------- ---------- 7 2618 +/

Alter session set events 'immediate trace name treedump level 100 ';

----- Begin tree dump
Branch: 0x1c00a3b 29362747 (0: nrow: 110, level: 1)
-- DBA (hexadecimal, decimal)
Starting at-1 TB t root starting at 0
Nrow: 110 branck or leaf
Level: branch block level (leaf block implicitly 0)
Root block split (usually level changed), branch block split, leaf block split (5-5, 9-1)
High = level + 1

Leaf: 0x1c00a3c 29362748 (-1: nrow: 485 rrow: 485)
Except for root, all the other operations start from-1,
Nrow: number of all index entries (including deleted entries)
Rrow: number of current index entries
Level: leaf block implicitly 0
----- End tree dump

**************************************** **************************************** * ************* Dump a shard
Alter system dump datafile 7 block 2619;

Branch block dump
========================
Header address 139782541810252 = 0x7f21a8c01a4c
Kdxcolev1 ++ index level (0 represents leaf blocks) level is 1,
It indicates that this is a branch block> 0 (root block)
Kdxcolevflags = ---
Kdxcolok 0 ++ denotes whether structural block transaction is occurring
Indicates whether a transaction is modifying the block structure on the index;
Kdxcoopc 0x80: opcode = 0: iot flags = --- is converted = Y ++ internal operation code indicates the internal Oracle operation code
Kdxconco 2 ++ index column count indicates the number of columns in the index entry.
Kdxcosdc 0 ++ count of index structural changes involving block
The number of changes in the index structure. This value increases when you modify an index key value in the table;
Kdxconro 109 ++ number of index entries (does not include kdxbrlmc pointer)
Number of entries in the current index, excluding the lmc pointer
Kdxcofbo 246 = 0xf6 ++ offset to beginning of free space within block
Kdxcofeo 6987 = 0x1b4b ++ offset to the end of free space (I. e .. first portion of block containing index data)
Kdxcoavs 6741 ++ available space in block (effectively area between kdxcofbo and kdxcofeo)
Kdxbrlmc 29362748 = 0x1c00a3c ++ block address if index value is less than the first (row #0) value
Kdxbrsno 0 ++ last index entry to be modified
The last modified index number. The value 0 indicates that the index is a new index.
Kdxbrbksz 8056 ++ size of usable block space indicates the size of available data blocks
Kdxbr2urrc 0 ++ is not described here in the Oracle internal document. I did not find this structure through bbed observation.

Row #0 [8047] dba: 29362749 = 0x1c00a3d
Col 0; len 3; (3): c2 05 57
Col 1; TERM
Row #1 [8038] dba: 29362750 = 0x1c00a3e
Col 0; len 3; (3): c2 0a 42
Col 1; TERM


Sys @ ZMDB> select UTL_RAW.CAST_TO_NUMBER ('c20557 ') from dual;

UTL_RAW.CAST_TO_NUMBER ('c20557 ')
--------------------------------
486 (nrow: 485)


Sys @ ZMDB> select UTL_RAW.CAST_TO_NUMBER ('c20a42 ') from dual;

UTL_RAW.CAST_TO_NUMBER ('c20a42 ')
--------------------------------
965 (nrow: 479 ==> 486 + 479)

**************************************** **************************************** Dump leaf Blocks
Alter system dump datafile 7 block 2620;

Leaf block dump
====================
Header address 140046263134820 = 0x7f5f0fc42a64
Kdxcolev0 ++ index level (0 represents leaf blocks)
Kdxcolevflags = ---
Kdxcolok 0 ++ denotes whether structural block transaction is occurring (indicates whether a structured block transaction is happening)
Kdxcoopc 0x80: opcode = 0: iot flags = --- is converted = Y ++ internal operation code
Kdxconco 2 ++ index column count
Kdxcosdc 0 ++ count of index structural changes involving block
)
Kdxconro 485 ++ number of index entries (does not include kdxbrlmc pointer)
Kdxcofbo 1006 = 0x3ee ++ offset to beginning of free space within block
Kdxcofeo 1830 = 0x726 ++ offset to the end of free space (I. e .. first portion of block containing index data)
Kdxcoavs 824 ++ available space in block (effectively area between kdxcofbo and kdxcofeo)
Kdxlespl 0 ++ bytes of uncommitted data at time of block split that have been cleaned out
Indicates the number of transactions not committed when the leaf node is split.
Kdxlende 0 ++ number of deleted entries
Indicates the number of index entries to be deleted.
Kdxlenxt 29362749 = 0x1c00a3d ++ pointer to the next leaf block in the index structure via corresponding rba
The address of the next leaf node of the current leaf node.
Kdxleprv 0 = 0x0 ++ pointer to the previous leaf block in the index structure via corresponding
The address of the previous leaf node of the current leaf node.
Kdxledsz 0 ++ deleted space indicates the available space, which is currently 0
Kdxlebksz 8032 ++ usable block space (by default less than branch due to the additional ITL entry)
Row #0 [8020] flag: ------, lock: 0, len = 12 ++ row header (1 byte) + flag (2 byte) + lock (1 byte) + col 8 = 12
++ Flag indicates a tag, such as a delete tag, and lock indicates a lock.
Col 0; len 2; (2): c1 02 ++ index key value
Col 1; len 6; (6): 01 c0 00 87 00 00 ++ file number (2 byte) + block number (2 byte) + row number (2 byte)
Row #1 [8008] flag: ------, lock: 0, len = 12
Col 0; len 2; (2): c1 03
Col 1; len 6; (6): 01 c0 00 87 00 01


Gyj @ ZMDB> select id, name, histogram (rowid) file #, dbms_rowid.rowid_block_number (rowid) block #, dbms_rowid.rowid_row_number (rowid) row # from gyj_t1 where id = 1;

Id name file # BLOCK # ROW #
--------------------------------------------------
1 gyj1 7 135 0

0x01 c0 00 87 00 00 00 ==> File No.: 01 c0 Block No.: 00 87 line No.: 00 00 00

......................

 

What is the implementation principle of ORACLE bitmap index and B * tree index?

1. Syntax
Create bitmap index index_name on table name (field );
2. For example, you can understand:
Table test (id, name, address)
Data
(1, Zhang San, Dalian)
(2, Li Si, Tianjin)
(3, Wang Wu, Beijing)
(4, Zhao 6, Dalian)
....

If such data is used during query, the general index cannot accelerate the query because there is a lot of such data in the database, the bitmap index will produce the following bitmap effects:
Suppose there are 4 data records (as shown above)

Dalian, Tianjin, Beijing
1 0 0
0 1 0
0 0 1
1 0 0

In this case, when querying:
Select * from table where address = 'dalian 'or address = 'beijing ';
In this case, the database will soon be able to determine the data that meets the requirements based on 1 and 0 in the same row.

---
The above is helpful to you.

BSTree * tree; BSTree tree; where are their differences? The following is the BSTree * tree that defines the pointer of a struct type;

If BSTNode represents a fruit
BSTress indicates that there is a fruit tree above
Tree indicates a circle with more than one fruit tree

Tree indicates the pointer to the pointer