Lesson 5	Reverse indexing
Objective	Create a reverse index in Oracle

Create Reverse Key Index in Oracle

A reverse key index (often called a “reverse index”) is a specialized form of Oracle B-tree index that helps in a very specific scenario: high-concurrency inserts into a sequentially increasing key, such as a primary key populated from a sequence.

In a standard B-tree, new, higher key values tend to land on the “right edge” of the index. Under heavy insert load—especially in Oracle RAC—many sessions can contend for the same rightmost leaf blocks, producing a hot spot that slows inserts. A reverse key index mitigates this by reversing the bytes of the indexed key before storing it, which spreads inserts across many leaf blocks instead of concentrating them at the right edge.

Oracle handles the reversal transparently: your SQL still uses the original key value. The reversal is only an internal storage and access mechanism.

Problem and Solution delivered by Reverse Key Indexes

1) Standard B-tree index: increasing keys concentrate inserts on the right edge leaf blocks. — 1) Standard B-tree index: sequentially increasing keys (for example, 101, 102, 103…) tend to insert into the rightmost leaf blocks, which can become a hot spot under concurrency.

2) Heavy insert concurrency can cause right-edge leaf block contention and frequent splits. — 2) Under heavy concurrent inserts, many sessions compete for the same right-edge blocks; contention and block splits can increase latency and reduce throughput (a common pain point in OLTP and RAC).

3) Reverse key index spreads inserts across many leaf blocks by reversing key bytes. — 3) Reverse key index: Oracle reverses the key bytes before storing them, distributing insert activity across many leaf blocks rather than concentrating it on the right edge.

4) With inserts spread across leaf blocks, hot-block contention is reduced. — 4) Because inserts are spread across the index structure, hot-block contention is reduced, which can improve insert scalability for sequential keys.

When to Use a Reverse Key Index

Reverse key indexes are not a general-purpose tuning feature. Consider them when:

Your indexed key is sequentially increasing (sequence-based numeric keys are the classic case).
You have high insert concurrency and evidence of index hot-block contention (particularly in RAC).
Your access pattern is primarily equality lookups on the indexed column(s).

For most applications, the default B-tree index remains the right choice. Reverse key is a tactical option for a narrow performance bottleneck.

Oracle Index Types Summary

Oracle supports many index types and features. Several “types” are actually variations on the B-tree theme. The table below highlights a small subset relevant to this lesson:

Index Type	Usage
B-tree	Default balanced tree index; best for high-cardinality columns, equality predicates, and selective range predicates.
Index-organized table	Table data is stored in a B-tree structure organized by the primary key; efficient when most access is by primary key.
Unique	Enforces uniqueness; commonly created implicitly by PRIMARY KEY or UNIQUE constraints, but can be created explicitly.
Reverse key	B-tree variant; spreads inserts for sequential keys to reduce right-edge contention. Best for equality lookups, not ranges.

Syntax to Create a Reverse Key Index

The syntax is the standard CREATE INDEX statement with the addition of the REVERSE keyword. The REVERSE keyword applies to the entire index (not to individual columns).

CREATE INDEX index_name
ON table_name (column_list)
REVERSE;

Example:

CREATE INDEX cust_rev_idx
ON cust (cust_id)
REVERSE;

CREATE INDEX	Required keywords.
index_name	Unique name for the index.
ON	Required keyword.
table_name	Name of the table the index will be based on.
column_list	List of indexed columns whose combined key will be stored as a reversed byte sequence.
REVERSE	Creates the index as a reverse key index to spread insert activity for sequential keys.

How to Confirm an Index Is Reverse Key

You can confirm whether an index is reverse key by checking the REVERSE attribute in the data dictionary:

SELECT index_name, reverse
FROM   user_indexes
WHERE  index_name = 'CUST_REV_IDX';

Disadvantages of Reverse Key Index

The primary trade-off is that a reverse key index generally cannot support efficient range scans. Because stored keys are reversed, adjacent logical key values are not stored adjacent in the index. As a result:

Good fit: equality predicates such as WHERE cust_id = :b1 (unique scans or equality-based range scans).
Poor fit: range predicates such as WHERE cust_id BETWEEN :a AND :b or queries that rely on index order for sorting.

Oracle can still perform a fast full scan^[1] of a reverse key index in some cases, but that is an I/O pattern similar to scanning a segment rather than walking keys in order. Reverse key also adds a small CPU overhead because Oracle must reverse key bytes during index maintenance and access.

If your root problem is “hot index blocks,” other strategies may be viable, including partitioning approaches that distribute activity across multiple logical index structures rather than reversing keys.

In the next lesson, you will learn about computing statistics for indexes.

[1]fast full scan: Reads the index using multiblock I/O (similar to a full scan) and can be efficient when the index contains all columns needed by the query, but it does not preserve key order like an ordered index range scan.