Re: SQL Execution Question

It depends initially on whether the views are mergeable or non-mergeable, and then whether or not you have views where Oracle can push predicates,

If you see the VIEW operator as the parent of a part of the plan that seems to be only about an individual inline view then the view is non-mergeable.

If you still see nested loop joins between a starting VIEW and following VIEWS it is possible that predicate pushing has taken place (you ought to see the operator as VIEW PUSHED PREDICATE if it has, but this doesn't always seem to appear). The check, then is to look at the predicate section of the plan and see of join predicates from the earlier views appear in lines that are in the middle of the later views.

It's not easy to explain this is a generic way, though; a few concrete examples of different variations would be better.

Regards
Jonathan Lewis

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 i=
> t
> 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