Oracle Database pruning feature effectively handles more complex predicates or SQL statements that involve partitioned tables. A common situation is when a partitioned table is joined to the subset of another table, limited by a WHERE condition. For example, consider the following query:
SELECT t.day_number_in_month, SUM(s.amount_sold)
FROM sales s, times t
WHERE s.time_id = t.time_id
AND t.calendar_month_desc='2000-12'
GROUP BY t.day_number_in_month;
If the database performed a nested loop join with times table on the right-hand side, then the query would access only the partition corresponding to this row from the times table, so pruning would implicitly take place. But, if the database performed a hash or sort merge join, this would not be possible. If the table with the WHERE predicate is relatively small compared to the partitioned table, and the expected reduction of records or partitions for the partitioned table is significant, then the database performs dynamic partition pruning using a recursive subquery. The decision whether to invoke subquery pruning is an internal cost-based decision of the optimizer.
A sample execution plan using a hash join operation would look like the following:
--------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes| Cost (%CPU)| Time | Pstart | Pstop |
--------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | | 761 (100) | | | |
| 1| HASH GROUP BY | | 20 | 640 | 761 (41) |00:00:10| | |
|* 2| HASH JOIN | | 19153 | 598K | 749 (40) |00:00:09| | |
|* 3| TABLE ACCESS FULL | TIMES | 30 | 570 | 17 (6) |00:00:01| | |
| 4| PARTITION RANGE SUBQUERY | | 918K | 11M | 655 (33) |00:00:08| KEY(SQ)|KEY(SQ)|
| 5| TABLE ACCESS FULL | SALES | 918 | 11M | 655 (33) |00:00:08| KEY(SQ)|KEY(SQ)|
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
2 - access("S"."TIME_ID"="T"."TIME_ID")
3 - filter("T"."CALENDAR_MONTH_DESC"='2000-12')
This execution plan shows that dynamic partition pruning occurred on the sales table using a subquery, as shown by the KEY(SQ) value in the PSTART and PSTOP columns.
The following is an example of advanced pruning using an OR predicate.
SELECT p.promo_name promo_name, (s.profit - p.promo_cost) profit
FROM
promotions p,
( SELECT
sales.promo_id,
SUM(sales.QUANTITY_SOLD * (costs.UNIT_PRICE - costs.UNIT_COST)) profit
FROM
sales, costs
WHERE
((sales.time_id BETWEEN TO_DATE('01-JAN-1998','DD-MON-YYYY',
'NLS_DATE_LANGUAGE = American') AND
TO_DATE('01-JAN-1999','DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American')
OR
(sales.time_id BETWEEN TO_DATE('01-JAN-2001','DD-MON-YYYY',
'NLS_DATE_LANGUAGE = American') AND
TO_DATE('01-JAN-2002','DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American')))
AND sales.time_id = costs.time_id
AND sales.prod_id = costs.prod_id)
GROUP BY
sales.promo_id) s
WHERE s.promo_id = p.promo_id
ORDER BY profit
DESC;
This query joins the sales and costs tables in the sh sample schema. The sales table is partitioned by range on the column time_id. One condition in the query is two predicates on time_id, which are combined with an OR operator. This OR predicate is used to prune the partitions in the sales table and a single join between the sales and costs table is performed. The execution plan is as follows:
--------------------------------------------------------------------------------------------------
| Id| Operation | Name |Rows |Bytes |TmpSp|Cost(%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 4 | 200 | | 3556 (14)| 00:00:43| | |
| 1| SORT ORDER BY | | 4 | 200 | | 3556 (14)| 00:00:43| | |
|* 2| HASH JOIN | | 4 | 200 | | 3555 (14)| 00:00:43| | |
| 3| TABLE ACCESS FULL |PROMOTIONS| 503 | 16599| | 16 (0)| 00:00:01| | |
| 4| VIEW | | 4 | 68 | | 3538 (14)| 00:00:43| | |
| 5| HASH GROUP BY | | 4 | 164 | | 3538 (14)| 00:00:43| | |
| 6| PARTITION RANGE OR | | 314K| 12M| | 3321 (9)| 00:00:40|KEY(OR)|KEY(OR)|
|* 7| HASH JOIN | | 314K| 12M| 440K| 3321 (9)| 00:00:40| | |
|* 8| TABLE ACCESS FULL| SALES | 402K| 7467K| | 400 (39)| 00:00:05|KEY(OR)|KEY(OR)|
| 9| TABLE ACCESS FULL | COSTS |82112| 1764K| | 77 (24)| 00:00:01|KEY(OR)|KEY(OR)|
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."PROMO_ID"="P"."PROMO_ID")
7 - access("SALES"."TIME_ID"="COSTS"."TIME_ID" AND "SALES"."PROD_ID"="COSTS"."PROD_ID")
8 - filter("SALES"."TIME_ID"<=TO_DATE('1999-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"SALES"."TIME_ID">=TO_DATE('1998-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"SALES"."TIME_ID">=TO_DATE('2001-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"SALES"."TIME_ID"<=TO_DATE('2002-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
The database also does additional pruning when a column is range-partitioned on multiple columns. As long as the database can guarantee that a particular predicate cannot be satisfied in a particular partition, the partition is skipped. This allows the database to optimize cases where there are range predicates on multiple columns or in the case where there are no predicates on a prefix of the partitioning columns.
For tips on partition pruning, refer to "Partition Pruning Tips".