Database Reference
In-Depth Information
Reverse Key Indexes
Another feature of a B*Tree index is the ability to reverse its keys. At first you might ask yourself, “Why would I want to
do that?” B*Tree indexes were designed for a specific environment and for a specific issue. They were implemented
to reduce contention for index leaf blocks in “right-hand-side” indexes, such as indexes on columns populated by a
sequence value or a timestamp, in an Oracle RAC environment.
■
Note
We discussed raC in Chapter 2.
RAC is a configuration of Oracle in which multiple instances can mount and open the same database. If two
instances need to modify the same block of data simultaneously, they will share the block by passing it back and
forth over a hardware
interconnect
, a private network connection between the two (or more) machines. If you have a
primary key index on a column populated from a sequence (a very popular implementation), everyone will be trying
to modify the one block that is currently the left block on the right-hand side of the index structure as they insert new
values (see Figure
11-1
, which shows that higher values in the index go to the right and lower values go to the left).
Modifications to indexes on columns populated by sequences are focused on a small set of leaf blocks. Reversing
the keys of the index allows insertions to be distributed across all the leaf blocks in the index, though it could tend to
make the index much less efficiently packed.
■
You may also find reverse key indexes useful as a method to reduce contention, even in a single instance of
Oracle. again, you will mainly use them to alleviate buffer busy waits on the right-hand side of a busy index, as described
in this section.
Note
Before we look at how to measure the impact of a reverse key index, let's discuss what a reverse key index
physically does. A reverse key index simply reverses the bytes of each column in an index key. If we consider the
numbers 90101, 90102, and 90103, and look at their internal representation using the Oracle
DUMP
function, we will
find they are represented as follows:
EODA@ORA12CR1> select 90101, dump(90101,16) from dual
2 union all
3 select 90102, dump(90102,16) from dual
4 union all
5 select 90103, dump(90103,16) from dual
6 /
90101 DUMP(90101,16)
---------- ---------------------
90101 Typ=2 Len=4: c3,a,2,2
90102 Typ=2 Len=4: c3,a,2,3
90103 Typ=2 Len=4: c3,a,2,4
Each one is 4 bytes in length and only the last byte is different. These numbers would end up right next to each
other in an index structure. If we reverse their bytes, however, Oracle will insert the following:
90101 reversed = 2,2,a,c3
90102 reversed = 3,2,a,c3
90103 reversed = 4,2,a,c3
