Re: Unbelievable Outer Join

From: Jan <noreply.jan_at_gmail.com>
Date: Wed, 12 Aug 2009 07:25:47 -0700 (PDT)
Message-ID: <013e8fc9-ca9d-4c49-ab24-8f0649008cb2_at_26g2000yqk.googlegroups.com>

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 Received on Wed Aug 12 2009 - 09:25:47 CDT