8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Reverse Key Indexes

Reverse key indexes literally reverse the bytes of the key value in the index to reduce block contention on sequence generated primary keys.

Scalable Sequences were introduced in Oracle 18c as an alternative to using reverse key indexes.

Related articles.

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.

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 contents of the table column is unchanged, only how the key is represented in the index block. As a result, there is nothing to see unless you dump the contents of the index blocks.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.