Re: SQL Execution Question
Date: Wed, 5 Oct 2011 10:04:45 -0700
Message-ID: <CAORjz=MNrE1M8Y5-D-PO1DBpeXxZM4SqY+uJXUd-vQY5i3BadA_at_mail.gmail.com>
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>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-lReceived on Wed Oct 05 2011 - 12:04:45 CDT