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.
For example, if your table is partitioned by Quarter, as ranges of three months (e.g. [Jan-Mar], [Apr-Jun], [Jul-Sep], [Oct-Dec]), then the predicate "WHERE PRTN_KEY = 'Apr'" will prune three of the partitions from processing, using the one containing [Apr-Jun] data. If you use the predicate "WHERE PRTN_KEY > 'Aug'", then just two of the partitions ([Jul-Sep], [Oct-Dec]) are included.
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 processing!
If your table is partitioned, it's typically very, very large. Pruning can eliminate large amounts of this data from your query's scanning workload, significantly reducing your return time.
Keep in mind that if you do NOT include the Partition Key in your WHERE clause, no Pruning takes place and ALL partitions will be included in the processing.
This work is licensed under a Creative Commons Attribution 3
Return to 97_Things_SQL