Is Partitioning Used By The Optimizer?
- From: Fred Habash <fmhabash@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 8 Oct 2014 12:07:10 -0400
How can we decide with some certainty if partitioning is or is no being
used by the optimizer in an Oracle database?
My research led me to inspect the values in
V$SQL_PLAN.P_START|P_STOP|OPTIONS|OPERATION.
Pruning is not used when ...
1) p_start/p_stop are null
2) p_start/p_stop have integers and they indicate a range that includes all
partitions
3) OPERATION reveals 'ARTITION RANGE ALL'.
The DB I'm looking at has 'ROW LOCATION' for p_start/stop. It is obvious
that this indicates dynamic pruning. However, the the OPERATION column
shows 'BY GLOBAL INDEX ROWID' for OPERATION 'TABLE ACCESS.
Does this really mean that partition pruning was used? Is there any other
empirical evidence we can produce to make such decision?
--
Thank you ...
----------------------------------------
Fred Habash, Database Solutions Architect (Oracle OCP 8i,9i,10g,11g)
Other related posts:
