Scribbles for my memory

I wanted to search a year of audit history for one rare value. The table has an index on the timestamp, so I assumed a date filter would make the query cheap. The optimizer disagreed, and it was right.

Check the plan at a few window sizes instead of guessing:

explain plan for
select count(*) from audit_tbl a
 where sys_extract_utc(a.a_date_) >= sys_extract_utc(systimestamp) - interval '30' day(3)
   and a.some_col in ('...');

select * from table(dbms_xplan.display(null, null, 'BASIC +COST +ROWS'));

On a 6.2M-row, 523k-block table the results were:

• 7 days: index range scan, cost ~96K
• 30 days: full table scan, cost ~234K
• 90 days and 1 year: full table scan, cost ~234K

The two access paths scale differently. A full scan reads every block sequentially, many blocks per I/O, so its cost is the same whatever the window. An index range scan is cheap at the index itself. After that it fetches each matching row's table block one at a time, so its cost grows with the number of rows in the window.

How expensive those fetches are depends on the index's clustering factor:

select index_name, clustering_factor, num_rows from all_indexes where table_name = 'AUDIT_TBL';

A value close to the table's block count means rows with nearby keys sit together, so one block read serves many rows. A value close to the row count means nearly every row needs its own block read. Mine was 3.35M against 523k blocks, which is badly scattered. The break-even point landed somewhere between 7 and 30 days. Past that, forcing the index with a hint makes the query slower, not faster.

One related trap: if the index is function-based, for example on SYS_EXTRACT_UTC(a_date_), the predicate has to use the same expression. A plain a_date_ >= ... can't use that index at any window size.