Re: Optimizer theory: Question on access paths for outer joins

I am a little confused. Is not PHONE, in this query, the one WITHOUT the +?

At 11:25 AM 4/10/01 -0800, you wrote:
>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 ph.id = ac.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?
>
>Thanks!
>Jay Miller
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Miller, Jay
> INET: JayMiller_at_TDWaterhouse.com
>
>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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Regina Harter
  INET: rharter_at_emc-inc.com

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).