Introduction to Oracle B-Tree indexes (B-Tree indexes)

B-tree indexes are the most common indexes (traditional indexes ).

The simple creation method is: create index test_id_idx on test (id ).

The following figure illustrates the B-Tree Index:

①: From top down, the first layer is the root node, the second layer is the branch node, and the third layer is the leaf node (including the column value and rowid ).

Rowid reference

②: This is the case when you look for it. For example, if you want to find the value of 11, it will start from the heel node, and 11 will start from 0 to 50 and point to the first box on the leftmost left of the branch node, go to the left-side first box of the branch node and find the second box pointing to the left of the leaf node in 11-19. Then go to the second box on the left of the leaf node to find the value of 11, so we can find the value of two 11 S and the response rowid. If you only search for the value of the index column, you do not need to search for the table based on rowid. If you want to search for the row with the value of 11, the values of other columns must be searched for in the table based on rowid. (Except in the same cluster, the rowid of each record may be unique ).
③: The leaf node also has a two-way linked list (). It takes effect when range scanning is performed through the index. For example, if you want to search for a value ranging from 10 to 19, if you find a value ranging from 10, you will not start searching for other values from the same node, you can find other values based on the link of the leaf node linked list.