Is Partitioning Used By The Optimizer?

From: Fred Habash <fmhabash_at_gmail.com>
Date: Wed, 8 Oct 2014 12:07:10 -0400
Message-ID: <CADpeV5xfbscJYdEVMDFbjFyxdTao2fsjBtZqMWdA+j2Bx4nshg_at_mail.gmail.com>

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:07:10 CEST