Databases Reference
In-Depth Information
new values in the same "hot" index block, the database spreads around the new entries across a number
of blocks, reducing contention for a busy block and thus, buffer contention(denoted by the buffer busy
wait event).
You can see how an index that's populated with sequentially increasing values results in all values
going to the rightmost index leaf block. Let's say you create a primary key with the following values:
696900
696901
696902
696903
696904
…
In a regular B-tree index, these sequential index values are all stored in an identical rightmost index
block, increasing contention for that block. In a reverse key index, Oracle Database will insert the same
values in the following manner:
009696
109696
209696
309696
409696
…
As you can see, the index values aren't in sequential order, although the actual values of the primary
key are. Oracle reverses the bytes in the data before inserting into the index. This reversing of the values
naturally spreads the key values by storing them non-sequentially all through the index blocks instead of
storing them in sequential order. During an insertion, the reverse key index distributes insertions across
all the leaf keys in the index, thus avoiding hotspots and enhancing performance.
Using a reverse key index will often speed up data loads in an Oracle RAC environment during batch
loads. While reverse key indexes are often mentioned in the context of an Oracle RAC environment, you
can consider them even for a single instance databases if you notice contention for index blocks, shown
by the buffer busy wait event. There are many applications where a primary key column is populated by
an increasing sequence. Often, you'll notice a significant buffer busy wait time for the index segments.
Consider a reverse key index for these kinds of situations. The reverse key index has the potential to
dramatically reduce the buffer busy waits and speed up performance.
Disadvantages of a Reverse Key Index
A big disadvantage to using a reverse key index is that you can't perform range scans on these indexes.
This is because the index entries are scattered all over instead of being stored sequentially. Reversing the
index key values randomly distributes the index blocks across the index leaf nodes. You can certainly use
the index to fetch by specific index key values and for a full index scan, however. Use reverse key indexes
when your queries have equality predicates. Note also that even though the database doesn't perform a
range scan when you use a reverse key index, it can perform a fast full scan of the index. There could be a
slight overhead in terms of CPU usage when the database searches on a reverse key index. The reason, of
course, is that the database has to reverse the order of bytes in the values of the index so they represent
the actual values stored in the table.
Remember that the main purpose of using a reverse key index is to eliminate contention caused
when the database is inserting rows with index key values generated by a sequence. Other solutions are
