What is oracle index inversion

In Oracle, index flipping refers to byte reversal of the index key value of the index column when creating an index, so as to achieve the purpose of dispersedly storing different child node blocks. It is a "B-tree" A branch of the index; using the reverse index, the key values ​​of the index columns can be reversed, so that the sequential key values ​​are dispersed into different leaf blocks, thereby reducing contention for index blocks.

The operating environment of this tutorial: Windows 10 system, Oracle version 12c, Dell G3 computer.

What is oracle index inversion

As a branch of the B-tree index, the reverse index mainly performs byte inversion on the index key value of the index column when creating the index. Invert, thereby achieving the purpose of dispersed storage into different leaf

sub-node blocks.

Problems targeted by reverse index

Using traditional B-tree index, when the index columns are generated in order, the corresponding index key value will Basically distributed in the same leaf block. When users operate on this column, contention for index blocks will inevitably occur.

Use an inverted index to reverse the key values ​​of the index column and disperse the sequential key values ​​into different leaf blocks, thereby reducing contention for index blocks.

For example: the key values ​​1001, 1002, 1003, after inversion, are 1001, 2001, 3001, and then dispersed to unused leaf node blocks.

When loading some ordered data, the index will definitely encounter some bottlenecks related to I/O. During data loading, some parts of the index and disk are bound to be used much more frequently than others. To solve this problem, the index table space can be stored on a disk architecture that can physically split the files across multiple disks.

To solve this problem, Oracle also provides a method to reverse the key index. If data is stored with a reversed key index, the values ​​of the data will be the opposite of the values ​​originally stored. In this way, data 1234, 1235 and 1236 are stored as 4321, 5321 and 6321. The result is that the index updates a different index block for each newly inserted row.

Reversal key processing cannot be performed on bitmap indexes and index-organized tables.

Tip:

If you have limited disk capacity and are performing a large number of ordered loads, you can use a reverse key index.

You cannot use inverted key indexes with bitmap indexes or index-organized tables.

ORACLE DBA practice: Create a reverse keyword index

For large online transaction processing systems, the tables are generally very large, and the primary keys of these tables It is generated by the serial number, and there is an index on it. Therefore, adjacent index records may exist in the same data block, which will cause competition for data blocks. As time goes by, old useless data will be continuously deleted and new useful data will be inserted into these tables. Obviously, the deleted data is old data with a small serial number, while the inserted data has a newly generated serial number. (must be a large number), which causes another serious problem: that is, the index tree is biased toward the side with larger sequence numbers, which will deepen the depth of the tree and reduce system efficiency.

In order to solve the above problems, Oracle introduced the inverted keyword index. Reverse keyword indexing is achieved by reversing the order of each byte of the keyword (serial number) and then re-indexing. For example:

As can be seen from the above, the original adjacent order# are no longer adjacent after being reversed, so that the adjacent order# can be dispersed to In different data blocks, the above problems are easily solved.

But things are always divided into two, and there are also problems with the reverse index keyword. For example, you will run into trouble when performing range queries on tables with reversed key indexes. Under normal indexing, index records that meet the conditions should be placed in the same or adjacent data blocks, which will be very fast; however, after using the reverse keyword index, the original adjacent index records that meet the conditions have been Scattered into multiple data blocks, the only way to complete the query operation is to perform a full table scan. Therefore, when deciding whether to use a reverse keyword index, it is necessary to distinguish whether the insertion operation is important or the range query is important.

Recommended tutorial: "

Oracle Video Tutorial

"

The above is the detailed content of What is oracle index inversion. For more information, please follow other related articles on the PHP Chinese website!