Common Reasons Why Indexes Are Ignored in SQL Queries

Even when a column has an index and the query uses that column, the execution plan may still ignore the index. The following points explain common causes.

Column-to-Column Comparison

When two indexed columns are compared, the optimizer may treat the predicate as a full table scan. Example:

select * from test where id=c_id;

Presence of NULL Values

If an indexed column can contain NULL, the optimizer often prefers a full scan because the index contains fewer entries than the table. Example:

select * from test where id is not null;

NOT Conditions

Predicates that use <>, NOT, IN, or NOT EXISTS make index navigation difficult, so the optimizer may choose a full scan.

select * from test where id<>500;
select * from test where id in (1,2,3,4,5);
select * from test where not in (6,7,8,9,0);
select * from test where not exists (select 1 from test_02 where test_02.id=test.id);

LIKE Wildcards

Using a leading wildcard (e.g., LIKE '%name') prevents index usage, while a trailing wildcard (e.g., LIKE 'name%') can still use an INDEX RANGE SCAN.

select * from test where name like '张%';

Functions on Indexed Columns

Applying functions such as UPPER(), TO_CHAR(), TRUNC(), etc., to an indexed column disables index usage because the stored index entries no longer match the computed values.

select * from test where upper(name)='SUNYANG';
select * from test where name=upper('sunyang'); -- INDEX RANGE SCAN

Low‑Cardinality Leading Columns in Composite Indexes

If the leading column of a composite index has low distinctness, the optimizer may perform an INDEX SKIP SCAN or fall back to a full scan.

select * from test where owner='sunyang';

Implicit Data Type Conversion

When the query forces an implicit conversion (e.g., comparing a numeric column to a string literal), the index cannot be used.

select * from sunyang where id='123';

CONNECT BY LEVEL

Queries that use CONNECT BY LEVEL do not use indexes.

Arithmetic Predicates on Indexed Columns

Expressions like id/2 = :type_id prevent index usage; rewrite them as id = :type_id*2 to enable the index.

select * from sunyang where id/2=:type_id;
select * from sunyang where id=:type_id*2;

Virtual Indexes

Virtual ("nosegment") indexes can be created and may improve performance in some scenarios, but their usefulness depends on the execution plan.

create index idx_test_id on test(id) nosegment;

Invisible Indexes

Oracle 11g introduced invisible indexes, which are ignored by the optimizer unless the session parameter optimizer_use_invisible_indexes is set to true. They are useful for testing without affecting production queries.

alter index idx_test_id invisible;
alter index idx_test_id visible;
alter session set optimizer_use_invisible_indexes = true;

Understanding these scenarios helps developers write queries that can effectively leverage indexes and avoid unnecessary full‑table scans.