Data Partition - Partition Pruning (Elimination)

About

Partition Pruning is access paths methods that occurs when a table is partitioned by the column in the predicate. In this case, the database will read only the partitions involved and not the full table. The partitions not involved are eliminated (or pruned) from the scan.

ping Algebraic Optimization

Articles Related

Example

The product managers (PM) are interested in the sales figures of their respective products only, not in all the sales.

So the PM of product code “8” issues this SQL statement several times an hour:

select 
   sales_dt, 
   sum(amount), 
   avg (amount), 
   max(amount), 
   min(amount)
from sales
where product_code = 8
group by sales_dt;

To improve the performance of this query, you may:

• build an index on the PRODUCT_CODE column, but it will increase the execution time on INSERT statements and might also adversely influence the execution plans of other queries on the same table. Therefore, it might be judicious not to create the index but to:
• list-partition the table on the PRODUCT_CODE column, with one partition per PRODUCT_CODE value. The PM's query will still do a full-table scan, but this time the scope of the “full table” is actually the partition, not the entire table. This enhances the performance immensely.

Recall that list partitioning was preferred over range partitioning in this case because the PRODUCT_CODE is a discrete value.