| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Optimizer theory: Question on access paths for outer joins
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?
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: steve.adams_at_ixora.com.au 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: ListGuru_at_fatcity.com (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).Received on Wed Apr 11 2001 - 03:57:49 CDT
 |
 |