RE: Is Partitioning Used By The Optimizer?

From: TJ Kiernan <tkiernan_at_pti-nps.com>
Date: Wed, 8 Oct 2014 16:27:16 +0000
Message-ID: <196DB2D4BDE5804EAF3158CCC1C698BC0A475728_at_lopez.pti-nps.com>

In your case, partition elimination does not appear to be used. If you’re using a global index to access rows on a partitioned table, you’re not going to get partition elimination.

Execution plans will include PSTART & PSTOP columns to indicate which partitions are used in the event of partition elimination. The possible values are a (sub)partition name, ‘KEY’, or ‘ALL’. KEY indicates that partition elimination is used, but due to things like bind variables, it the optimizer may not know which partition(s) will be used at parse time.

HTH,
T. J.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Fred Habash Sent: Wednesday, October 08, 2014 11:07 AM To: oracle-l_at_freelists.org
Subject: Is Partitioning Used By The Optimizer?

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)

--

http://www.freelists.org/webpage/oracle-l Received on Wed Oct 08 2014 - 18:27:16 CEST