Reverse Key Index FAQS

1. What is a Reverse Key Index in Oracle?

A Reverse Key Index is a special type of B-Tree index where the bytes of the indexed column value are reversed before being stored in the index.

Example:

CREATE INDEX emp_id_rev_idx

ON employees(employee_id)

REVERSE;

If employee_id = 12345

Normal index stores:

12345

Reverse index stores:

54321

2. Why do we need a Reverse Key Index?

Reverse key indexes are mainly used to reduce index block contention in high-concurrency systems.

Problem:

• Sequential values (like sequences)
• All inserts go to the right-most leaf block
• Causes hot block contention

Reverse key spreads inserts across the index.

3. What is Hot Block Contention?

When using:

employee_id NUMBER GENERATED BY SEQUENCE

Values increase like:

1001

1002

1003

1004

In a normal B-Tree index:

• All new values go to the right-most block
• Multiple sessions compete
• Leads to buffer busy waits

This is called right-hand growth problem.

Reverse key prevents this.

4. How does Reverse Key solve the problem?

Sequential values:

1001 → 1001

1002 → 1002

1003 → 1003

After reversing:

1001 → 1001

1002 → 2001

1003 → 3001

Now values are distributed across index tree.

Inserts go to different leaf blocks.

Reduces contention significantly.

5. When should you use Reverse Key Index?

Best for:

• Primary keys generated by sequences
• High insert OLTP systems
• RAC environments
• Systems with heavy concurrent inserts

Example:

CREATE INDEX orders_id_rev_idx

ON orders(order_id)

REVERSE;

6. When should you NOT use Reverse Key Index?

Do NOT use if:

• Range queries are required
• BETWEEN conditions are frequent
• ORDER BY on that column is needed

Because reverse key disables efficient range scan.

7. Why does Reverse Key disable range scans?

Example:

Normal index order:

1001

1002

1003

1004

Reverse key order:

1001

2001

3001

4001

Values are no longer sequential.

So query:

WHERE order_id BETWEEN 1001 AND 1010

Cannot perform efficient range scan.

Oracle may do:

• Full index scan
• Or table scan

8. Can Reverse Key Index support equality search?

Yes.

Example:

WHERE order_id = 1005;

Oracle:

• Reverses search value
• Performs index unique scan

Equality lookups work perfectly.

9. Is Reverse Key still a B-Tree index?

Yes.

Internally:

• Same structure (Root → Branch → Leaf)
• Only difference: reversed key values stored

10. Reverse Key vs Normal B-Tree

Feature	Normal B-Tree	Reverse Key
Equality search	Excellent	Excellent
Range scan	Supported	Not supported
Insert contention	High (sequential keys)	Low
Right-hand growth	Yes	No
Best for	Mixed workloads	High insert OLTP

11. Does Reverse Key improve SELECT performance?

Not generally.

It improves:

• INSERT performance (concurrency)

It does NOT improve:

• Range query performance

Main purpose = concurrency improvement.

12. Can Reverse Key be Composite?

Yes.

CREATE INDEX txn_acc_id_rev_idx

ON transactions(account_id, txn_id)

REVERSE;

But typically used on:

• Single-column sequence-based primary keys

13. What about clustering factor?

Reverse key:

• Usually increases clustering factor
• Because physical order differs from logical order

May reduce performance for some SELECT queries.

14. Reverse Key in RAC environment

In Oracle RAC:

• Multiple instances insert simultaneously
• Sequential keys cause severe contention

Reverse key:

• Distributes inserts
• Improves scalability

Common in high-volume RAC systems.

15. Does Reverse Key affect DML?

INSERT:

• Faster in concurrent systems

UPDATE:

• Same as normal index

DELETE:

• Same as normal index

Main benefit is reduced block contention.

16. How to check if index is reverse?

Query:

SELECT index_name, index_type

FROM user_indexes

WHERE table_name = 'EMPLOYEES';

INDEX_TYPE will show:

NORMAL/REV

17. Real-world example

High-volume banking system:

• order_id generated by sequence
• 1000+ concurrent inserts

Normal index:

• Buffer busy waits
• Index block contention

Solution:

CREATE INDEX orders_pk_rev

ON orders(order_id)

REVERSE;

Result:

• Reduced contention
• Improved scalability

18. Common mistakes

• Using reverse index when range queries needed
• Using without understanding workload
• Applying to reporting tables
• Not analyzing execution plan impact

19. Performance tuning guidelines

Use reverse key when:

• Column is strictly increasing
• Heavy concurrent inserts
• No range queries required
• OLTP workload
• RAC system

Avoid when:

• Reporting queries depend on range
• Sorting by indexed column frequently
• Analytical workloads

20. Interview Tip

If asked:

“What is a Reverse Key Index and when would you use it?”

Answer:

“A Reverse Key Index is a special type of B-Tree index where the bytes of the indexed value are reversed before storage. It is mainly used to reduce right-hand index block contention caused by sequential inserts, especially in high-concurrency OLTP or RAC environments. However, it does not support efficient range scans.”