Oracle Indexing Principle

• B-tree Index

A B-Tree index has only one root node, which is actually the branch node at the top of the tree. You can use a to describe the structure of a B-tree index. where b represents a branch node, and L represents a leaf node.

For a branch node block (including the root node block), it contains index entries that are sorted in order (by default ascending, or specified in descending order when the index is created). Each index entry (which can also be called a record) has two fields. The first field represents the minimum key value contained in the index block that is currently linked under the branch node block, and the second field is four bytes, which represents the address of the linked index block, which points to the following index block. 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. For example, from one can see, for the root node block, contains three records, respectively (0 B1), (B2), (B3), they point to three branch node block. 0, 500, and 1000 respectively represent the minimum value of the key value linked by the three branch node blocks. The B1, B2, and B3 represent the addresses of the three branch node blocks pointed to.

For a leaf node block, it contains index entries in the same order as the branch nodes (the default is ascending, or you can specify descending order when you create the index). Each index entry (which can also be called per record) also has two fields. The first field represents the key value of the index, which is a value for a single-column index, and multiple values are combined for a multicolumn index. The second field represents the rowid of the record line corresponding to the key value, which is the physical address of the record line in the table. If the index is created on a non-partitioned table or if the index is a local index on the partitioned table, the rowID occupies 6 bytes, and the rowID occupies 10 bytes If the index is a global index created on the partitioned table.

• Bitmap index

A bitmap (bitmap) index is another type of index that is organized in the same way as the B-tree index and is a balanced tree. The difference from the B-tree index is that the index entries are stored in the leaf node in different ways. From the previous we know that 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, a corresponding index entry is maintained for that record row in the leaf node. The bitmap index is not the case, as shown in the index entry in its leaf node.
Suppose that all records in a table t have only three values on the column C1:01, 02, and 03. After the bitmap index is created on the C1 column of table T, the leaf node's content 9-14 is shown. 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 ROWID for the first record in the table and the corresponding ROWID for the last record. The last part of the index entry is a bitmap consisting of multiple bit bits, each of which corresponds to a record.

When a SQL statement such as where c1= ' 01 ' is emitted, Oracle searches for the index entry where 01 is located, and then scans all the bit bits in bitmap in that index entry. The first bit bit is 1, which indicates that the C1 value on the first record is 01, so the rowid of the first record is returned (based on the start rowID recorded in the index entry plus the line number to get the rowid of the record). The second bit is 0, which indicates that the C1 value on the second record is not 01, and so on.        In addition, if the index column is empty, it is also 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 relatively small, such as for the gender column (male or female), it is better to use a bitmap index, because it occupies very little space (because it is a bit bit to represent the table data rows), so when scanning index, the number of scanned index block is also relatively small. Imagine that if you create a bitmap index on a column with a different value on the columns, such as a primary key column, the resulting index entry equals the number of bars recorded in the table, and the bitmap in each index entry is only 1, and the others are 0. This is not as efficient as the B-tree index. If the indexed column is frequently updated, it is not appropriate to use a bitmap index. Because when you update the column in which the bitmap is located, because you want to modify the bit bit between different index entries, such as changing the first record from 01 to 02, you must change the first bit of the index entry for 01 to 0, and then change the first bit bit of the index entry where 02 is located to 1. Therefore, in the process of updating an index entry, multiple index entries in the anchor index are locked. That is, only one user can update the table T at the same time, thus reducing concurrency. Bitmap indexes are better suited for use in Data Warehouse systems and are not suitable for OLTP systems.

• Hash index

Hash clusters must be used to use hash indexes. 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 the data is stored, all the rows associated with the cluster key are stored on a database block. If the data is stored on the same database block, and the hash index is the exact match in the WHERE clause, Oracle can access the data by executing a hash function and I/O-while accessing the data by using a B-tree index with a two-yuan height of 4. You need to use 4 I/O when retrieving data. 2-5, where 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 can be the quickest way to access data in a database, but it also has its 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. A number that underestimates the different values of cluster keys can cause cluster conflicts (the key values of two clusters have the same hash value). This conflict is very resource-intensive. Collisions cause a buffer overflow to store additional rows and then cause 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 may also waste space. If you cannot determine how much space is required to maintain all the rows on a cluster key, you can create a waste of space. If additional space cannot be allocated for future growth of the cluster, the hash cluster may not be the best choice.

Hash clusters may not be the best choice if the application frequently makes full table scans on the cluster tables. Full table scans can be very resource-intensive due to the need to allocate the remaining amount of space in the cluster for future growth.

Be careful before you implement a hash cluster. You need to look at the application thoroughly to ensure that you already know a lot of information about tables and data before you implement this option. In general, hashing is very effective for some static data that contains ordered values.

Skills:

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

• Index of the clustered family

Here or in the dictionary to the analogy, generally speaking, there are several indexes in Chinese dictionary, such as pinyin, radicals, strokes and so on. The organization of the dictionary itself is also sorted, and I remember that it is generally sorted by pinyin. The pinyin here is the clustered index.       This means that the organization order of the clustered index is consistent with the organization order of the data itself, which explains why only one clustered index can be defined in the database, because the data itself can only be sorted in one way. What are the special benefits of the clustered index, which is that the statement that executes the lookup batch of data in the database is faster because the data has been sequenced in accordance with the clustered index, and very few IO operations can take the data out of the library. As you look in the dictionary to pronounce the kanji from a to C, you only need to find the start page of a and the end page of C, all the pages in the middle meet the query requirements, no More page by page.

• Non-clustered index

Non-clustered indexes are like the radicals in a dictionary, the Stroke Index, the Index organization order and the data organization order are inconsistent, so the non-clustered index can create more than one. When finding a single piece of data, the efficiency of non-clustered and clustered indexes is not very significant, but when searching for a batch of data (n), the IO required for a nonclustered index may be n times the clustered index, because the nonclustered index needs to be looked up one piece at a time.

This digest from http://orange5458.iteye.com/blog/1165319

Oracle Indexing Principle