Create Descending Index in Oracle

Oracle B-tree indexes store key values in sorted order, which allows the optimizer to retrieve rows efficiently and, in many cases, avoid an explicit sort for ORDER BY. Although Oracle can scan a standard B-tree index in either direction (ascending or descending) at runtime, a descending index becomes most valuable when you need a composite index that mixes sort directions across columns (for example, one column ascending and another descending).

Typical use case: retrieving “most recent first” data (such as newest orders) and supporting a query pattern that sorts by one key descending while keeping another key ascending for grouping, filtering, or tie-breaking.

Create a descending index when you have an established query pattern that requires a stable mixed sort order on indexed columns, such as:

• ORDER BY created_ts DESC, order_id ASC
• ORDER BY score DESC, user_id ASC

For a single column, Oracle can usually satisfy ORDER BY col DESC by scanning a normal B-tree index in reverse, so a single-column descending index is often unnecessary unless you have a specific reason (for example, matching an existing indexing standard or validating plan stability).

A descending index is created by applying the DESC keyword to the specific column(s) inside the index column list. This lets you mix directions per column (ASC is the default).

-- single column
CREATE INDEX index_name
ON table_name (col1 DESC);

-- multiple columns
CREATE INDEX index_name
ON table_name (col1 DESC, col2 ASC, col3 DESC);

By default, Oracle stores B-tree index keys in ascending order. When you specify DESC for a column in the index definition, Oracle maintains that key in descending order for the index. Descending keys are particularly useful when your workload consistently sorts one column descending while keeping another ascending in the same ORDER BY.

In Oracle 23ai, indexing strategy should still be driven by actual query patterns and execution plans. After creating a descending index, validate that the optimizer uses it with EXPLAIN PLAN or plan views (for example, a plan operation such as an index range scan aligned with your predicates and ordering). If you are testing whether an index helps without changing application SQL, consider using an invisible index as a controlled experiment, and then make it visible once you confirm the benefit.

Descending indexes are one tool in Oracle’s broader indexing toolbox. Other index options commonly used under specialized circumstances include:

1. Bitmap
2. Bitmap join
3. Function-based
4. Indexed virtual column
5. Invisible
6. Global partitioned
7. Local partitioned
8. Domain (for example, Oracle Text, Spatial)
9. B-tree cluster
10. Hash cluster

Many of these index types are explored in later lessons and modules.

Click the Exercise link below to practice creating a descending index.
Creating Descending Index - Exercise