Re: Unbelievable Outer Join
Date: Thu, 13 Aug 2009 01:47:35 -0700 (PDT)
Message-ID: <aa34b9b7-44f9-4460-bd4f-12da5168125c_at_q23g2000yqn.googlegroups.com>
On 12 srp, 23:22, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Jan" <noreply...._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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
Thanks for all the hints. I am really impressed with how helpfull you
are being.
I know I could try newer syntax but it's not easy to change the SQL
withing the life system now, to have one select with different syntax
from all the others is not neat either.
Statistics are up to date, TI238_14935572 has dynamic sampling
enforced as this table has for example 0 rows at time T1, 3 milion
rows at time T2 and back to 0 at time T3 all within few minutes, like
this the whole day.
My question is "simple". How can oracle, by this not being a BUG for whatever reason, apply left outer join of "preserved" table TT203_10100000001372631 that has 6798 rows on the outer joined table TI238_14935572 (with one row) and estimate it will end up with 1 row. The joining column is actually the primary key of the TT203_% table (thus it is not possible 1 row in TI238_14935572 would eliminate all 6798 rows.
This is the explain plan again:
| Id | Operation | Name | Rows ---------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | 1 | HASH UNIQUE | | 1 |* 2 | TABLE ACCESS BY INDEX ROWID | TI238_185727 | 1 | 3 | NESTED LOOPS | | 1 | 4 | NESTED LOOPS | | 1 | 5 | NESTED LOOPS | | 1 |* 6 | FILTER | | | 7 | NESTED LOOPS OUTER | | 1 |* 8 | TABLE ACCESS FULL | TT203_10100000001372631 | 6798 |* 9 | TABLE ACCESS BY INDEX ROWID | TI238_14935572 | 1 |* 10 | INDEX RANGE SCAN | I_T201_14935572 | 1 |* 11 | INDEX RANGE SCAN | IDX_T510_PARENT_TO_CHILD | 111 | 12 | PARTITION RANGE ITERATOR | | 1 |* 13 | TABLE ACCESS BY LOCAL INDEX ROWID| TT203_3401330 | 1 |* 14 | INDEX RANGE SCAN | UQ_3401330_BD | 48 |* 15 | INDEX RANGE SCAN | I_VAL_185727 | 1 ----------------------------------------------------------------------------------
At this moment I have no means to "play" with the original query as this is life environment, content of TI238_14935572 has completely changed since then and to simulate this problem on test enviroment will require significant effort. Does it however matter for replying the puzzling question?
I tried to simulate the problem on a test case (different 10.2.0.3.0 environment, most likely identical settings) that should have identical joining logic to the original and in this simulation the optimizer behaves correctly. This is the explain plan of the test case: (I can provide the plsql code for this simulation if anyone is intrested)
| Id | Operation | Name | Starts | E-Rows |A-Rows
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |* 2 | FILTER | | 1 | | 9998 |* 3 | FILTER | | 1 | | 9998 | 4 | NESTED LOOPS OUTER | | 1 | 9999 | 9999 |* 5 | TABLE ACCESS FULL | TABLE1 | 1 | 9999 | 9999 |* 6 | TABLE ACCESS BY INDEX ROWID| TABLE2 | 9999 | 1 | 1 |* 7 | INDEX RANGE SCAN | T2_T3_ID | 9999 | 1| 9999
Friends, I am reading and re-reading your tips. At this point I am not even thinking how to fix the problem yet. I just can't believe how the problem could occur in the first place and still BUG seems to be the only answer. Or is there another explanation?
Regards,
Jan Rod
Received on Thu Aug 13 2009 - 03:47:35 CDT