What can you achieve by implementing reverse key index?
A.
Reverse the bytes of each column indexed including the row ID
B.
Store a bitmap for each key value instead of a list of row IDs in the leaf node
C.
Prevent contention on the highest leaf block when using sequences to generate keys
D.
Remove repeated key values from the index to fit more index entries in a given amount of disk space
Reverse Key Indexes
A reverse key index is a type of B-tree index that physically reverses the bytes of each
index key while keeping the column order. For example, if the index key is 20, and if
the two bytes stored for this key in hexadecimal are C1,15 in a standard B-tree index,
then a reverse key index stores the bytes as 15,C1.
Reversing the key solves the problem of contention for leaf blocks in the right side of a
B-tree index. This problem can be especially acute in an Oracle Real Application
Clusters (Oracle RAC) database in which multiple instances repeatedly modify the
same block. For example, in an orders table the primary keys for orders are sequential.
One instance in the cluster adds order 20, while another adds 21, with each instance
writing its key to the same leaf block on the right-hand side of the index.
In a reverse key index, the reversal of the byte order distributes inserts across all leaf
keys in the index. For example, keys such as 20 and 21 that would have been adjacent
in a standard key index are now stored far apart in separate blocks. Thus, I/O for
insertions of sequential keys is more evenly distributed.
Because the data in the index is not sorted by column key when it is stored, the reverse
key arrangement eliminates the ability to run an index range scanning query in some
cases. For example, if a user issues a query for order IDs greater than 20, then the
database cannot start with the block containing this ID and proceed horizontally
through the leaf blocks.