13.2 Using Partition Pruning

Table of Contents Previous Next


13 Table Partitioning : 13.2 Using Partition Pruning

Advanced Server's query planner uses partition pruning to compute an efficient plan to locate a row (or rows) that matches the conditions specified in the WHERE clause of a SELECT statement. To successfully prune partitions from an execution plan, the WHERE clause must constrain the information that is compared to the partitioning key column specified when creating the partitioned table. When querying a:
list-partitioned table, partition pruning is effective when the WHERE clause compares a literal value to the partitioning key using operators like equal (=) or AND.
range-partitioned table, partition pruning is effective when the WHERE clause compares a literal value to a partitioning key using operators such as equal (=), less than (<), or greater than (>).
hash-partitioned table, partition pruning is effective when the WHERE clause compares a literal value to the partitioning key using an operator such as equal (=).
The constraint_exclusion parameter controls constraint exclusion. The constraint_exclusion parameter may have a value of on, off, or partition. To enable constraint exclusion, the parameter must be set to either partition or on. By default, the parameter is set to partition.
When you execute a SELECT statement that does not contain a WHERE clause, the query planner must recommend an execution plan that searches the entire table. When you execute a SELECT statement that does contain a WHERE clause, the query planner determines in which partition that row would be stored, and sends query fragments to that partition, pruning the partitions that could not contain that row from the execution plan. If you are are not using partitioned tables, disabling constraint exclusion may improve performance.
Like constraint exclusion, fast pruning can only optimize queries that include a WHERE (or join) clause, and only when the qualifiers in the WHERE clause match a certain form. In both cases, the query planner will avoid searching for data within partitions that cannot possibly hold the data required by the query.
Fast pruning is controlled by a boolean configuration parameter named edb_enable_pruning. If edb_enable_pruning is ON, Advanced Server will fast prune certain queries. If edb_enable_pruning is OFF, the server will disable fast pruning.
For LIST-partitioned tables, Advanced Server can fast prune queries that contain a WHERE clause that constrains a partitioning column to a literal value. For example, given a LIST-partitioned table such as:
(
PARTITION americas VALUES('US', 'CA', 'MX'),
PARTITION europe VALUES('BE', 'NL', 'FR'),
PARTITION asia VALUES('JP', 'PK', 'CN'),
PARTITION others VALUES(DEFAULT)
)
Fast pruning can reason about WHERE clauses such as:
Given the first WHERE clause, fast pruning would eliminate partitions europe, asia, and others because those partitions cannot hold rows that satisfy the qualifier: WHERE country = 'US'.
Given the second WHERE clause, fast pruning would eliminate partitions americas, europe, and asia because because those partitions cannot hold rows where country IS NULL.
The operator specified in the WHERE clause must be an equal sign (=) or the equality operator appropriate for the data type of the partitioning column.
For range-partitioned tables, Advanced Server can fast prune queries that contain a WHERE clause that constrains a partitioning column to a literal value, but the operator may be any of the following:
Fast pruning can reason about WHERE clauses such as:
Note that fast pruning can also optimize DELETE and UPDATE statements containing WHERE clauses of the forms described above.

13 Table Partitioning : 13.2 Using Partition Pruning

Table of Contents Previous Next