[Oracle] B-Tree index and bitmap index __oracle

11.3 B-Tree Index

At the top of the index is the root node, which contains items that hold pointers to the next level of the index. Next is the branch node (block), and the record (item) in the branch node is a pointer to the next level (block). The lowest layer is the leaf node. The index entry in the leaf node that points to the data row in the table. Leaf nodes are linked by a two-way chain of links to facilitate scanning in ascending or descending order of index keywords.

The overall structure of the B-tree index is introduced, and the internal structure of index entries (records) for index leaf nodes is now introduced. Index entries (records) are made up of three parts, which are:

Ø Index Header (Entry header): Information that stores the number of rows and locks.

Ø index column length and value: must appear in pairs, it defines the length of the column, immediately after the length is the value of the column.

Ørowid: Refers to the ROWID of the data row in the table.

Index entries (records) of leaf nodes in a B-tree index on a non-partitioned table have the following characteristics:

Ø index key values are stored repeatedly if there are multiple rows of data rows with the same index key value and the index is not compressed.

Ø data rows with null (NULL) values for all index columns, Oracle will not store the corresponding index entries. Therefore, if the value of the column that contains the index in the WHERE clause is null,oracle, a full table scan will not be used without the index.

Ø used to point to data rows is a restrictive rowid because all data rows belong to the same segment. The advantage of using restrictive rowid in an index entry is that it saves the storage space for the index.

When you perform a DML operation on a table, the Oracle server automatically maintains all indexes that are based on the table. Its maintenance methods are as follows:

Ø inserts a row of index entries into the corresponding index data block when inserting (insert) the table.

Ø When a table is deleted (delete), the Oracle server only makes a logical deletion of the index entries. That is, adding a tag to a deleted index entry does not really delete the item, but only if all items in that block are deleted before they are actually deleted.

Ø when the table is modified (UPDATE), the Oracle server actually carries out two operations on the index, one for the logical deletion and the other for the insert operation.

After introducing the B-Tree index, we'll start with a bitmap index that describes the structure of another index that may be used frequently in Oracle. 11.4-bit graph index

Compared to the B-tree index, in some cases the lower-graph index has more advantages. Bitmap indexing is also a B-tree structure, but the leaf node of a bitmap index is not rowid but a bitmap of each key value.

Each bit in the bitmap corresponds to a possible rowid, and if this bit is placed, it means that the rowid corresponds to a row that contains the key value. The leaf nodes of the bitmap index contain the following sections:

Ø Index Header (Entry header): Information that stores the number of rows and locks.

Ø Key values: the length and value of the indexed column. In the example in Figure 11-2, the index key is composed of only one column, and the key value of its last index entry is "hero".

Ø starting rowID: The starting address of the bitmap, the rowid of the first row in the Ascended map. Includes the relative file number, the block number in the relative file, and the line number in the block.

Ø Terminate rowID: The ending address of the bitmap, the rowid of the last line in the picture. It also includes the relative file number, the block number in the relative file, and the line number in the block.

Ø Bitmap Segment (Bitmap segment): Is composed of a string of bits. a position (1) indicates that the corresponding row of the bit contains the key value of the index. If the bit is not set bit (0), it means that the row for that bit does not contain the key value of the index. The Oracle server uses a patented compression technology to compress the bitmap segments and then store them, so bitmap indexing can save a lot of storage space.

Below we follow the bitmap shown in Figure 11-2, giving a practical explanation:

See key value for Bachelor's first line:

The bitmap ends with the No. 0 line from the 14th (data) block of document 4th to the 16th line of the 8th (data) block of the 4th file. The first row in the bitmap is a bachelor (because the corresponding bit in the bitmap is 1), the second row is not a bachelor, the third line is not, the fourth line is not, but the fifth line is, and so on.

Next, see the last line of the key value for the warrior:

Bitmaps are also completed from the No. 0 line of the 14th (data) block of document 4th to the 16th line of the 8th (data) block of the 4th file. The first row of records in a bitmap is not a hero (because the corresponding bit in the bitmap is 0, in fact, the line is already a bachelor, of course, can no longer be a hero, the second line is not a hero (because the line is already a doctor, of course, can no longer be a hero), the third line is the heroes (because the corresponding bit in the bitmap is 1), and so on. Comparison of 11.5 B-Tree index and bitmap index

With so many B-tree indexes and bitmap indexes, what are the differences between the two indexes? Table 11-1 gives a summary of their comparative results.

Table 11-1

Here's a detailed explanation of the conclusions given in chart 11-1. I have consulted some English dictionaries to find the exact Chinese meaning of cardinality, but I didn't find a satisfactory answer. The meaning of low-cardinality here is that the values of columns can be enumerated, such as sex and marital status, and the degree in our example. And the meaning of high-cardinality is that the value of the column is difficult to enumerate, such as names.

The storage structure of the bitmap index described in the previous section shows that using a bitmap index for a low-cardinality column is much more compact than a B-tree index, which saves a lot of disk space, which reduces the input/output and improves the efficiency of the system.

In addition, because a bitmap index requires much less storage space than a B-tree index, a generic Oracle server loads the entire bitmap index segment into memory when using a bitmap index. This actually turns a search process on disk into a memory lookup process, which greatly improves the efficiency of the system. Oracle defines the size of the memory area using the parameter Create_bitmap_area_size in the initialization parameter file. The default value is 8 MB.

The reader should note that the B-tree index mentioned in Figure 11-1 is relatively inexpensive to make changes to the closing column, and is not really expensive, but is not expensive compared to a bitmap index, which is actually expensive for any type of index. Modifying a key-value column (an indexed column) in a bitmap index requires a segment-level lock, while a B-tree index is a row-level lock used, and a bitmap may be adjusted in this case. Therefore, the modification of the closing columns in the bitmap index is very expensive.

When a bitmap index is logically operated, the Oracle server uses a bitwise operation, so the logical operation of the bitmap index is highly efficient.

The final conclusion is that B-tree indexing may be more appropriate for online transaction processing (OLTP) systems because of the frequent DML operations in online transaction processing systems. Bitmap indexes may be more appropriate for data warehousing (Warehouse) systems, because tables are generally large but static in the Data warehouse system and queries are more complex.

Other differences: The bitmap index performs better when the filter condition operator of the indexed column is "=", or when the table joins, it also indexes the null value. The B-Tree index is better when the index column has a filter condition of ">", "<", and there is no row in the index that contains null values.