When "Using an Index" Is the Problem: A Skip Scan Fooled by Empty Index Statistics

A query that used to return in milliseconds suddenly took about a minute. The plan showed an index scan, so nothing looked broken. Adding a hint made it fast again. The real fix was a one-line stats gather on an index the query didn’t even need.

The symptom

An application query joins a history table to a large 82M-row table and filters on five equality predicates. From the app it took roughly 60 seconds. With an INDEX hint pointing at one specific index, it finished in seconds. If a hint fixes a query, the optimizer is making a costing mistake.

What the plan showed

The slow plan accessed the big table like this:

INDEX SKIP SCAN   ix_id_proc    cost 1   rows 3423K
  access("PROC_ID"=:4)
TABLE ACCESS BY INDEX ROWID BATCHED   (filter on the other four predicates)

Two things stood out:

• The index was (txn_id, proc_id), but the query had no filter on txn_id. The only way to use that index was a skip scan.
• The cost was 1 while estimating 3.4M rows. A scan that returns millions of rows can’t cost 1.

The index that matched all five predicates, ix_lookup (msg_type, trace_no, txn_ts, proc_id, inst_code), was available the whole time and enabled a clean range scan.

Range scan vs skip scan, briefly

A range scan descends the B-tree once to the first matching key and reads neighbouring entries. It needs the leading column(s) in the predicate.

A skip scan treats the index as many mini-indexes, one per distinct leading value, and probes each one. It only pays off when the leading column has very few distinct values. Here the leading column was a transaction ID, with 82M distinct values, so the real work was close to 82M probes. Only proc_id was used as an access predicate, and the other four filters ran after fetching rows from the table.

The root cause

I checked the stats for every index on the table:

select index_name, num_rows, leaf_blocks, distinct_keys, blevel, last_analyzed
from dba_indexes
where owner = :owner and table_name = :tab;

Every index had real numbers except one, ix_id_proc:

num_rows = 0   leaf_blocks = 0   distinct_keys = 0   blevel = 0   status = VALID

To the optimizer, that index was empty, so a skip scan on it looked almost free. The properly matched index had accurate stats, so it looked expensive by comparison. The optimizer picked the “cheaper” plan, which was fiction.

The likely trigger was a table rebuild: add a column, load the data into a new table, then rename it. An index created on a still-empty table gets stats computed at zero rows. Nothing refreshed them afterwards. The nightly auto-stats job looked at the table, found it not stale, and left the index alone.

The fix

begin
  dbms_stats.gather_index_stats(
    :owner, 'IX_ID_PROC',
    estimate_percent => dbms_stats.auto_sample_size,
    degree           => 4,
    no_invalidate    => false);
end;
/

Once the optimizer saw 82M distinct leading values and about 427K leaf blocks, the skip scan priced far above the range scan on ix_lookup. I never touched ix_lookup and I added no hint. The optimizer simply compared two honest costs.

Before and after

A trap during verification

After the regather, the cursors still showed the old plan. They had been parsed minutes before the stats were refreshed, so the optimizer hadn’t re-costed them. Compare each cursor’s last_load_time against the stats’ last_analyzed timestamp (with seconds, not just the date). If the load time is earlier, the cursor is stale and needs invalidation or a purge to re-parse.

Lessons

• A cheap-looking plan with large row estimates is suspicious. A cost of 1 for millions of rows means the inputs are wrong.
• Check stats on all indexes, not just the ones in your plan. A zero-stats index can steal queries it has no business serving.
• Skip scan on a high-cardinality leading column is a warning sign. Check that index’s stats and look for an index with the right leading column.
• Table swaps need an index stats step. After a load and rename, run gather_table_stats(..., cascade => TRUE) on the final table name. cascade only covers indexes that exist when the gather runs.
• Prefer fixing stats over pinning plans. Hints, SQL profiles and SQL patches work as emergency relief, but they hide the real problem and don’t adapt as data changes.

Sweep for similar cases:

select i.index_name, i.table_name, i.num_rows idx_rows, t.num_rows tab_rows, i.last_analyzed
from dba_indexes i
join dba_tables t on t.owner = i.table_owner and t.table_name = i.table_name
where i.owner = :owner
  and (i.last_analyzed is null or i.num_rows is null
       or (i.num_rows = 0 and t.num_rows > 0))
order by t.num_rows desc;