Indexes and access paths: when Oracle uses an index, and when it won't

"Add an index" is the first thing everyone suggests, and half the time the optimizer ignores it. Usually it's right to. This page asks the database directly.

The test case

On a copy of the deliveries with a primary key and an index on batter_id, explain four queries and compare how each reads the table:

1. one ball by its key;
2. the same ball, with + 0 added to the key column;
3. one batter's balls;
4. one batter's balls, with a hint telling Oracle to use the index.

Setup

begin
  for t in (select table_name from user_tables where table_name = 'D141_BALLS') loop
    execute immediate 'drop table d141_balls purge';
  end loop;
end;
/

Setup

create table d141_balls as select * from cr_deliveries;

Setup

alter table d141_balls add constraint d141_balls_pk primary key (match_id, innings_no, seq_no);

Setup

create index d141_batter_ix on d141_balls (batter_id);

Setup

delete from plan_table where statement_id like 'D141%';

Setup

explain plan set statement_id = 'D141_1_KEY' for
select * from d141_balls where match_id = 1 and innings_no = 1 and seq_no = 10;

Setup

explain plan set statement_id = 'D141_2_WRAPPED' for
select * from d141_balls where match_id + 0 = 1 and innings_no = 1 and seq_no = 10;

Setup

explain plan set statement_id = 'D141_3_BATTER' for
select * from d141_balls where batter_id = 101;

Setup

explain plan set statement_id = 'D141_4_HINTED' for
select /*+ index(b d141_batter_ix) */ * from d141_balls b where batter_id = 101;

Expected result

For queries 1, 2 and 4, the access steps:

select substr(statement_id, 6) as query, operation,
       replace(replace(options, 'STORAGE '), ' BATCHED') as options, object_name
from   plan_table
where  statement_id in ('D141_1_KEY', 'D141_2_WRAPPED', 'D141_4_HINTED')
and    operation in ('TABLE ACCESS', 'INDEX')
order  by statement_id, id;

The REPLACEs smooth over two cosmetic differences: on Exadata (where Autonomous and apex.oracle.com run) a full scan is called TABLE ACCESS STORAGE FULL, and index lookups usually say BY INDEX ROWID BATCHED (fetching table blocks in batches). Same access path, different label.

What happened

1. The full key finds exactly one row through the primary key. No contest.
2. match_id + 0 = 1 gives the same answer, but the index is on match_id, not on match_id + 0. Wrap an indexed column in anything (+ 0, UPPER(), TRUNC(), TO_CHAR()) and a normal index can't be used. Full scan. (Old-school tuners used + 0 on purpose to switch an index off.)
3. The hint forces the index: a range scan finds batter 101's entries, then the table rows are fetched one address at a time.

And query 3, the one we didn't test

What does Oracle do with batter_id = 101 by itself? On our 19c Autonomous database, at the time of writing:

---------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |   229 |     9   (0)|
|   1 |  PX COORDINATOR      |            |       |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000   |   229 |     9   (0)|
|   3 |    PX BLOCK ITERATOR |            |   229 |     9   (0)|
|   4 |     TABLE ACCESS FULL| D141_BALLS |   229 |     9   (0)|
---------------------------------------------------------------

A full scan, with a perfectly good index sitting there. Why? The optimizer estimates 229 rows, about 3% of the table, and the whole table is only a few dozen blocks. Reading it all in big multi-block reads is cheaper than 229 separate trips from the index to the table. On a table of 50 million deliveries, the same 229 rows would be a tiny fraction and the index would win.

That's why query 3 isn't in the test: the right answer depends on the data's size and spread, and it can change as the table grows. Which is the point.

Rules of thumb

• Indexes win when they're selective: few rows out of many. "Give me 1 ball" yes; "give me all the balls that weren't wides" no.
• Don't hide indexed columns inside functions in the WHERE. Write match_date >= date '2026-04-01' and match_date < date '2026-05-01' instead of trunc(match_date, 'MM') = ..., or create a function-based index on exactly the expression you use.
• Composite index column order matters: an index on (match_id, innings_no, seq_no) helps WHERE match_id = 1, but not WHERE seq_no = 10 on its own (except via a skip scan).
• Hints are a last resort. They freeze one decision forever, even after the data changes. Fix statistics and SQL first.

19c and 26ai

The same rules on both. 26ai (and 19c Autonomous) can also create indexes for you: automatic indexing watches the workload and adds the indexes that help.

Gotchas

• Implicit conversion is a hidden function. Comparing a VARCHAR2 column to a number (where team_code = 123) makes Oracle wrap the column in TO_NUMBER(), and the index is gone.
• IS NULL can't use a plain B-tree index on one column, because rows where every indexed column is null aren't in the index.