Understanding Partition Pruning

From WikiContent

(Difference between revisions)
Jump to: navigation, search
Line 1: Line 1:
-
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.
+
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. In this situation, 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''".
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.
+
Pruning occurs when the database server 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.
+
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.
+
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.
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.
Line 23: Line 23:
----
----
- 
-
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.
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
This work is licensed under a Creative Commons Attribution 3
Return to [[97_Things_SQL]]
Return to [[97_Things_SQL]]

Revision as of 00:49, 3 December 2009

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. In this situation, 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 server 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.


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

Personal tools