An Oracle index is useful when its structure matches important queries closely enough to reduce their work—and the resulting read benefits outweigh the storage and write costs. Judge an index by representative execution plans and measurements across the workload, not by its presence or by whether one query mentions its columns.
What an Oracle index can—and cannot—do
An index gives Oracle an additional access path to table data. Depending on the query and index, it can make selective lookups, range access, ordered retrieval, or queries that can be answered from index entries less costly. The optimizer chooses among available access paths; creating an index does not guarantee that a query will use it.
As an Amazon Associate I earn from qualifying purchases.
Oracle’s Database 18c SQL Tuning Guide puts it plainly: “Indexes are not a performance cure-all.” An index can be a poor match for a query, or a valid access path that still requires enough table block visits to be costly.
When an index is a plausible fit
Selective filters and ranges
A B-tree index is worth evaluating when a query filters on indexed values and returns a relatively small part of a table, or when it needs a range of values. It can also help with ordered retrieval when the requested order aligns with the index. Whether it wins depends on the query, data, and available access paths; the optimizer may choose another plan.
#1 Best Overall
Composite predicates and covered queries
A composite index stores multiple key columns in a defined order. It can help when queries use the leading portion of that key in relevant predicates. A query that uses only later columns generally cannot access the index in the expected way through that leading-key path.
Including the values a query needs in the index can sometimes let Oracle answer it from index entries without visiting table rows. That is not automatic: the query’s predicates and returned columns must match what the index provides, and the plan still needs to make the access path worthwhile.
Repeated expressions in predicates or ordering
If frequently run SQL filters or sorts on a transformed value—for example, a case-normalized column—an ordinary index on the untransformed column may not support that expression access path. A function-based index on the matching expression can be considered. The query expression must align with the indexed expression, and changes to indexed data still require index maintenance and expression evaluation.
Free tools Windows power users keep installed
One-click scans. No signup required.
Which index type fits the workload?
| Index type | Consider it for | Key limitation or cost |
|---|---|---|
| B-tree, including composite keys | Selective lookups, ranges, ordered retrieval, and some queries answerable from index entries. Composite keys may help where queries use their leading columns. | Benefits depend on predicate and key alignment; index storage and DML maintenance add workload cost. |
| Function-based | Recurring predicates or ordering on a transformed column or expression. | The query expression must match the indexed expression; DML also maintains the index and evaluates the expression. |
| Bitmap | Analytic or warehouse queries with multiple predicates on low- or medium-cardinality attributes. | Heavy concurrent DML can cause contention on bitmap entries representing sets of rows, making this a poor general OLTP fit. |
| Reverse-key | Insert hot spots, where reversing key bytes may spread inserts. | Does not support index range scans. Validate applicability against the target release and workload. |
These are workload-dependent choices, not interchangeable performance upgrades. Oracle’s 21c Database Performance Tuning Guide describes the reverse-key tradeoff; verify feature behavior for the database release in use.
How to determine whether an index helps
- Choose representative statements. Identify important SQL, its filter and join predicates, returned columns, execution frequency, and the workload’s read/write mix. Consider normal activity rather than a single unusual run.
- Check whether the index matches the query. For a composite key, examine whether the query can use its leading columns. For an expression predicate, check whether a function-based index matches the expression. Consider whether the query could be covered by the index.
- Inspect the execution plan and measure. Confirm whether Oracle chose the index access path and compare query timings under representative conditions. An index access can still require many table block visits. Oracle’s clustering factor is a diagnostic indicator of how closely index entry order corresponds to table row placement: a high value can mean more I/O for a large range scan, but it is not a verdict on its own.
- Measure the whole-workload tradeoff. Compare processing with and without the index where practical, including read latency and throughput, insert/update/delete performance, storage, and maintenance work. Oracle recommends weighing query gains against DML performance.
- Observe usage over a representative period before removal. Oracle documents index usage monitoring, but an index that appears unused in a short or atypical window may still matter to less frequent activity. Base removal decisions on a period that reflects normal workload cycles.
Why a seemingly sensible index can hurt
- It does not match the access pattern. A predicate on a non-leading column of a composite key, or an expression that differs from the indexed expression, may not get the expected access path.
- The plan still does too much table work. Index entries may lead to many table block visits; clustering factor can help explain this for range scans.
- Writes pay to maintain it. Inserts, updates to indexed values, and deletes require additional index work, consuming resources and potentially slowing DML.
- Its structure conflicts with concurrency. Bitmap indexes that suit multi-predicate analytics can be a poor fit for heavy concurrent changes.
- Its benefit is not representative. A result from one query or a narrow observation period does not establish that the index improves the workload as a whole.
Version and scope to keep in mind
The guidance summarized here draws on Oracle documentation for Database 18c (index design and tuning), 19c (index concepts and function-based indexes), 12c (optimizer access paths), and 21c (the reverse-key tradeoff). A referenced optimizer tutorial covers Database 11g and SQL Developer 3.2, so it should not be treated as current guidance for newer releases. Check the documentation for the target database release and edition before relying on specific syntax, optimizer behavior, feature availability, or licensing details.
Quick Recap
Best Value
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
