Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to understand the reverse key index in oracle. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

A reverse key index is an B-tree index that physically changes the bytes of each index key while maintaining the column order (reverse key indexes reverse the bytes of each index column except ROWID and still). For example, if the index key is 20, and if the two bytes stored for this key in hexadecimal are C1, then it is 15 bytes in the standard b-tree index, then a reverse key index stores the bytes as 15 ~ # C1.

Advantages:

The reverse key solves the problem of contention on the right side of the b-tree index. This problem is particularly acute in Oracle Real Application cluster (Oracle RAC) databases, where multiple instances repeatedly modify the same block. For example, in the orders table, the primary key of the order is sequential. One instance in the cluster adds order 20 and another instance adds 21, and each instance writes its keywords to the same leaf block on the right side of the index.

In a reverse key index, the reversal of byte order is distributed over all leaf keys in the index. For example, keys such as 20 and 21 adjacent to each other in the standard key index are now stored separately in different blocks (the index will be on the left, middle, right-everywhere). So the argument just disappeared. Therefore, the Istroke O of sequential key insertion is more evenly distributed.

Disadvantages:

However, it must be pointed out that now the entire index is best in the buffer cache, and before-- only the hot right side needed to be effectively inserted in the cache. If the index cannot be cached, we will most likely turn the busy buffer wait into a physical IO wait, which may be worse (remedies are worse than symptoms).

Because the data in the index is not sorted by column keys when stored, the reverse key arrangement eliminates the ability to run index range scan queries in some cases. For example, if a user issues a query against an order ID greater than 20, the database cannot start with the block that contains the ID and then proceed horizontally through the leaf block.

Summary:

These indexes are designed to eliminate index hotspots on inserted applications. These indexes perform very well for inserts, but they are limited because the database cannot use them for index range scanning.

Syntax:

Create index on (,)

REVERSE

On how to understand the reverse key index in oracle to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.