Partition Pruning

Partition pruning is an essential performance feature for data warehouses. In partition pruning, the optimizer analyzes FROM and WHERE clauses in SQL statements to
eliminate unneeded partitions when building the partition access list. This enables
Oracle Database to perform operations only on those partitions that are relevant to the SQL statement.

Oracle Database prunes partitions when you use range, LIKE, equality, and IN-list predicates on the range or list partitioning columns, and when you use equality and IN-list predicates on the hash partitioning columns. Partition pruning dramatically reduces the amount of data retrieved from disk and shortens processing time, thus improving query performance and resource utilization.

If you partition the index and table on different columns (with a global partitioned
index), partition pruning also eliminates index partitions even when the partitions of the underlying table cannot be eliminated. Depending upon the actual SQL statement, Oracle Database may use static or dynamic pruning. Static pruning occurs at compile-time, with the information about the partitions accessed beforehand while dynamic pruning occurs at run-time, meaning that the exact partitions to be accessed by a statement are not known beforehand. A sample scenario for static pruning would be a SQL statement containing a WHERE condition with a constant literal on the partition key column. An example of dynamic pruning is the use of operators or functions in the WHERE condition. Partition pruning affects the statistics of the objects where pruning will occur and will therefore also affect the execution plan of a statement.


Information that can be Used for Partition Pruning
Oracle Database prunes partitions when you use range, LIKE, equality, and IN-list
predicates on the range or list partitioning columns, and when you use equality and
IN-list predicates on the hash partitioning columns.

On composite partitioned objects, Oracle can prune at both the range partition level
and at the hash or list subpartition level using the relevant predicates. Examine the
table sales_range_hash created earlier in this chapter, which is partitioned by
range on the column s_salesdate and subpartitioned by hash on the column s_
productid, and consider the following example:
SELECT * FROM sales_range_hash
WHERE s_saledate BETWEEN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY'))
AND (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')) AND s_productid = 1200;


Oracle uses the predicate on the partitioning columns to perform partition pruning as
follows:
■ When using range partitioning, Oracle accesses only partitions sal99q2 and
sal99q3, representing the partitions for the third and fourth quarters of 1999.
■ When using hash subpartitioning, Oracle accesses only the one subpartition in
each partition that stores the rows with s_productid=1200. The mapping
between the subpartition and the predicate is calculated based on Oracle's internal
hash distribution function


How to Identify Whether Partition Pruning has been Used
Whether Oracle uses partition pruning or not is reflected in the execution plan of a
statement, either in the plan table for the EXPLAIN PLAN statement or in the shared
SQL area.
The partition pruning information is reflected in the plan columns PARTITION_
START and PARTITION_STOP. In the case of serial statements, the pruning


Static Partition Pruning
For a number of cases, Oracle determines the partitions to be accessed at compile time.
This happens when the predicates on the partitioning columns use a range predicate.
In addition, the predicates must only use constants so that Oracle can determine the
start and stop partition numbers at compile time. When this happens, the actual
partition numbers show up in the partition start partition stop columns of the explain
plan. The following is an example:


SQL> explain plan for select * from sales where time_id = to_date('01-jan-2001', 'dd-mon-yyyy');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 3971874201
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 673 | 19517 | 27 (8)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 673 | 19517 | 27 (8)| 00:00:01 | 17 | 17 |
|* 2 | TABLE ACCESS FULL | SALES | 673 | 19517 | 27 (8)| 00:00:01 | 17 | 17 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TIME_ID"=TO_DATE('2001-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
information is also reflected in the OPERATION and OPTIONS columns.

This plan shows that Oracle accesses partition number 17, as shown in the PSTART
and PSTOP columns.

Dynamic Partition Pruning
Oracle Database converts the pruning predicates into a partition number at compile
time whenever it can. However, there are a number of cases when this is not possible.
For example, if a predicate on a partitioning column involves a bind variable, Oracle
cannot determine the partition numbers at compile time. Even if bind variables are not used, for certain classes of predicates such as IN-list predicates Oracle accesses a list of predicates not a range. In all these cases, the explain plan will show KEY in the partition start and stop columns. There is also some information about the type of predicate used. For example, if an IN-list predicate was used, the plan output will show KEY(I) in the partition start and stop column. For example, consider the following:



SQL> explain plan for select * from sales s where time_id in ( :a, :b, :c, :d);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 513834092
---------------------------------------------------------------------------------------------------
| Id | Operation | Name |Rows|Bytes|Cost (%CPU)| Time | Pstart| Pstop|
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |2517|72993| 292 (0)|00:00:04| | |
| 1 | INLIST ITERATOR | | | | | | | |
| 2 | PARTITION RANGE ITERATOR | |2517|72993| 292 (0)|00:00:04|KEY(I) |KEY(I)|
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |2517|72993| 292 (0)|00:00:04|KEY(I) |KEY(I)|
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE |SALES_TIME_BIX| | | | |KEY(I) |KEY(I)|
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("TIME_ID"=:A OR "TIME_ID"=:B OR "TIME_ID"=:C OR "TIME_ID"=:D)
17 rows selected.
For parallel plans only, the partition start and stop columns contain the partition
pruning information; the operation column will contain information for the parallel
operation, as shown in the following example:
SQL> explain plan for select * from sales where time_id in ( :a, :b, :c, :d);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 4058105390
-------------------------------------------------------------------------------------------------
| Id| Operation | Name |Rows|Bytes|Cost(%CP| Time |Pstart| Pstop| TQ |INOUT| PQ Dis|
-------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | |2517|72993| 75(36)|00:00:01| | | | | |
| 1| PX COORDINATOR | | | | | | | | | | |
| 2| PX SEND QC(RANDOM)|:TQ10000|2517|72993| 75(36)|00:00:01| | |Q1,00| P->S|QC(RAND|
| 3| PX BLOCK ITERATOR| |2517|72993| 75(36)|00:00:01|KEY(I)|KEY(I)|Q1,00| PCWC| |
|* 4| TABLE ACCESS FULL| SALES |2517|72993| 75(36)|00:00:01|KEY(I)|KEY(I)|Q1,00| PCWP| |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("TIME_ID"=:A OR "TIME_ID"=:B OR "TIME_ID"=:C OR "TIME_ID"=:D)
16 rows selected.

Posted in: Partitioning