Why Your SQL Queries Skip Indexes and How to Make Them Use Indexes

SQL Index Usage Issues and Solutions

When Oracle's optimizer cannot apply an index even though a suitable one exists, query performance suffers dramatically. The root causes fall into eight typical categories, each requiring specific adjustments to the SQL or environment.

1. Situations Where SQL Cannot Use an Index

Inaccurate statistics

Index columns allowing NULL values

Predicate uses inequality operators ( <>, !=)

LIKE patterns with leading or full wildcards

Functions, arithmetic, or other expressions applied to index columns

Implicit type conversions

Query transformation failures

Other logical reasons in the statement

Below are the most common cases and their remedies.

2. Inequality Predicates ( <> , != )

If the set of values not equal to a constant is small, replace the inequality with an IN list or an equality check. If the set is large, rewrite the condition as a combination of > and < predicates.

Example before rewrite (cannot use index):

After rewriting with OR (index is used):

Another valid rewrite:

SELECT * FROM t WHERE t.NAME IN ('ORADB1','ORADB2','ORADB3');

3. LIKE with Leading or Full Wildcards

Three approaches are recommended:

Remove the leading wildcard if business logic permits.

Create a function‑based index (e.g., INSTR) for patterns that cannot be changed.

Use a reverse function index for pure leading‑wildcard searches.

Example of removing the leading wildcard:

Creating a reverse function index and rewriting the query:

CREATE INDEX idx_rev_name ON t (REVERSE(name));

SELECT * FROM t WHERE REVERSE(name) LIKE REVERSE('数据')||'%';

4. Functions or Expressions on Index Columns

Remove any calculations or functions applied to indexed columns to keep the index column “pure”.

Before (index not used):

After removing the expression:

5. Implicit Type Conversions

Avoid implicit conversions by explicitly casting values to the column’s datatype, and never apply conversions to indexed columns unless a function index is defined.

Problematic query (implicit conversion, index not used):

Corrected query (explicit cast, index used):

6. Query Transformation Failures

Complex transformations such as view merging, subquery unnesting, or join‑predicate push‑down may fail, causing the optimizer to fall back to full scans. When this occurs, investigate the 10053 trace or SQLT, and consider disabling the buggy transformation via the _fix_control parameter (e.g., enable bug 9380298).

Example of a view that works on 10g but not on 11g:

7. Logical Errors Preventing Index Selection

Incorrect predicate ordering or non‑deterministic expressions can stop the optimizer from propagating predicates to the index. Rewriting the query to place filter conditions on the indexed column directly often restores index usage.

Example of rewriting a composite‑index query to enable index access:

8. Other Miscellaneous Reasons

Additional scenarios include DBLINK queries, missing statistics, and poorly selective indexes. Each requires a case‑by‑case analysis.

Enforcing SQL Development Standards

Many of the above problems stem from non‑compliance with database development guidelines. To ensure consistent quality, organizations can implement an automated SQL audit workflow that detects new statements, pinpoints violations, and suggests optimizations.

A typical audit platform provides:

Detection of newly introduced SQL in each release

Rule‑based checks covering syntax, model design, and index usage (over 200 rules)

Automatic generation of performance‑related recommendations and visual reports

Example commands to create a functional index for a derived column and gather statistics:

SQL> CREATE INDEX idx1_t_object ON t_objects(last_ddl_time - created);
SQL> exec dbms_stats.gather_table_stats(ownname => USER, tabname => 't_objects', estimate_percent => 100, method_opt => 'for all indexed columns', cascade => TRUE);

By integrating such tools into the release pipeline, teams can shift performance tuning earlier, reduce manual effort, and maintain high‑quality database code.