Explain partition pruning.
In the world of massive databases, performance isn't just about how fast you can find data—it's about how much data you can ignore.
Partition Pruning is the single most important performance benefit of partitioning. It is the database’s ability to look at your query and say, "I know exactly which parts of this massive table don't have what you're looking for, so I'm not even going to look at them."
Imagine you have a SALES table with 10 years of data (1 billion rows). You've partitioned this table by Year.
If you run a query asking for sales in December 2025, without pruning, Oracle would have to scan the entire 1-billion-row table. With Partition Pruning, Oracle "prunes" (discards) the partitions for 2015 through 2024. It only opens and reads the 2025 partition.
You’ve just ignored 90% of the table, making the query 10x faster with zero extra effort.
Pruning happens automatically during the Optimization phase. The Optimizer compares the constants in your WHERE clause to the high-values defined in the table's partitioning metadata.
There are two main types of pruning:
This happens when your SQL has hard-coded values.
Query: SELECT * FROM sales WHERE sale_date = '01-JAN-2026';
Action: Oracle knows at compile-time exactly which partition to hit.
This happens when the filter isn't known until the query is actually running (e.g., using a subquery or a bind variable).
Query: SELECT * FROM sales WHERE sale_date = (SELECT today FROM calendar);
Action: Oracle identifies the correct partition at run-time as soon as the subquery provides the date.
Pruning works differently depending on how you sliced your data:
| Partition Type | Pruning Logic | Example |
| Range | Uses "Greater than/Less than" logic. | WHERE year = 2025 |
| List | Uses discrete "In list" logic. | WHERE region IN ('North', 'East') |
| Hash | Uses a mathematical formula. | WHERE user_id = 500 |
Note on Hash: For Hash partitioning to prune, you must use an equality operator ($=$). Range filters (like
user_id > 500) do not work for pruning in Hash partitions because the data is scattered mathematically.
You can tell if pruning is working by looking at the Pstart (Partition Start) and Pstop (Partition Stop) columns in an execution plan.
Pstart/Pstop = Numbers (e.g., 5, 5): Success! Oracle only visited partition 5.
Pstart/Pstop = 1, 120: Failure. Oracle scanned the whole table (all 120 partitions).
Pstart/Pstop = KEY: Success! This indicates Dynamic Pruning (it will decide the partition at runtime).
Even with a partitioned table, pruning can fail if you aren't careful:
Functions on Columns: WHERE TRUNC(sale_date) = '01-JAN-2026'. This often breaks pruning because Oracle can't easily map the function result to the partition key.
Data Type Mismatches: If sale_date is a DATE but you pass a VARCHAR string without an explicit TO_DATE, the implicit conversion can prevent pruning.
Missing Partition Key: If you partition by REGION but your query only filters by CUSTOMER_NAME, Oracle has no choice but to scan every region.
| Metric | Without Pruning | With Pruning |
| I/O Operations | Massive (Total Table Scan). | Minimal (Targeted Scan). |
| Memory Usage | High (Buffer cache thrashing). | Efficient. |
| User Experience | Frustratingly slow. | Near-instant. |
In a Sub-partitioned table (e.g., Partition by Year, Sub-partition by Region), Oracle can perform two-level pruning. It can prune the years you don't need and the regions you don't need within that year, narrowing the search to a tiny fraction of the total data.