RE: Partitioned Explain Plan - Pstart/Pstop = KEY after execution

I have observed similar issues from some of our databases, for CTAS statements (Microstrategy reports). The tables are either range partitioned by multi columns or list, and parallel operations are used. For one case with list partition, the query uses sub query to specify partition key lists. I have been able to solve this one using PRECOMPUTE_SUBQUERY hint.  

Thanks.  

Xiang  

> From: Christopher.Taylor2_at_parallon.net
> To: mohamed.houri_at_gmail.com
> CC: oracle-l_at_freelists.org
> Date: Thu, 6 Jun 2013 10:31:11 -0500
> Subject: RE: Partitioned Explain Plan - Pstart/Pstop = KEY after execution
>
> 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_at_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_at_parallon.net<mailto:Christopher.Taylor2_at_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
>
>
                                               

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