Partition Pruning

What is Partition Pruning?

Some tables are so large that a Full Table Scan is unthinkable. If these tables are not partitioned, they should be.

The most common (by far) form of partitioning is Range Partitioning on a DATE column. For example, a table may be partitioned on TXN_DATE, with a separate partition for each year, month, or even day (I have seen a multi-terabyte database where daily partitions were the norm!).

If Oracle can use the WHERE predicates to eliminate some partitions from a search, then it probably will. This is called Partition Pruning. For example:

Explain Plan lines showing PARTITION RANGE SINGLE or PARTITION RANGE ITERATOR indicate that Oracle is performing a Partition Prune. A line of PARTITION RANGE ALL indicates Oracle is scanning all partitions. Depending on your Explain Plan tool, there is one confusing situation: if Oracle can work out exactly which partition or partitions to scan, then the step is removed from the plan. eg.

This looks like Oracle is performing a Full Table Scan of all partitions, but it is not. If that were the case, the plan would look like this:

New Explain Plan tools will show the actual partition number(s) thus eliminating this confusion.

When can I use Partition Pruning?

There are three ways to exploit partitons for performance:

How to fix SQLs that won't Partition Prune

If you are joining on the partition key, but not using equals joins, then you may have a design problem. eg.

If these tables are really big, then you are in a lot of trouble: this cannot be tuned effectively.