RE: Query Performance

And even more benefit to see the STAT lines from a 10046 Trace with the 
execution plans. 

My hunch is that it's doing sub-query unnesting and it can't do partition 
pruning because the sub query is joined with the main query in such a way that 
it can't do the pruning.  Maybe using a WITH clause for the sub query would do 
the trick. 

Ric Van Dyke
Hotsos Enterprises Ltd




-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Jared Still
Sent: Thu 22-Jul-10 4:20 AM
To: bill@xxxxxxxxxxxx
Cc: Oracle-L@xxxxxxxxxxxxx
Subject: Re: Query Performance
 
On Wed, Jul 21, 2010 at 11:10 AM, Bill Zakrzewski <bill@xxxxxxxxxxxx> wrote:

> All -
>
> Oracle 10.2.0.4.0
> RH Linux 5.3
>
> I have a query that contains a subquery.  When I run the subquery as a
> standalone it completes in less than 2 minutes, but the full query takes
> over 2 hours and ATTR_CODE is a small table.  The first thing I noticed was
> that when I run the subquery as a standalone it uses partition pruning for
> the incidentkey table, but when incorporated as a subquery it does not.  FYI
> - it is partitioned on IN_DATE - one partition for each year.  Any thoughts
> for why this is happening or how I can force it to use the partitioning?
>
>
>
It would probably be beneficial to see the execution plan for both queries.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com

Other related posts: