Partition Pruning

Partition pruning, or partition elimination, can save considerable time and resources during execution.

Semantics-based partitioning allow SAP ASE to eliminate certain partitions when performing a search. Range-based partitions, for example, contain rows for which partitioning keys are discrete value sets. When a query predicate—a where clause—is based on those partitioning keys, SAP ASE can quickly ascertain whether rows in a particular partition can satisfy the query. This behavior is called partition pruning.

For example, suppose the roysched table in pubs2 is partitioned on hirange and royalty. SAP ASE 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 SAP ASE can return query results more efficiently because it needs to scan only p1 and p2.

In these examples, SAP ASE does not use partition pruning:
select * from roysched
where hirange != 5000
select * from roysched
where royalty*0.15 >= 45
Note: 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.
Related concepts
Composite Partitioning Keys