Re: Unbelievable Outer Join

From: Ruud de Koter <nobody_at_internet.org>
Date: Wed, 12 Aug 2009 18:19:51 +0200
Message-ID: <4a82eb9a$0$182$e4fe514c_at_news.xs4all.nl>

Hi Jan,

> 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

Did it occur to you that the join syntax you are using is not the ANSI syntax recommended by Oracle? I wouldn't be surprised at all if the CBO were to do this correctly when you 'd be using this newer syntax.

I 'd say: try rewriting the statement using the ANSI syntax and see what that brings.

Good luck,

Ruud de Koter Received on Wed Aug 12 2009 - 11:19:51 CDT