When many sessions insert rows with consecutive key values, such as numbers from a sequence, every insert lands on the same last leaf block of the B-tree, and the sessions wait for each other. A reverse-key index reverses the bytes of each key, so consecutive values spread across the whole index. A descending index, by contrast, simply stores a column in descending order.
Code for This Guide
The main examples are in the examples/indexes folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create index index on table (column [, ...]) reverse create index index on table (column desc [, ...])
A Reverse-Key and a Descending Index
Example:
create index payments_id_rev_ix on payments (payment_id, booking_id) reverse; create index payments_paid_desc_ix on payments (paid_at desc); select index_name, index_type, column_name, descend from user_indexes join user_ind_columns using (index_name, table_name) where table_name = 'PAYMENTS' and index_name like 'PAYMENTS_%X' order by index_name, column_position;
Output:
Index PAYMENTS_ID_REV_IX created. Index PAYMENTS_PAID_DESC_IX created. INDEX_NAME INDEX_TYPE COLUMN_NAME DESCEND ________________________ ________________________ _______________ __________ PAYMENTS_BOOKING_IX NORMAL BOOKING_ID ASC PAYMENTS_ID_REV_IX NORMAL/REV PAYMENT_ID ASC PAYMENTS_ID_REV_IX NORMAL/REV BOOKING_ID ASC PAYMENTS_PAID_DESC_IX FUNCTION-BASED NORMAL SYS_NC00006$ DESC
The reverse-key index has the type NORMAL/REV. The descending index is implemented as a function-based index on a hidden column, SYS_NC00006$, marked DESC. The example drops both afterward.
What the Descending Index Stores
Example:
select index_name, column_expression from user_ind_expressions where index_name = 'PAYMENTS_PAID_DESC_IX2';
Output:
INDEX_NAME COLUMN_EXPRESSION _________________________ ____________________ PAYMENTS_PAID_DESC_IX2 "PAID_AT"
USER_IND_EXPRESSIONS shows that the hidden column is the paid_at column itself, stored in descending order. The index was created and dropped by the example.
Trade-Offs
| Index | Helps | Cannot |
|---|---|---|
| Reverse-key | Heavy concurrent inserts of increasing keys | Serve range scans such as BETWEEN or >, since adjacent values are scattered |
| Descending | Queries sorting mixed directions, such as ORDER BY a ASC, b DESC | Much for single-column sorts, which a normal index can read backward |
Related Guides
Conclusion
Reverse-key indexes spread consecutive keys across the index to relieve contention on heavy concurrent inserts, at the cost of range scans. Descending indexes store columns in descending order, implemented as function-based indexes, and help mixed-direction sorts.
