Databases Reference
In-Depth Information
possible. For example, you can you can partition a hot index rather than make it into a reverse index,
using the partitioning to mitigate the input/output hotspot that you otherwise would have.
You can make the decision whether to implement a reverse key index by comparing the benefits
they provide (the faster insert rates due to the reduction in contention) with the biggest drawback (their
inability to support range-based predicates). If your application uses a primary key that is always used
through an equality predicate, a reverse key index is helpful, especially in an Oracle RAC environment.
Reverse key indexes are very helpful if your application is suffering from a hot right-hand side index
block. If the contention is due to a hot index root block, partitioning the index is potentially helpful
because the index will end up with multiple index root blocks.
If you create a reverse key index and your query specifies
WHERE ind_col = value
, the database will
certainly use the reverse key index. However, since the index values in a reverse key index aren't stored
in their natural order, the optimizer sometimes has a problem with the index. For example, the index
values 54321 and 64321are more likely to be in different index life blocks than they are to be together in
the same block. The database thus has to search for the two values independently as the values are most
likely stored in different data blocks. Thus, the database will be unable to use a reverse index when
handling a range predicate, such as
BETWEEN, <=, <, <,
and
<=.
Regardless of whether you use an
index hint, the database, when confronted with a reverse key index in the context of a range predicate,
will perform a full table scan, ignoring your index.
You can overcome the limitation described here by replacing the range predicate where possible
with an
IN
clause. The database will then transform each
IN
clause into an
OR
clause, which is compatible
with a reverse key index. That is, instead of specifying
BETWEEN
(12345, 12346, 12347), you can specify
IN
(12345, 12346, 12347). The database will change this to 12345
OR
12346
OR
12347 and will be able to use
your reverse key index. Oracle Database can do this because reverse key indexes are fully compatible
with an equality predicate.
There are a couple of exceptions to the technical fact that Oracle can't perform an index range scan
when dealing with a reverse key index. The first is when you use a non-unique index rather than a
unique index. Since duplicate values are stored in the index structure, the database will perform an
index range scan when it does an equality search with the non-unique reverse key index. However, this
is an unlikely event anyway, since the primary reason for using reverse key indexes is to eliminate
contention caused by insertions into a primary key that's populated using monotonically increasing
sequence values. During batch inserts, a reverse key index will usually help make the insertion of data
faster.
As explained earlier, a reverse key index is often considered an ideal solution in cases where a
generated or sequential key causes severe contention on the index leaf blocks. Whenever you use a date
or a sequence with values that increase monotonically, you are likely to encounter this contention,
especially in a RAC environment. However, before you go in for a reverse key index, do consider
alternatives such as a hash-partitioned global index. In a multi-user RAC environment, a hash-
partitioned global index can improve the performance of inserts by spreading out the inserts. Oracle's
hashing algorithm determines the location of the index values in each of the global index's partitions.
The algorithm uses the index's keys to generate unique values that it places in different partitions. When
your application is inserting frequently into segments with indexes that are right-growing, a hash-
partitioned global index can reduce contention.
In a RAC environment, Oracle recommends that you use the
NOORDER
and the
CACHE
options when
creating a sequence in order to reduce index contention. The rowlock cache statistics from the
V$SYSTEM_EVENT
view tells you if monotonically increasing sequences are causing contention in the
database. You can examine the
EQ_TYPE
column in the
GV$ENQUEUE_STAT
view to determine if sequences
are causing any index enqueue waits. If you see a value of
SQ Enqueue
for the
EQ_TYPE
column, it is
usually an indication of contention for a sequence. If the index key values are derived from a sequence,
you can increase the size of the sequence cache to reduce index contention. You can easily raise the
cache size for a sequence with an
alter sequence
statement.
