Unbelievable Outer Join

From: Jan <noreply.jan_at_gmail.com>
Date: Wed, 12 Aug 2009 07:16:42 -0700 (PDT)
Message-ID: <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.

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? Received on Wed Aug 12 2009 - 09:16:42 CDT