Question: I an running a SQL against
partitioned tables and I need to be able to see whether partition
pruning is being used. The the explain plan show if partition
pruning is being used?
Answer: In a nutshell, an
execution plan with lines showing "partition range
single" or "partition range iterator" indicate
that the SQL is using partition pruning.
Oracle partition pruning documentation notes that The
partition pruning information is reflected in the execution
plan columns PSTART (PARTITION START) and PSTOP
In the case of serial SQL statements, the partition
pruning information is also reflected in the OPERATION and
OPTIONS columns of the explain plan.
Note that beginning with Oracle 11g, partition pruning
uses bloom filtering instead of subquery pruning.