Hi Jay,
Under rule based optimization deficient (outer joined) relations are always last
in the join order. In general cost based optimization will do the same, but it
is not bound to do so. For example, if T2 below is a complex view, then it
cannot be merged into the parent query because it is being outer joined. That
would mean that the view must be instantiated and accessed via a sort-merge join
or hash join or used to drive query. If so, the optimizer may well choose to
instantiate the view and drive the query as a nested loops join from there.
However, in general it will no do so without a good reason.
In this case however I suspect that the problem is that at least one of the
in-line views T0 and T1 are mergeable. Because the merging of in-line views is
done before query optimization, the optimizer never gets to consider the
cardinality of the potential instantiations of the in-line views. The optimizer
is presented with a merged query involving all the base tables for the merged
views and PHONE and ACCOUNT. Worse than that, because transitivity analysis is
not done for join predicates, you end up with an almost linear join topology.
Also, because PHONE and T2 are outer joined, OR expansion and IN-list iterators
cannot be used (lest duplicates might be introduced) to obtain index-based
access paths to the other base tables involved and thus driving the query from
PHONE (or from an index on ACCOUNT with your extraneous predicate) is natural.
If this analysis is right, and you are right about the cardinality of the
in-line views, the correct approach to optimizing this query is to place a
NO_MERGE hint in the query block for the relevant in-line views, move T2 to the
end of the from clause, and place ORDERED and USE_HASH(T2) hints in the outer
query block.
Hope this help,
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-----Original Message-----
>Hi all,
>
>I was just trying to figure out why in an outer join Oracle prefers to
>access the table with the (+) first. I would have thought that the table
>from which all the data was coming would come first and then appropriate
>rows would come from the second table with nulls being generated for
>non-existent rows. I started looking at it because of the following query:
>
>Simplified SQL:
>
>select stuff
> from inlineview1 T0,
> inlineview2 T1,
> inlineview3 T2,
> account ac,
> phone ph
> where T0.generic_id = T1.account_id
> and T1.account_id = T2.account_id (+)
> and T1.valid_flag = T2.valid_flag (+)
> and T0.generic_id = ac.id
> and ac.id = ph.current_phone_id(+)
>
>In this query the inline views are rather complicated but apply substantial
>restrictions on ACCOUNT (a huge table, as is PHONE). Logically, it is
>faster to run the inline views first, join them to ACCOUNT and then go to
>PHONE. The Optimizer kept doing a full table scan on PHONE first, and then
>joining to Account. I tried ORDERED, FIRST_ROWS and INDEX hints to no
>avail.
>The hints work if I take away the outer join symbol (but of course this
>gives incomplete results).
>I finally tricked Oracle into going in the correct order by adding a WHERE
>clause to the ACCOUNT of
>AND ac.id > 0
>(presumably causing the Optimizer to think there's more of a restriction on
>ACCOUNT and therefore taking it first). Since id is always greater than 0
>this doesn't change the results but makes the query run much faster.
>
>So I have it working the way I want, but I'm still wonderinG why the
>Optimizer prefers to read the (+) table first? From the "Everything you
>always Wanted to Know About the Oracle Optimizer" book I know that the
>Optimizer tries to sort the join orders in ascending order of their
computed
>cardinality. I'd guess that the Optimizer assumes an outer joined table
>will be returning some default percentage of the other table and therefore
>should always be accessed first?
>Can anyone confirm or refute this?
>
---------------------------------------------------------------------------------
You can view this message online at http://www.ixora.com.au/q+a/0104/11164729.htm
To unsubscribe from Ixora Answers send a blank email to [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve Adams
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
