Re: Unbelievable Outer Join

"Jan" <noreply.jan_at_gmail.com> wrote in message news:0673c7b2-fb00-4ad9-8305-3a3480de5b67_at_n11g2000yqb.googlegroups.com...
> Hi,
> our 10.2.0.4.0 - 64bit EE running on Windows Server 2003 executes one
> kind of insert from select whose explain plan I can't comprehend.
>
>
> See the NESTED LOOPS OUTER between TT203_10100000001372631 and
> TI238_14935572?
> The join is written this way:
> TT203_10100000001372631.OID = TI238_14935572.T203VALUE_OID (+)
>
> Inner table TT203_10100000001372631 has really 6798 rows and outer
> table TI238_14935572 has one. Why would optimizer think this will
> result in 1 row????
>
> Do you have any experience with this behavior?

It's done that way because it's the right way to do it.

TT203_10100000001372631.OID = TI238_14935572.T203VALUE_OID (+)

|  7 | NESTED LOOPS OUTER           |
|* 8 |  TABLE ACCESS FULL           | TT203_10100000001372631
|* 9 |  TABLE ACCESS BY INDEX ROWID | TI238_14935572
|*10 |   INDEX RANGE SCAN           | I_T201_14935572

The "preserved" table must come first in the join order if you are using a nested loop join. Your syntax is saying:

    report the row from TT203 and matching rows from TI238,     even if there are no rows from TI238

Consequently, for each row in TT203 you must try to find rows from TI238 - hence the plan.

Since you are on 10.2 you have the option for forcing a right outer hash join if you want to reverse the apparent order of execution - but this may not help.

The "1" in line 9 is "one row for each row from line 8", not "one row in total".

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html