Re: Unbelievable Outer Join

"Jan" <noreply.jan_at_gmail.com> wrote in message news:aa34b9b7-44f9-4460-bd4f-12da5168125c_at_q23g2000yqn.googlegroups.com... On 12 srp, 23:22, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
This is the explain plan again:

|                          |     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

Apologies,

I've just realised I've been completely misinterpreting your question and answering a question you weren't asking.

To address the join cardinality of one:

I note you have a filter operation above the join.

It's hard to tell from the mangled plan, but if this is a filter subquery, with a select nvl() in it, then there is a bug in the optimizer that evaluates the selectivity of the subquery as (1/(100*NDV)) - and then, if I recall correctly - applies it early, which means you always get a cardinality of 1.

I think the bug is present in 10.2 as a side effect of a fix to the 9i optimizer.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html