Understanding Partition Pruning
If you have a very large table (approaching or exceeding 2GB), or you need to periodically roll data out of your table, you should seriously consider partitioning it into smaller, more manageable segments. If you do this, 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".
Pruning occurs when the database optimizer determines that some partitions of the table can be safely ignored because they do not contain the data you are asking for. This can significantly reduce the amount of work required by the database when executing your query.
You cause pruning to occur by simply including the table's Partition Key in a predicate of your query's WHERE clause. The Partition Key is the column (or columns) used to define how the partitions are segmented.
When your table is partitioned by RANGE, and you specify "<partition-key> = <value>", the database optimizer will only direct the use of 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!
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.
Another important thing to understand is that the indexes of a partitioned table may also be partitioned and pruning can occur when they are referenced. Indexes can be partitioned Locally, where the indexes are partitioned exactly the same as the table, or Globally, where the indexes are partitioned in a different manner than the table. Indexes may also be non-partitioned.
This work is licensed under a Creative Commons Attribution 3
Return to 97_Things_SQL