Why Oracle Partition Pruning Silently Fails — and How to Catch It

The query that reads 400 million rows to return 12

You partitioned the orders table by month, rewrote nothing, and expected the database to get smarter on its own. Then a report that filters on order_date still takes eleven minutes. The execution plan shows a full scan of every partition. Partitioning didn't break — pruning just never happened, and Oracle won't warn you. It will happily scan all 48 partitions and return correct results, slowly.

This is the most common partitioning disappointment in practice: the feature works exactly as documented, but a small detail in the query or the statistics quietly disables the one optimization you were paying for. The good news is that pruning failure is easy to detect once you know where to look, and the causes are a short, finite list.

What pruning actually looks like in a plan

Partition pruning is the optimizer's ability to skip partitions that cannot contain rows matching your predicate. When it works, EXPLAIN PLAN output shows it explicitly in the PSTART and PSTOP columns of the plan table — you'll see values like PARTITION RANGE SINGLE with concrete partition numbers, or PARTITION RANGE ITERATOR for a bounded set.

Run this from any SQL client with permission on the table (no special privileges needed beyond EXPLAIN PLAN access):

EXPLAIN PLAN FOR
SELECT * FROM orders
WHERE order_date >= DATE '2026-09-01'
  AND order_date <  DATE '2026-10-01';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

What you want to see: PARTITION RANGE ITERATOR with PSTART/PSTOP showing two partition numbers. What you don't want: PARTITION RANGE ALL with PSTART = 1 and PSTOP = 1048575 (or your full partition count). That second shape means every segment in the table gets opened and read. Note that KEY in PSTART/PSTOP means pruning happens at execution time from a bind variable or join value — that's fine, not a failure.

The usual suspects

Functions wrapped around the partition key

The classic killer: WHERE TRUNC(order_date) = DATE '2026-09-15'. Applying a function to the column means the optimizer can no longer map the predicate directly onto partition boundaries, so it gives up and scans everything. The fix is almost always to rewrite the predicate as a range on the bare column — order_date >= DATE '2026-09-15' AND order_date < DATE '2026-09-16' — which prunes cleanly and is semantically identical for a DATE column. The same applies to TO_CHAR(order_date, 'YYYY-MM') filters; rewrite them as half-open date ranges instead.

Stale or missing partition-level statistics

The optimizer prunes based on what it believes about partition boundaries and contents. If statistics are stale — common right after a large load into a new partition — it can make poor choices even when pruning technically occurs. Check freshness with:

SELECT partition_name, num_rows, last_analyzed
FROM dba_tab_partitions
WHERE table_name = 'ORDERS'
ORDER BY partition_position;

Any partition with a NULL or old LAST_ANALYZED after a data change is a candidate for a targeted refresh: EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'APP', tabname=>'ORDERS', granularity=>'PARTITION', partname=>'ORDERS_2026_09'); — run as the schema owner or a user with ANALYZE ANY. Gathering stats changes optimizer inputs, so on a production system do it during a low-activity window and be aware plans can shift afterward.

Type mismatches and implicit conversion

Comparing a DATE key to a string literal (WHERE order_date = '15-SEP-26') forces an implicit conversion whose behavior depends on session NLS settings and can defeat pruning. Always use explicit DATE '...' literals or TO_DATE on the literal side, never on the column side.

A worked example, before and after

Take a range-partitioned table events(event_ts DATE, payload ...) partitioned by month. The original report query:

SELECT COUNT(*) FROM events
WHERE TO_CHAR(event_ts, 'YYYY-MM') = '2026-09';

Plan shows PARTITION RANGE ALL. Rewritten:

SELECT COUNT(*) FROM events
WHERE event_ts >= DATE '2026-09-01'
  AND event_ts <  DATE '2026-10-01';

Re-run the EXPLAIN PLAN block above and confirm the operation now reads PARTITION RANGE SINGLE with PSTART/PSTOP pointing at the September partition. For a runtime check rather than an estimate, execute the query with /*+ GATHER_PLAN_STATISTICS */ and inspect DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALLSTATS LAST +PARTITION') — the actual partition access appears in the real plan, which is the ground truth when estimates and reality disagree.

The trade-off nobody mentions at design time

Pruning is fragile in a specific way: it depends on every query author, forever, writing predicates the optimizer can map to partition boundaries. ORM-generated SQL, reporting tools, and well-meaning TRUNC habits all erode it over time. There's no constraint you can add to enforce pruning-friendly predicates. The practical mitigation is operational, not declarative: pick a handful of critical queries, snapshot their plans (PSTART/PSTOP values included) after go-live, and re-check them after statistics refreshes, upgrades, and application releases. A plan baseline can pin a good plan, but it won't stop a new query shape from scanning everything.

Also keep partition count honest. Thousands of partitions inflate parse time, dictionary contention, and DML overhead, and they make every pruning failure more expensive because "scan all" means scanning all. If you're on 12c or later, interval partitioning removes the excuse of pre-creating hundreds of empty future partitions.

What to do Monday morning

Pick your largest partitioned table and its three most important queries. Run EXPLAIN PLAN on each and read the PSTART/PSTOP columns. If you see PARTITION RANGE ALL where you expected a single partition, check for functions on the key, implicit type conversion, and stale partition stats — in that order. That fifteen-minute audit catches the majority of silent pruning failures, and it turns partitioning from a hope into a verified behavior.