Oracle Partition Pruning: Read One Partition, Not the Whole Table

Someone partitions the big table, the migration ticket gets closed, and everyone moves on believing the queries are now fast. Then a report that should read one month of data reads all of it, the plan says PARTITION RANGE ALL, and the partitioning that was supposed to help is just extra objects to manage.

Partitioning doesn't make queries fast. Partition pruning does — and pruning is something the optimizer has to be allowed to do. It's the one payoff that justifies the whole feature for performance work: when a query only needs June, Oracle reads the June partition and skips the other twenty-three. Get it and a report drops from scanning the table to scanning a slice of it. Lose it — usually to one small mistake in the WHERE clause — and you've paid for partitioning and gotten none of the speed. This post is what pruning is, the mistake that quietly kills it, how to read it in the plan, and a lab that watches the same query read one partition or all of them depending on a single function call.

What pruning actually is

A partitioned table is one logical table stored as several physical segments — say, ORDERS split into one partition per month. Partition pruning is the optimizer working out, at parse or run time, which of those segments a query could possibly need, and reading only those. If a query filters on the partition key in a way Oracle can map to partition boundaries, it touches the matching partitions and ignores the rest.

That's the entire performance case for range partitioning. A query for one month out of two years reads 1/24th of the data. A DELETE of last quarter's rows becomes a partition drop. A full scan that was unavoidable becomes a scan of one segment. None of it depends on an index — pruning happens before access method selection, at the level of "which segments are even in play."

The plan tells you whether it happened, in two columns most people never look at: Pstart and Pstop.

The same query, two WHERE clauses. A range predicate on the partition key lets the optimizer map the filter to partition boundaries and read only June. Wrap that same key in a function and the optimizer can no longer see the boundaries, so it falls back to reading every partition — for the identical rows.

The mistake that silently defeats it

Here is the trap, and it's the reason a partitioned table can still scan itself end to end. The optimizer can only prune when it can see the raw partition key in the predicate. The moment you wrap that key in a function, the boundaries disappear and pruning is off:

-- PRUNES: the optimizer maps the range straight to partition boundaries
WHERE order_date >= DATE '2025-06-01' AND order_date < DATE '2025-07-01'

-- DOES NOT PRUNE: a function on the key hides the boundaries -> every partition is scanned
WHERE TO_CHAR(order_date, 'YYYY-MM') = '2025-06'
WHERE TRUNC(order_date) = DATE '2025-06-15'          -- same problem, TRUNC hides the key

Both of those return the right rows. The TO_CHAR version just does it by reading the entire table and throwing away 95% of what it read. It's an easy mistake precisely because it looks tidier — TO_CHAR(...) = '2025-06' reads like exactly what you mean. But the optimizer doesn't evaluate the function against partition metadata; it evaluates it against every row, which means it has to fetch every row first.

The fix is always the same shape: express the filter as a range on the bare column — >= start AND < end — so the boundaries stay visible. Same logic for TRUNC, TO_CHAR, NVL, arithmetic, an implicit type conversion because you compared a DATE column to a string — any of them can turn pruning off. When a partitioned table is scanning more than it should, a function on the partition key is the first thing to look for.

Static pruning vs dynamic pruning

Not all pruning looks the same in the plan, and the difference is worth knowing so you don't misread a healthy plan as a broken one:

• Static pruning — the optimizer knows the partitions at parse time, because the predicate uses literals or is otherwise resolvable up front. Pstart/Pstop show actual partition numbers, e.g. 19 / 19. This is the cleanest case.
• Dynamic pruning — the partitions can't be known until run time, typically because the predicate uses a bind variable (WHERE order_date >= :d). The plan shows Pstart/Pstop as KEY, which is not a problem — it means "pruning happens, but the specific partitions are decided at execution." Bind variables are still the right default for reuse; KEY is pruning working, just later.

The failure you're hunting is neither of those. It's Pstart = 1 and Pstop = the last partition — the plan operation PARTITION RANGE ALL. That's the optimizer telling you it gave up and read everything.

Now prove it

Put 1.2 million orders into a table with one partition per month — twenty-four monthly partitions across 2024 and 2025 — and run the same question ("how many orders in June 2025?") two ways. First the range predicate, then the function. Here are the real plans, straight from DBMS_XPLAN:

--- WHERE order_date >= DATE '2025-06-01' AND order_date < DATE '2025-07-01' ---
| Id  | Operation       | Name   | Pstart| Pstop |
|   2 |   PARTITION RANGE SINGLE|    |    19 |    19 |
|   3 |    TABLE ACCESS FULL    | ORDERS |    19 |    19 |

--- WHERE TO_CHAR(order_date,'YYYY-MM') = '2025-06' ---
| Id  | Operation        | Name   | Pstart| Pstop  |
|   2 |   PARTITION RANGE ALL|        |     1 |1048575 |
|   3 |    TABLE ACCESS FULL | ORDERS |     1 |1048575 |

Both queries return the same 50,000 rows. The first reads one partition (PARTITION RANGE SINGLE, Pstart = Pstop = 19) and burns 1,725 buffer gets. The second reads all of them (PARTITION RANGE ALL) and burns 41,448 — about 24× more work for the identical answer. That factor is not a coincidence: it's one partition versus twenty-four, which is exactly what pruning bought.

The odd-looking Pstop = 1048575 in the second plan is just Oracle's sentinel for "the maximum possible partition" on an interval-partitioned table — it means all partitions, not that a million of them exist. PARTITION RANGE ALL is the phrase that matters.

Don't take my word for it — run it. The partition pruning lab stands up an Oracle Database Free container, builds the 1.2M-row monthly-partitioned table, and runs both queries with GATHER_PLAN_STATISTICS. It asserts the range predicate gets PARTITION RANGE SINGLE on a single partition, the function predicate gets PARTITION RANGE ALL, both return the same 50,000 rows, and the pruned query reads at least 5× fewer buffers (it reads ~24× fewer). If pruning stops working, the run fails. It's proven on every CI push.

What teams get wrong

• A function on the partition key. The headline mistake: TO_CHAR, TRUNC, NVL, or arithmetic around the key turns pruning off and the table scans itself. Filter with a bare-column range instead.
• An implicit type conversion. Comparing a DATE partition key to a string literal (WHERE order_date = '2025-06-15') makes Oracle apply an internal conversion to the column — same effect as wrapping it in a function. Compare dates to DATE literals and numbers to numbers.
• Reading KEY as broken. Seeing Pstart = KEY and assuming pruning failed, then ripping out bind variables to "fix" it. KEY is dynamic pruning — it's working. PARTITION RANGE ALL is the broken case.
• Partitioning on the wrong key. Partitioning by a column the queries don't filter on. Pruning can only use the partition key; if your access pattern filters by customer_id but you partitioned by region, nothing prunes. Partition for how the data is queried, not how it's shaped.
• Expecting pruning to replace an index. Pruning narrows which segments are read; within a partition you still scan or index as usual. A month's partition that's still millions of rows may need a local index on top of the pruning. The two work together — reading the plan tells you which one you're missing.
• Global indexes that unravel on partition maintenance. Dropping or truncating a partition invalidates global indexes unless you maintain them (UPDATE INDEXES), quietly turning a fast partition drop into an index rebuild. Prefer local indexes with partitioning unless a global one is genuinely required.

Frequently asked questions

What is partition pruning in Oracle?

Partition pruning is the optimizer eliminating partitions that a query cannot possibly need and reading only the ones that remain. When a query filters on the partition key in a way Oracle can map to partition boundaries, it accesses just the matching partitions and skips the rest, which is the primary performance benefit of partitioning. It happens before access-method selection and does not depend on indexes: it operates at the level of deciding which physical segments are in play. In the execution plan it appears as operations such as PARTITION RANGE SINGLE, PARTITION RANGE ITERATOR, or PARTITION RANGE ALL, with Pstart and Pstop columns showing which partitions are touched. Reading one partition instead of the whole table is what turns partitioning into speed.

Why is my partitioned table still doing a full scan of every partition?

The most common cause is a predicate the optimizer cannot map to partition boundaries. Wrapping the partition key in a function such as TO_CHAR, TRUNC, or NVL, doing arithmetic on it, or triggering an implicit type conversion (for example comparing a DATE partition key to a string literal) all hide the key, so the optimizer falls back to PARTITION RANGE ALL and scans every partition even though the query returns the same rows. The fix is to filter with a range on the bare partition key, such as order_date >= DATE start AND order_date < DATE end, and to compare dates to DATE literals and numbers to numbers so no conversion is applied to the column. A plan showing Pstart = 1 and Pstop equal to the last partition is the signature of pruning being defeated.

What do Pstart and Pstop mean in an execution plan?

Pstart and Pstop are the first and last partitions the operation will access. When they show specific numbers that are equal, such as 19 and 19, the query is reading a single partition (PARTITION RANGE SINGLE). When they show a small range like 19 and 21, it is reading a contiguous set (PARTITION RANGE ITERATOR). When they show the value KEY, pruning is happening dynamically at run time, typically because a bind variable is involved, and the specific partitions are decided during execution rather than at parse time. When Pstart is 1 and Pstop is the last partition (often shown as a large sentinel number on interval-partitioned tables), the operation is PARTITION RANGE ALL, meaning no pruning occurred and every partition is read.

What is the difference between static and dynamic partition pruning?

Static pruning happens at parse time, when the optimizer can determine the exact partitions from the predicate because it uses literals or otherwise-resolvable values; the plan shows actual partition numbers in Pstart and Pstop. Dynamic pruning happens at execution time, most often because the predicate uses bind variables whose values are not known until the query runs; the plan shows KEY in Pstart and Pstop to indicate that pruning will occur but the specific partitions are chosen at run time. Dynamic pruning is not a problem and is fully expected with bind variables, which remain the right default for cursor reuse. Both are healthy; the case to worry about is PARTITION RANGE ALL, where no pruning of any kind takes place.

Does partition pruning replace indexes?

No. Partition pruning decides which partitions to read; it does not decide how to read within a partition. If a pruned partition is still large, a query that returns a small fraction of it may still need an index to avoid scanning the whole partition. Pruning and indexing are complementary: pruning narrows the segments, and an index (usually a local index, aligned with the partitioning) narrows the rows within them. A common tuning outcome is a query that prunes correctly to one partition but still scans that entire partition because the selective predicate on a non-key column has no supporting index. Reading the execution plan shows whether the cost is coming from touching too many partitions or from scanning too much within one.

Should I use a local or global index on a partitioned table?

Prefer local indexes with partitioning unless there is a specific reason not to. A local index is partitioned the same way as the table, so each index partition corresponds to one table partition; this keeps partition maintenance operations such as dropping or truncating a partition fast and localized, and it aligns naturally with pruning. A global index spans all partitions and can be necessary when you need to enforce uniqueness on a column that is not the partition key or to support a query pattern that crosses partitions efficiently, but it becomes invalid when a partition is dropped or truncated unless you maintain it with the UPDATE INDEXES clause, which turns a fast metadata operation into an index rebuild. Choose local by default and reach for global only for the cases that require it.

Does partitioning require a separate Oracle license?

The Oracle Partitioning option is a separately licensed option of Oracle Database Enterprise Edition on-premises, so using partitioning on-prem Enterprise Edition requires that license. In Oracle Cloud Infrastructure, including Autonomous Database and the Base Database and Exadata cloud services, partitioning is included. It is also available in Oracle Database Free for development and testing, which is what makes it straightforward to reproduce pruning behavior in a local container. Because licensing depends on edition and platform and can change, confirm your specific entitlement before relying on partitioning in production on-premises rather than assuming it is included.

Pruning is the same discipline as the rest of the performance series: know what the optimizer is actually doing and give it what it needs to do the fast thing. Reading the execution plan is how you catch PARTITION RANGE ALL in the first place; good statistics keep the row estimates that drive the plan honest; and SQL plan baselines keep a plan that prunes today from quietly regressing tomorrow. Partition for how the data is queried, keep functions off the partition key, and check Pstart/Pstop when a query reads more than it should. Then prove it the way that ends the argument — with the partition pruning lab, where the same query reads one partition or twenty-four depending on a single function call.

---

Originally published at uptimearchitect.com.