RE: Is Partitioning Used By The Optimizer?
- From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
- To: "fmhabash@xxxxxxxxx" <fmhabash@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 8 Oct 2014 09:22:36 -0700
PART JOIN FILTER CREATE and PARTITION RANGE SUBQUERY are two such operations
that indicate the use of partition pruning.
Iggy
Date: Wed, 8 Oct 2014 12:07:10 -0400
Subject: Is Partitioning Used By The Optimizer?
From: fmhabash@xxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
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:
