RE: Query Performance

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Thu, 22 Jul 2010 08:05:55 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF01190CFE_at_WIN02.hotsos.com>

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_at_freelists.org on behalf of Jared Still Sent: Thu 22-Jul-10 4:20 AM
To: bill_at_intactus.com
Cc: Oracle-L_at_freelists.org
Subject: Re: Query Performance  

On Wed, Jul 21, 2010 at 11:10 AM, Bill Zakrzewski <bill_at_intactus.com> 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

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

Received on Thu Jul 22 2010 - 08:05:55 CDT