Understanding Partition Pruning
If you discover that one of the tables in your query is Partitioned into smaller, more manageable segments, you need to understand what Partition Pruning is and how it can benefit you.
First, let's look at the definition of "prune": "to cut or lop off" or "to rid or clear of".
When pruning occurs, several partitions of the table are eliminated from processing, significantly reducing the amount of work required by the database to gather your results.
You cause pruning to occur by simply including the table's Partition Key in a predicate of your query's WHERE clause.
When your table is partitioned by RANGE, and you specify "<partition-key> = <value>", then the database optimizer will only use the partition(s) that contain <value> in their range definition.
In the first example, 75% of the table's data is eliminated from processing!
Pruning works the same when the table is partitioned by LIST.
For example, if your table is partitioned by Region, as groups of US States (e.g. [NY, NJ, CT], [MA, RI], [NH, VT, ME], [FL, GA, NC, SC]), then the predicate "WHERE PRTN_KEY IN ('NH','MA') will prune two of the four partitions from processing.
In this example, 50% of the table's data is eliminated from the processing!
If your table has many millions of rows, partition pruning can eliminate large amounts of its data from your query's scanning workload. Queries using Partition Pruning can significantly reduce the return time.