Data Partition - Partition Pruning (Elimination)

Data System Architecture

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

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.





Discover More
Card Puncher Data Processing
Hive - Partition

in Hive Each Table can have one or more partition. Data in each partition may be furthermore divided into Buckets. The partition columns determine how the data is stored. A separate data directory...
Card Puncher Data Processing
Oracle - Partitions (Table and Indexes)

partition in Oracle. Partitioning enables you to store one logical object – a table or index – transparently in several independent segments. Partitioning can provide great performance improvements...
Data System Architecture
SQL - Query Performance

The query performance is the of a query. OLTP query are query that comes from an OLTP application. This kind of query retrieve few rows and their performance is generally improved with a index....



Share this page:
Follow us:
Task Runner