Common Scenarios Where SQL Fails to Use Indexes and How to Resolve Them

The article starts by noting that many SQL statements appear to be index‑eligible but the optimizer fails to choose the index, often because of poor SQL coding practices; it then outlines two main goals: avoid optimizer limitations and rewrite SQL according to business data characteristics.

It lists eight common reasons why an index is not used: inaccurate statistics, nullable indexed columns, inequality predicates (<> or !=), leading‑wildcard LIKE, functions or expressions on indexed columns, implicit type conversion, query transformation failures, and other logical issues.

Inequality predicates – When a predicate uses <> or != the optimizer cannot use a range scan. The article suggests converting the predicate to an equality or IN clause when possible, or rewriting it as a combination of > and < conditions. Example: SELECT * FROM t WHERE t.NAME IN ('ORADB1','ORADB2','ORADB3');

LIKE with leading or full wildcard – A pattern such as LIKE '%ORADB1' prevents index usage. Solutions include removing the leading wildcard, creating a function index (e.g., CREATE INDEX idx_instr ON t(name) INDEXTYPE IS CTXSYS.CTXCAT; ), or using a reverse index for Chinese characters: SELECT * FROM t WHERE REVERSE(t.name) LIKE REVERSE('数据')||'%';

Functions or expressions on indexed columns – Applying functions, arithmetic, or other expressions to an indexed column disables the index. The fix is to keep the indexed column pure and move calculations to the query side. Example screenshots illustrate removing the expression and re‑executing the plan.

Implicit type conversion – When the optimizer implicitly converts data types (e.g., VARCHAR2 to NUMBER), the index is ignored. The remedy is to use explicit conversion to match the indexed column’s type. The article shows before‑and‑after execution plans with images.

Query transformation failures – Complex transformations such as view merging or subquery unnesting may fail, causing the optimizer to fall back to a full scan. A real‑world bug (9380298) is described, and the fix is to enable the corresponding _fix_control parameter.

Logical issues with composite indexes – When predicates do not align with the leading columns of a composite index, the optimizer may choose a full table scan. The article demonstrates attempts to reorder predicates and, when that fails, suggests creating a function index like: CREATE INDEX idx1_t_object ON t_objects(last_ddl_time-created); and gathering statistics: exec dbms_stats.gather_table_stats(ownname => USER, tabname => 't_objects', estimate_percent => 100, method_opt => 'for all indexed columns', cascade => TRUE);

Finally, the piece emphasizes the importance of adhering to database development standards and introduces an automated SQL audit platform that can detect new SQL, flag issues, and provide optimization recommendations, thereby improving overall system quality and development efficiency.