Oracle Database pruning feature effectively handles more complex predicates or SQL statements that involve partitioned tables.Ī common situation is when a partitioned table is joined to the subset of another table, limited by a WHERE condition. Equality and IN-list predicates can prune with all the partitioning methods. However, on list-partitioned tables, range predicates may not map to a contiguous set of partitions. Range predicates cannot be used for pruning on hash partitioned tables, but they can be used for all other partitioning strategies. The partitioning type plays a role in which predicates can be used. Similarly, a range or an IN-list predicate on the time_id column and the optimizer would be used to prune to a set of partitions. Predicate Information (identified by operation id):ģ - filter("TIME_ID"=TO_DATE(' 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) | Id | Operation | Name | Rows| Bytes | Cost (%CPU)| Time |Pstart| Pstop | WHERE time_id = TO_DATE('0', 'DD-MON-YYYY') īecause there is an equality predicate on the partitioning column of sales, the query is pruned down to a single predicate and this is reflected in the following execution plan: As an example, consider the following query: The three predicate types, equality, range, and IN-list, are the predicates most commonly used for partition pruning. The optimizer uses a wide variety of predicates for pruning.