Partition Pruning with Zone Maps

Partition pruning is enhanced to take advantage of zone maps for pruning of complete partitions. Providing enhanced pruning capabilities provides better performance with less resource consumption and shorter time-to-information.

A zone map is a independent access structure that can be built for a table. During table scans, zone maps enable you to prune disk blocks of a table and partitions of a partitioned table based on predicates on the table columns. Zone maps have no correlation to the partition key columns of a partitioned table, so statements on partitioned tables with zone maps can prune partitions based on non-partition key columns.

Partition pruning with zone maps is especially effective when the zone map column values correlate with partition key column values. For example, the correlation can be between columns of the partitioned table itself, such as a shipping date that has a correlation to the partition key column order date in the same partitioned table, or within the join zone map columns and the partitioned table, such as a join zone map column month description from a dimension table times that correlates with the partition key column day of the partitioned table.

Example 3-2 illustrates partition pruning with zone maps for correlated columns of a partitioned table. Column s_shipdate in the partitioned table sales_range correlates with the partition key column order_date because orders are normally shipped within a couple of days after an order was received.

Due to the correlation of s_shipdate and the partition key column any selective predicate on this column has a high likelihood to enable partition pruning for the partitioned table sales_range, without having the column as part of the partitioning key.

The following SELECT statement looks for all orders that were shipped in the first quarter of 1999:

SELECT * FROM sales_range 
      WHERE s_shipdate BETWEEN to_date('01/01/1999','dd/mm/yyyy') 
      AND to_date('03/01/1999','mm/dd/yyyy');

In the following execution plan for the previous SELECT statement, zone maps are used for partition pruning and also to prune blocks from the partitions that have to be accessed.

Partition pruning with zone maps is identified by having KEY(ZM) in the PSTART and PSTOP columns of the execution plan. The block level pruning of all accessed partitions is identified by the filter predicate at table access time (id 2).

CREATE TABLE sales_range( 
  s_productid      NUMBER,
  s_saledate       DATE,
  s_shipdate       DATE,
  s_custid         NUMBER,
  s_totalprice     NUMBER)
CLUSTERING BY (s_shipdate)
WITH MATERIALIZED ZONEMAP
PARTITION BY RANGE (s_saledate)
 (PARTITION sal99q1 VALUES LESS THAN
   (TO_DATE('01-APR-1999', 'DD-MON-YYYY')),
  PARTITION sal99q2 VALUES LESS THAN
   (TO_DATE('01-JUL-1999', 'DD-MON-YYYY')),
  PARTITION sal99q3 VALUES LESS THAN
   (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')),
  PARTITION sal99q4 VALUES LESS THAN
   (TO_DATE('01-JAN-2000', 'DD-MON-YYYY')));

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |       |       |     3 (100)|          |       |       |
|   1 |  PARTITION RANGE ITERATOR       |             |    58 |  3306 |     3   (0)| 00:00:01 |KEY(ZM)|KEY(ZM)|
|*  2 |   TABLE ACCESS FULL WITH ZONEMAP| SALES_RANGE |    58 |  3306 |     3   (0)| 00:00:01 |KEY(ZM)|KEY(ZM)|
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter((SYS_ZMAP_FILTER('/* ZM_PRUNING */ SELECT "ZONE_ID$", CASE WHEN
 BITAND(zm."ZONE_STATE$",1)=1 THEN 1 ELSE CASE WHEN (zm."MAX_1_S_SHIPDATE" < :1 OR
 zm."MIN_1_S_SHIPDATE" > :2) THEN 3 ELSE 2 END END FROM "SH"."ZMAP$_SALES_RANGE" zm WHERE
 zm."ZONE_LEVEL$"=0 ORDER BY zm."ZONE_ID$"',SYS_OP_ZONE_ID(ROWID),TO_DATE(' 1999-01-01 00:00:00',
 'syyyy-mm-dd hh24:mi:ss'),TO_DATE(' 1999-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<3 AND
 "S_SHIPDATE">=TO_DATE(' 1999-01-01 00:00:00','syyyy-mm-dd hh24:mi:ss') AND "S_SHIPDATE"<=TO_DATE('
  1999-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))