Manage index tables: In-depth study on the impact of B-tree indexes-DML on B-tree indexes (theoretical article 2)

B-tree index access

When an oracle process needs to access data blocks in a data file, Oracle has two types of I/O operations:

1) Random Access: Read a data block each time (represented by the "DB file sequential read" Wait event ).

2) sequential access: read multiple data blocks each time (as shown in the "DB file scattered read" event ).

The first method is to access the data blocks in the index, and the second method of I/O operations is full table scan. Here, there is a question: why does Random Access correspond to DB file sequential read wait events, while sequential access corresponds to DB file scattered read wait events? This seems to be the opposite. Random Access should be scattered, and sequential access should be sequential. In fact, wait events are named based on the actual method of obtaining physical I/O blocks, rather than based on the Logical Method of the I/O sub-system. The following describes how to obtain index data blocks. In fact, although the B-tree index is a tree-like three-dimensional structure, the arrangement corresponding to the data file is of course still a plane form, that is, like below.

/Root/branch/leaf /... /Leaves/branches/leaves /... /Leaves/branches/leaves /... /Leaf/branch /.....

Therefore, when Oracle needs to access an index block, it is bound to move on this structure.

When Oracle needs to obtain an index block, it first starts from the root node and knows the branch node of the next layer based on the key value to be searched, then, access the branch nodes at the next layer, and then access the branch nodes at the next layer based on the key value. In this way, access the leaf nodes at the bottom layer. It can be seen that the physical I/O block is sequential and sequential. In the process of obtaining the final physical block, we cannot read multiple blocks at the same time, because we do not know which block to access when the current block is not obtained. Therefore, when accessing the data block on the index, it will correspond to the DB file sequential read wait event. The root cause is that we jumped from one index block to another in order, to find the final index block.
For full table scan, there is no need to access the previous block before accessing the next block. During full table scan, Oracle knows to access all data blocks, so the only problem is to access these data blocks as efficiently as possible. Therefore, Oracle can synchronize multiple data blocks in batches. These batches of data blocks may be physically scattered in the table, so they correspond to the DB file scattered read wait event.

InsertB-tree index impact
The description of B-tree index insertion can be divided into two types: one is how the index is managed when an index is created on a table already full of data; the other is how indexes are managed when one row is followed by another row to insert, update, or delete data into the table.

The first case is relatively simple. When you create an index (create Index Command) on a table full of data, Oracle will first scan the data in the table and sort it, then a leaf node is generated. After all leaf nodes are generated, several levels of branch nodes are generated based on the number of leaf nodes, and the root node is generated. This process is clear.

The second case is much more complicated, and the leaf node will be split. This process can be divided into two types: one is that the inserted key value is not the maximum value; the other is that the inserted key value is the maximum value. When a non-maximum key value (First case) When you want to enter the index, but find that the index block to be entered is insufficient to accommodate the current key value:

1. obtain a new index data block from the index availability list

2. Divide the index entries that are full of indexes into two parts, one with a small key value and the other with a large key value. Oracle will move the portion with a large key value into the new index data block, while the portion with a small key value remains unchanged.

3. Insert the current key value into an appropriate index block, which may be an index block with insufficient space or a new index block.

4. Update the kdxlenxt information of the index block with insufficient space to point it to the new index block.

5. Update the kdxleprv in the index block on the right of the original insufficient index block to point it to the new index block.

6. add an index entry to the branch index block at the upper level of the index block with insufficient space. The index entry stores the minimum key value in the new index block, and the address of the new index block.

It can be seen from the above process of splitting leaf nodes that the process is very complicated. Therefore, if the inserted key value is the maximum value (Situation 2To simplify the split process, Oracle omitted Step 1 above, but directly entered Step 2 to insert the new key value into the new index block.

When more and more leaf nodes cause the original root node to be insufficient to store new index entries (these index entries point to the leaf node), the root node must be split. When the root node is split:

1. obtain two new index data blocks from the index availability list.

2. Divide the index entries in the root node into two parts, which are placed into two new index blocks to form two new branch nodes.

3. Update the index entries of the original root node so that they point to the two new index blocks respectively.

Therefore, the index level becomes two layers. At the same time, we can see that the root node index block is always the same index block physically. As the amount of data increases, branch nodes need to be split. The splitting Process of the branch node is similar to that of the root node (in fact, the root node splitting is actually a special case of the branch node splitting ):

1. obtain a new index data block from the index availability list.

2. Divide the index entries in the full branch nodes into two parts. The smaller key value does not move, and the larger key value is moved into the new index block.

3. Insert new index entries into appropriate branch index blocks.

4. Add a new index entry to the top branch index block to point it to the new branch index block.

When the data volume increases, the original root node is insufficient to store new index entries (these index entries point to the branch node), the root node is split again, the split process is the same as that of the root node split caused by the increase of leaf nodes.Each splitting process requires a certain amount of overhead, especially for physical hard disk I/O operations.

Impact of delete on B-tree indexes

Before introducing the mechanism for deleting the index key value, we will first introduce an important view related to the index: index_stats. This view displays a large amount of internal index information. Normally, this view does not have any data and will be filled with data only after the following command is run, in addition, this view can only store one index-related record that has been analyzed, without the second record. At the same time, only sessions that run the command can view the data in this view. Data in other sessions cannot be seen.

Analyze index index_name validate structure;

One disadvantage of this command is that the entire table is locked during running, blocking other sessions to insert, update, and delete tables. This is because the main purpose of this command is not to fill the index_stats view. Its main function is to verify that each valid index entry in the index corresponds to a row in the table, at the same time, each row of data in the table has a corresponding index entry in the index. To achieve this purpose, You need to lock the entire table during the running process. For a large table, running this command takes a lot of time.

In the index_stats view, height indicates the height of the B-tree index; blocks indicates the number of allocated index blocks, including those not used yet; pct_used indicates the percentage of space used in the current index. The value is calculated based on (used_space/btree_space) * 100 in this view. Used_space indicates the space in use, while btree_space indicates the total space occupied by the index; del_lf_rows indicates the number of records to be deleted (the data in the table is deleted and the index entries corresponding to the index are not immediately cleared from the index blocks, which will be discussed later ); del_lf_rows_len indicates the total space occupied by the deleted records; lf_rows indicates the total number of records contained in the index, including the number of deleted records. In this case, the number of records that are not deleted in the index is the lf_rows-del_lf_rows. At the same time, we can calculate the space occupied by index entries (that is, valid index entries) corresponding to records not deleted as (used_space-del_lf_rows_len)/btree_space) * 100.

The deletion of indexes is summarized as follows:

1. When deleting a record in the table, the index entries corresponding to the index are not physically deleted, but are marked as deleted.

2. when a new index entry enters an index leaf node, Oracle checks whether there are any index entries marked as deleted in the leaf node. If yes, all index entries with the delete tag are physically deleted from the leaf node.

3. when a new index entry enters the index, Oracle will reclaim all the currently cleared leaf nodes (all the index entries on the leaf node are set to delete tags, and then become an available index block again.

Although the space occupied by the deleted index entries can be reused in most cases, there are still some situations that may result in a waste of index space, and cause many index data blocks but few index entries. In this case, the index can be considered as fragmented. The index fragmentation mainly includes:

1. unreasonable and high pctfree. Obviously, this will reduce the available space of the index block.

2. The index key value continues to increase (for example, using sequence to generate the key value of the serial number), and deleting the index key value in sequence may cause index fragmentation. As we know before, some index entries are deleted from an index block. The space can be reclaimed only when a key value enters the index block. The increasing index key value will always be inserted into the index block at the top. Therefore, the space in the index cannot be recovered, but only when all the index entries contained in the index are deleted, this index block can be reused.
3. Key values that are often deleted or updated, and will almost no longer be inserted in the future, this situation is similar to the above situation.

The method for determining whether an index is fragmented is very simple: directly run analyze index... Validate structure command, and then check the index_stats ViewThe pct_used field of. If the field is too low (less than 50%), it indicates that there are fragments.

UpdateImpact of B-tree indexes
The impact of value update on index entries can be considered as a combination of deletion and insertion. That is, set the index entry corresponding to the updated old value to the D (delete) Mark, and insert the updated value to the appropriate index block in order. I will not repeat it here.

 

Summary:

1. Avoid indexing columns that may produce high update actions.

2. Avoid indexing multiple columns in tables that are frequently deleted. If possible, index only the primary keywords and/or columns that will be deleted on such tables. If it is inevitable to index multiple columns, you should consider dividing the tables based on these columns, and then perform the truncate action (rather than the delete action) on each of these columns ). When using truncate together with the drop storage phrase, You can reset the high-water mark to simulate the process of deleting a table and an index, and re-creating a table and an index.

3. Avoid creating B * tree indexes for columns with low uniqueness. This low selectivity will result in the density of Tree node blocks, resulting in large-scale index scanning due to index "flat. The higher the degree of uniqueness, the better the performance, because it can reduce the scope of scanning, or even use a unique scan to replace the range of scanning.

4. null values are not stored in a single column index. For composite indexes, values are stored only when a column is not empty. When creating an is null or is not null phrase for a DML statement, remember this question.

5. Is null does not cause index scanning, and A is not null without any restrictions may cause full index scanning.