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

From: Hillman, Alex <Alex.Hillman_at_usmint.treas.gov>
Date: Tue, 10 Apr 2001 14:08:31 -0700
Message-ID: <F001.002E679A.20010410132100@fatcity.com>

if ac.id column is indexed for outer join to use this index this column should be not ull or condition should be added ac.id is not null or ac.id>0 etc. It is because an index will not have rowid's for rows when this column is null.

Alex Hillman

-----Original Message-----
Sent: Tuesday, April 10, 2001 3:26 PM
To: Multiple recipients of list ORACLE-L

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: Hillman, Alex
INET: Alex.Hillman_at_usmint.treas.gov

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------