Re: Unbelievable Outer Join
Date: Wed, 12 Aug 2009 07:55:08 -0700 (PDT)
Message-ID: <f9aebc22-821f-46f7-9a55-a3e6afaef9a1_at_a13g2000yqc.googlegroups.com>
On Aug 12, 10:25 am, Jan <noreply...._at_gmail.com> wrote:
> On 12 srp, 16:16, Jan <noreply...._at_gmail.com> wrote:
>
>
>
>
>
> > 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.
>
> > This is the explain plan of the statement:
> > -----------------------------------------------------------------------------------------------------------------------------------
> > | Id | Operation |
> > Name | Rows | Bytes | Cost (%CPU)| Time |
> > Pstart| Pstop
> > |
> > -----------------------------------------------------------------------------------------------------------------------------------
> > | 0 | INSERT STATEMENT
> > | | | | 512 (100)|
> > | |
> > |
> > | 1 | HASH UNIQUE
> > | | 1 | 167 | 512 (1)| 00:00:07
> > | |
> > |
> > |* 2 | TABLE ACCESS BY INDEX ROWID |
> > TI238_185727 | 1 | 26 | 2 (0)| 00:00:01
> > | |
> > |
> > | 3 | NESTED LOOPS
> > | | 1 | 167 | 511 (1)| 00:00:07
> > | |
> > |
> > | 4 | NESTED LOOPS
> > | | 1 | 141 | 509 (1)| 00:00:07
> > | |
> > |
> > | 5 | NESTED LOOPS
> > | | 1 | 91 | 100 (2)| 00:00:02
> > | |
> > |
> > |* 6 | FILTER
> > | | | | |
> > | |
> > |
> > | 7 | NESTED LOOPS OUTER
> > | | 1 | 76 | 99 (3)| 00:00:02
> > | |
> > |
> > |* 8 | TABLE ACCESS FULL |
> > TT203_10100000001372631 | 6798 | 331K| 99 (3)| 00:00:02
> > | |
> > |
> > |* 9 | TABLE ACCESS BY INDEX ROWID |
> > TI238_14935572 | 1 | 26 | 0 (0)|
> > | |
> > |
> > |* 10 | INDEX RANGE SCAN |
> > I_T201_14935572 | 1 | | 0 (0)|
> > | |
> > |
> > |* 11 | INDEX RANGE SCAN |
> > IDX_T510_PARENT_TO_CHILD | 111 | 1665 | 1 (0)| 00:00:01
> > | |
> > |
> > | 12 | PARTITION RANGE ITERATOR
> > | | 1 | 50 | 409 (1)| 00:00:05 |
> > KEY | 23
> > |
> > |* 13 | TABLE ACCESS BY LOCAL INDEX ROWID|
> > TT203_3401330 | 1 | 50 | 409 (1)| 00:00:05 |
> > KEY | 23
> > |
> > |* 14 | INDEX RANGE SCAN |
> > UQ_3401330_BD | 48 | | 360 (1)| 00:00:05 |
> > KEY | 23
> > |
> > |* 15 | INDEX RANGE SCAN |
> > I_VAL_185727 | 1 | | 1 (0)| 00:00:01
> > | |
> > |
> > -----------------------------------------------------------------------------------------------------------------------------------
>
> > (I hope the formatting will show up correctly)
>
> > 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????
>
> > The plan is that completely wrong because of this. The select ran 50
> > minutes instead of a under a minute which it would normally....
>
> > Do you have any experience with this behavior?
>
> I apologize for the formatting of the explain plan, very unhelpful.
> This is the part of plan in question:
> Operation | Rows
> -----------------------------------
> NESTED LOOPS OUTER | 1
> TABLE ACCESS FULL | 6798
> TABLE ACCESS BY INDEX ROWID | 1
>
> Best regards,
> Jan- Hide quoted text -
>
> - Show quoted text -
Jan, it does appear to be a bug in determining the cardinality on the
outer join. It looks like the CBO only expects to find one matching
key value and does not consider the fact the query wants an outer
join.
I am not an expert on the optimizer but there have been bugs in the
CBO since it came out back with version 7.0. Any opinion I could make
as to why would be pure speculation. If I had to guess I would hazard
a guess that the bug results from a side effect of the presence of
partioning which would force the CBO to consider partition pruning.
The above is unless there is an unshown filter condition that the predicate portion of the explain plan shows is applied in this step so that all 6798 rows would not be returned.
Have you verified that the statistics are current for all objects in this query>
HTH -- Mark D Powell -- Received on Wed Aug 12 2009 - 09:55:08 CDT