RE: Query Performance

  • From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
  • To: <jkstill@xxxxxxxxx>, <bill@xxxxxxxxxxxx>
  • Date: Thu, 22 Jul 2010 08:05:55 -0500

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
> 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:
Home Page:

Other related posts: