Why Your Oracle Indexes Aren’t Used and How to Fix It

Column vs Column Comparison

When two columns (id and c_id) both have separate indexes, a query like select * from test where id=c_id; will not use either index and may be treated as a full table scan.

Presence of NULL Values

Avoid NULL values in table design; if unavoidable, provide default values. An indexed column that is nullable may not be used because the index contains fewer distinct values than the table, leading to a full scan.

select * from test where id is not null;

NOT Condition

Indexes work well for equality or range queries, but conditions using <>, NOT, IN, or NOT EXISTS often cause the optimizer to prefer a full table scan.

select * from test where id<>500;
select * from test where id in (1,2,3,4,5);
select * from test where id 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 Wildcard

For fuzzy searches, use a trailing wildcard (e.g., name||'%') so the index can be used; a leading wildcard prevents index usage.

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

Functions in Conditions

Avoid applying functions to indexed columns, such as upper(name)='SUNYANG', which disables index usage. Using a function on the constant side is acceptable.

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

Composite Index Leading Column Selectivity

If the leading column of a composite index has low selectivity, Oracle may use INDEX SKIP SCAN; if it has high selectivity but splitting the leading column is costly, the optimizer may choose a full scan.

select * from test where owner='sunyang';

Data Type Conversion

Implicit type conversion can cause index loss, e.g., comparing a numeric column to a string literal.

select * from sunyang where id='123';

Connect By Level

Queries that use connect by level do not use indexes.

Predicate Operations

Arithmetic operations on indexed columns also invalidate the index. Rewrite id/2=:type_id as id=:type_id*2 to enable index usage.

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

Virtual Index

A virtual index’s usefulness depends on the execution plan. In an experiment, a virtual index on column a performed faster than a regular index after the first execution, due to caching effects.

create index idx_test_id on test(id);
create index idx_test_id on test(id) nosegment;
CREATE TABLE test_1116(
  id number,
  a number
);
CREATE INDEX idx_test_1116_id on test_1116(id);
CREATE INDEX idx_test_1116_a on test_1116(a) nosegment;
begin
  for i in 1..100000 loop
    insert into test_1116 values(i,i);
  end loop;
  commit;
end;
select count(id) from test_1116; -- first run 0.061s, second run 0.016s
select count(a) from test_1116;  -- first run 0.031s, second run 0.016s

Invisible Index

Oracle 11g introduces invisible indexes, which are ignored by the optimizer unless the session parameter optimizer_use_invisible_indexes is set to true. This feature is 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;