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 range and list partitioning – SAP ASE can apply partition pruning on equality (=) and range (>, >=, <, and <=) predicates on partition-key columns on a single table.
For hash partitioning – SAP ASE can apply partitioning pruning only on equality predicates on a single table.
For range, list, and hash partitioning – SAP ASE 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. 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.