Reverse Key Indexes

Reverse Key Indexes

A reverse key index is created by including the REVERSE keyword in the index creation.

CREATE INDEX t1_id_idx ON t1 (id) REVERSE;

The Problem

A sequence is a database object used to produce unique integers, which are commonly used to populate a synthetic primary key column in a table. Since the sequence number always increases, typically by 1, each new entry is placed on the right-most leaf block of the index, making it a hot block. By itself this can cause contention, but things can get worse when dealing with a RAC database, where the contention on the right-most leaf block can cause cluster waits, with the RAC instances fighting over the block. Reverse key indexes were introduced in Oracle 8 to help reduce this contention.