Are you using bind variables on the partition keys? I normally see the key: key values when that happens. You don't want to use binds on partition key fields. Don. On Jun 6, 2013 10:33 AM, <Christopher.Taylor2@xxxxxxxxxxxx> wrote: > 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@xxxxxxxxx] > 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@xxxxxxxxxxxx<mailto: > Christopher.Taylor2@xxxxxxxxxxxx>> > 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 > > > -- > //www.freelists.org/webpage/oracle-l > > > > > -- > Bien Respectueusement > Mohamed Houri > > -- > //www.freelists.org/webpage/oracle-l > > > -- //www.freelists.org/webpage/oracle-l