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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Hillman, Alex
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).
