What Is a Composite and Descending Indexes in Oracle Database?
Orders keys by multiple columns and can store selected keys descending to match access and sort patterns.
How the Composite and Descending Indexes Algorithm Works
Orders keys by multiple columns and can store selected keys descending to match access and sort patterns.
When to Use Composite and Descending Indexes
Queries constraining leading columns and then ranging or sorting by later columns.
How to Create a Oracle Database Composite and Descending Indexes
Review data quality, existing structures, feature support, build time, and deployment requirements before creating an index on a production table.
CREATE INDEX orders_customer_date_ix ON orders (customer_id, created_at DESC);Composite and Descending Indexes Query Example
This example demonstrates the matching query shape. Check the execution plan to confirm that the engine can use the index.
SELECT order_id FROM orders WHERE customer_id = :customer_id ORDER BY created_at DESC FETCH FIRST 20 ROWS ONLY;Queries Supported by Composite and Descending Indexes
Queries constraining leading columns and then ranging or sorting by later columns. A similar-looking predicate may not use the same access path; operators, expressions, key order, and data type all matter.
Read Performance, Storage, and Write Costs
Column order governs usable prefixes; redundant indexes add storage and write cost. Indexes reduce some search work but require storage and add maintenance to writes.
Composite and Descending Indexes Limitations and Common Mistakes
Queries with unconstrained leading keys or incompatible sort expressions may not benefit.
Do not assume that an index being present means the optimizer chose it or that the query became selective.
How to Measure and Tune Composite and Descending Indexes
Inspect access/filter predicates, align key order with observed workload, and gather statistics.
Compare representative plans and timings before and after, including concurrent writes and realistic data volumes.
Composite and Descending Indexes Questions
Will the database always use this index?
No. The optimizer weighs selectivity, estimated costs, statistics, cache state, and the amount of data returned.
What should I verify before production?
Confirm version support, query compatibility, write overhead, build/rollback procedures, and index behavior during backup and recovery.
Official Oracle Database Index Documentation
Check Oracle Database Concepts: Indexes and Index-Organized Tables for release-specific requirements and supported operators.