Oracle indexing principle (B-TREE,BITMAP, clustered, nonclustered index) __oracle

A B-Tree index has only one root node, which is actually the topmost branch node in the tree.

You can use the following figure to describe the structure of the B-tree index. where b represents the branch node, and L represents the leaf node.

For a branch node block (including the root node block), the index entries it contains are sorted in order (by default, in ascending order, or in descending order when the index is created). Each index entry (also called each record) has two fields. The first field represents the minimum key value contained in the linked index block under the current branch node block, and the second field is four bytes representing the address of the linked index block, which points to one of the following index blocks. The number of record rows that can be accommodated in a branch node block is determined by the size of the data block and the length of the index key value. As you can see from the image above, there are three records for the root node block (0 B1), (B2), (1000 B3), which point to three branch node blocks. These 0, 500, and 1000 represent the minimum values of the key values linked by these three branch node blocks. B1, B2, and B3 represent the addresses of the three branch node blocks to which they are pointing.

For leaf node blocks, the index entries that they contain are arranged in the same order as the branch nodes (by default, in ascending order, or in descending order when you create the index). Each index entry (also called each record) also has two fields. The first field represents the key value of the index, is a value for a single-column index, and multiple values are grouped together for multiple-column indexes. The second field represents the rowid of the record row for the key value, which is the physical address of the record row in the table or table. rowID If the index is created on a rowid table or if the index is a local index on the partitioned table, it occupies 6 bytes, and if the index is a global index created on the partitioned table, the rowID occupies 10 bytes.

Bitmap Index

The

      bitmap (bitmap) index is another type of index, which is organized in the same way as the B-tree index, and is also a balanced tree. The difference from a B-tree index is that the index entries are stored in different ways in the leaf node. As we know from the front, in the leaf node of the B-tree index, for each data row in the table, if the value of the indexed column is not empty, then a corresponding index entry is maintained for the record row in the leaf node. The
is not the case for bitmap indexes, and the index entries in their leaf nodes are shown in the following illustration.

    assumes that all records in a table t have only three values on the column C1:01, 02, and 03. After you create a bitmap index on the C1 column of table T, the contents of the leaf node are shown in Figure 9-14. As you can see, the bitmap index has only three index entries, that is, the value of each C1 column corresponds to an index entry. The bitmap index entry also contains the corresponding ROWID for the first record in the table and the rowid corresponding to the last record. The last part of the index entry is a bitmap consisting of multiple bit bits, each of which corresponds to a single record.

When an SQL statement such as where c1= ' 01 is emitted, Oracle searches for the index entry in 01 and then scans all bit bits in the bitmap in the index entry. When the first bit bit is 1, the C1 value on the first record is 01, which returns the rowID of the first record (according to the start rowID that is recorded in the index entry and the ROWID of the record). The second bit bit is 0, indicating that the C1 value on the second record is not 01, and so on. In addition, if the index column is empty, it will also be recorded in the bitmap index, that is, the corresponding bit bit is set to 0.
If the number of different values on the index column is less, for example, for gender column (male or female), the use of bitmap index is better, because it occupies very little space (because it is used bit bit to represent the data rows in the table), so that when scanning the index, the number of index blocks scanned is also relatively small. You can imagine that if you create a bitmap index on a column with a very different number of columns, such as a primary key column, the resulting index entry equals the number of records in the table, and the bitmap in each index entry is only 1, and the rest is 0. This is not as efficient as the B-tree index.
Bitmap indexing is not appropriate if the indexed columns are often updated. Because when you update the column that contains the bitmap, because you want to modify bit bits between different index entries, such as changing the first record from 01 to 02, you must change the first bit bit of the index entry in 01 to 0, and then change the first bit of the index entry in 02 to 1. As a result, multiple index entries in the Anchor Map index are locked during the update of the index entry. That is, only one user can update the table T at the same time, thereby reducing concurrency.
Bitmap indexes are better suited for use in data warehousing systems and are not suitable for OLTP systems.

Hash Index

You must use a hash cluster to use a hash index. When a cluster or hash cluster is established, a cluster key is defined. This key tells Oracle how to store tables on a cluster. When data is stored, all rows associated with this cluster key are stored on a database block. If the data is stored on the same database block, and the hash index is an exact match in the WHERE clause, Oracle can access the data by executing a hash function and I/O, and accessing the data by using a B-tree index of two-yuan height 4. You need to use 4 I/O when retrieving data. As shown in Figure 2-5, the query is an equivalent query that matches the hash column and the exact value. Oracle can quickly use this value to determine the physical storage location of a row based on the hash function.

A hash index may be the quickest way to access data in a database, but it also has its own drawbacks. The number of different values on the cluster key must be known before the hash cluster is created. You need to specify this value when creating a hash cluster. Underestimating the number of different values of a cluster key can cause a cluster conflict (the key values for two clusters have the same hash value). This conflict is very resource-intensive. A conflict creates a buffer overflow for storing extra rows, and then creates additional I/O. If the number of different hash values has been underestimated, you must change this value after rebuilding the cluster. The ALTER CLUSTER command cannot change the number of hash keys.

Hash clusters can also waste space. If you cannot determine how much space is needed to maintain all the rows on a cluster key, you can create a waste of space. If additional space is not allocated for future growth of the cluster, the hash cluster may not be the best choice.

A hash cluster may not be the best option if the application often makes a full table scan on the cluster table. Full-table scans can be very resource-intensive because of the need to allocate the rest of the cluster's space for future growth.

Be careful before implementing a hash cluster. You need to observe the application in a comprehensive way, ensuring that you know a great deal about tables and data before implementing this option. Typically, hashes are very effective for some static data that contains ordered values.

Skills:

A hash index is useful in situations where there are constraints, which require specifying a certain value rather than a range of values.

Clustered Index

Here or in the dictionary for analogy, generally speaking, Chinese dictionary has several indexes, such as pinyin, the radical, strokes and so on. The organization of the dictionary itself is also sorted, and I remember that it is usually sorted by pinyin. The phonetic Alphabet here is the clustered index. That is, the organizational order of the clustered index is consistent with the organization order of the data itself, which explains why only a single clustered index can be defined in the database, because the data itself can only be sorted in one way.
What is the special benefit of the clustering index, the advantage is that the execution of the query to find a batch of data in the database is faster, because the data has been sorted according to the clustered index, and very few IO operations can take the data out of the library. Like you look in the dictionary to find pronunciation from a to C Chinese characters, only need to find a start page and C end page, all the pages in the middle of the query to meet the requirements, no more pages to find.

non-clustered index

Non-clustered indexes are like the key in a dictionary, the index of Strokes, the index organization order and the data organization order are inconsistent, so the nonclustered index can create multiple. When looking for a piece of data, the efficiency of the nonclustered index and the cluster index is not quite the same, but when looking for a batch of data (n), the IO required by the nonclustered index may be n times the number of the clustered index, because the nonclustered index needs one by one to find it.