Do you know about Oracle B-tree indexes?

Before giving you a detailed introduction to Oracle B-tree indexes, let's first take a look at non-unique indexes and then give a full introduction to Oracle B-tree indexes. The Oracle B-tree index does not have a unique entry.

In a non-unique index, Oracle appends rowid to the key as an additional column to make the key unique.

Exp: create index I on T (x, y). In terms of concept, Create unique index I on T (x, y, rowid ). Oracle will first sort by the index key value and then sort by rowid in ascending order.

In a unique index, data is only sorted by the index key value.

1. In most cases, the index height of the Oracle B-tree is 2 or 3. Therefore, it usually takes 2 or 3 times to find a key in the index.

2. all the leaf blocks of the Oracle B-tree index should be on the same layer, and the leaf nodes are actually two-way linked lists. In this way, when performing index range scan, you only need to move the leaf node forward or backward, without having to navigate the index structure.

3. Compress columns with duplicate values in the Oracle B-tree index to increase the cache hit rate and reduce the I/O count.
Related entries may all exist in a block. Exp: create index I on T (userid, username) username = 'steven 'this value may correspond to multiple rowids placed in the same index block ); however, the negative effect is that the index structure is complicated, the index maintenance time is longer, and the query index takes more CPU time. Compression is suitable for multiple column indexes)

4. The reverse key index of the Oracle B-tree index is mainly used to relieve the busy waiting in the buffer zone on the right side of the index. It is suitable for primary keys similar to the primary keys generated by sequence, because these columns do not need to use range scanning, that is, they do not use max (PK), min (PK ), between and or where PK <200

5. if order by colum1 asc, column2 desc exists in the query, try to create index I on T (colum1 asc, column2 desc) when creating the INDEX, because Oracle index is sorted by DESC by default, sorting in indexes is always much better than sorting in disks.

6. Suitable for Oracle B-tree indexes:
1) a small proportion of rows in the Access Table
2) The table is not accessed at all, and all the data to be queried is in the index.
3) in a special usage, indexes are used to access all rows in the table. This is used to make the initial response time of the query very short, not to optimize the total throughput.

7. The index is stored in the order of the index key, and the index will be accessed in the ordered order of the keys. The block to which the index points is randomly stored in the heap.

8. Suggestion: Use the Oracle B-tree index in the thin table to query <2-3% columns and <20-25% columns in the fat table.

1. Analysis and Discussion on Oracle Database Restoration
2. Oracle remote disk image
3. New Oracle 11g function display
4. Detailed analysis of Oracle XML data
5. Oracle Data Guard