Understanding Partition Pruning

From WikiContent

Revision as of 22:14, 28 November 2009 by ThomVF (Talk | contribs)
(diff) ←Older revision | Current revision (diff) | Newer revision→ (diff)
Jump to: navigation, search

If you've decided to partition one of your large tables into smaller, more manageable segments, you need to understand what Partition Pruning is and how it can benefit you.

First, the definition of "prune" is "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-Dev]) are included.

In this example, 75% of the table's data is eliminated from the 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 percentages of its data from your query's scanning work. Queries using Partition Pruning can significantly reduce the 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.

Personal tools