Semantics-based partitioning can allow Adaptive Server to eliminate certain partitions when performing a search. Range-based partitions, for example, contain rows whose partitioning keys are discrete value sets. When a query predicate—a where clause—is based on those partitioning keys, Adaptive Server can quickly ascertain whether rows in a particular partition can satisfy the query. This behavior is called partition pruning, or partition elimination, and it can save considerable time and resources during execution.
For range and list partitioning – Adaptive Server can apply partition pruning on equality (=) and range (>, >=, <, and <=) predicates on partition-key columns on a single table.
For hash partitioning – Adaptive Server can apply partitioning pruning only on equality predicates on a single table.
For range, list, and hash partitioning – Adaptive Server cannot apply partition pruning on predicates with “not equal to” (!=) clauses or to complex predicates that have expressions on the partitioning column.
For example, suppose the roysched table in pubs2 is partitioned on hirange and royalty—see “Composite partitioning keys”. Adaptive Server can use partitioning pruning on this query:
select avg(royalty) from roysched where hirange <= 10000 and royalty < 9
The partition pruning process identifies p1 and p2 as the only partitions to qualify for this query. Thus, the p3 partition need not be scanned, and Adaptive Server can return query results more efficiently because it needs to scan only p1 and p2.
In these examples, Adaptive Server does not use partition pruning:
select * from roysched where hirange != 5000
select * from roysched where royalty*0.15 >= 45
In serial execution mode, partition pruning applies
only to scans, inserts, deletes, and updates; partition pruning
does not apply to other operators. In parallel execution mode, partition
pruning applies to all operators.
