Re: SQL Execution Question

From: Anthony Ballo <anthony.ballo_at_onecall.com>
Date: Thu, 6 Oct 2011 09:21:46 -0700
Message-ID: <CAB32351.F2E1%anthony.ballo_at_onecall.com>

Thanks all for the tips – I'm going to explore subquery factoring… I haven't worked with it before and I appreciate the direction. Anthony

From: Jared Still <jkstill_at_gmail.com<mailto:jkstill_at_gmail.com>> Date: Wed, 5 Oct 2011 10:04:45 -0700
To: Anthony Ballo <anthony.ballo_at_onecall.com<mailto:anthony.ballo_at_onecall.com>> Cc: oracle-l <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: Re: SQL Execution Question

Try using a factored subquery (Common Table Expression in the rest of the database world) and see how (and if) the plan changes.

Really, as Jonathan mentioned, concrete examples would be useful.

There are many possibilities here.

WITH a as ( SELECT .... FROM .... WHERE ....) SELECT A.col1,

      B.col2,
      C.col3
      D.col4

FROM
(SELECT .... FROM ....) B,
(SELECT .... FROM ....) C,
(SELECT .... FROM ....) D

WHERE

     A.col1 = B.col1
     A.col1 = C.col1
     A.col1 = D.col1

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

On Wed, Oct 5, 2011 at 8:20 AM, Anthony Ballo <anthony.ballo_at_onecall.com<mailto:anthony.ballo_at_onecall.com>> wrote: I am on 10.2.0.4 - and had a question about the execution of a SQL statement. I have a modular query and was wondering if you have:

SELECT A.col1,

      B.col2,
      C.col3
      D.col4

FROM
(SELECT .... FROM .... WHERE ....) A,
(SELECT .... FROM ....) B,
(SELECT .... FROM ....) C,
(SELECT .... FROM ....) D

WHERE

     A.col1 = B.col1
     A.col1 = C.col1
     A.col1 = D.col1

Say A returns only 10 records - when B (C and D also) is executed, will it only be executed for the joined rows (10) or the full rowset returned by the SELECT statement? I'm not a pro at interpreting a Explain Plan but I suspect it is returning all rows. Whats the best way to work with this while keeping the modular approach?

Thanks in advance,

Anthony

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




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

Received on Thu Oct 06 2011 - 11:21:46 CDT