From: <Christopher.Taylor2@parallon.net>
To: <mohamed.houri@gmail.com>
CC: <oracle-l@freelists.org>
Date: Thu, 6 Jun 2013 10:31:11 -0500
Subject: RE: Partitioned Explain Plan - Pstart/Pstop = KEY after execution
Thread-Topic: Partitioned Explain Plan - Pstart/Pstop = KEY after execution
Message-ID: <F05D8DF1FB25F44085DB74CB916678E887A05B42F4@NADCWPMSGCMS10.hca.corpad.net>
References: <F05D8DF1FB25F44085DB74CB916678E887A05B427D@NADCWPMSGCMS10.hca.corpad.net>
 <CAJu8R6gUD5D54EjUNM8SdAoiFzgL+OzKzJWA4GD_U9ED=Ag5uw@mail.gmail.com>
In-Reply-To: <CAJu8R6gUD5D54EjUNM8SdAoiFzgL+OzKzJWA4GD_U9ED=Ag5uw@mail.gmail.com>
Accept-Language: en-US
Content-Language: en-US
acceptlanguage: en-US
Content-type: text/plain
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: Christopher.Taylor2@parallon.net

Well, complete partition pruning definitely did not occur.  (See my email about running the trace file through orasrp - multiple partitions were touched/checked/scanned for the partitioned table in question.
It threw me off that after execution it still said KEY for the execution plan.

(Unless you're telling me that every partition will be touched in a partition pruning scenario anyway which I don't *think* would happen but perhaps it might)

Chris

From: Mohamed Houri [mailto:mohamed.houri@gmail.com]
Sent: Thursday, June 06, 2013 10:26 AM
To: Taylor Christopher - Nashville
Cc: ORACLE-L
Subject: Re: Partitioned Explain Plan - Pstart/Pstop = KEY after execution

The partition pruning did occur in this case. This is just an indication that during the parse time the optimizer couldn't know what is the exact partition to prune. This happens when you are using partition keys as bind variable

Best regards

Mohamed Houri
www.hourim.wordpress.com<http://www.hourim.wordpress.com>

2013/6/6 <Christopher.Taylor2@parallon.net<mailto:Christopher.Taylor2@parallon.net>>
Env: 10.2.0.4 RAC Linux 64-bit
I've got an explain plan with Pstart/Pstop with "KEY" instead of partition numbers.  I've been reading http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf (The Oracle Optimizer Explain the Explain Plan) and it explains that KEY is used when Oracle believes the execution will dynamically partition prune.

So I executed my statement and traced it (of course) and still the explain plan shows KEY KEY and running it through orasrp shows that many partitions were scanned (perhaps not all of them however - I haven't checked).

So, is it safe to say that *after* execution if the plan has KEY KEY then partition pruning did not occur?

Thanks!!

Chris Taylor
Oracle DBA
Parallon IT&S


--
http://www.freelists.org/webpage/oracle-l


--
Bien Respectueusement
Mohamed Houri

--
http://www.freelists.org/webpage/oracle-l