Re: Execution path having full scan in a nested loop

Mohamed,
A couple of points about possible differences between your experience and this query

1. The first execution plan has already transformed the NOT IN to a NOT EXISTS - it's almost guaranteed in modern versions of Oracle unless the arithmetic is against it. Possibly in your case the critical tablescan was due to the correlation column(s) being the indexed column(s) that would otherwise have been used.
2. the LNNVL() strategy is present in both execution plans - though described very differently. Compare the old predicate 9 9 - storage("DTL"."FL_TYP"='DP') filter("DTL"."FL_TYP"='DP' AND LNNVL("DTL"."FL_NM"<>:B1) AND LNNVL(NVL(:B2,'-')<>NVL("DTL"."SID",'-')))

with the new predicate 8

   8 - access(INTERNAL_FUNCTION("RF"."fattr3")=INTERNAL_FUNCTION("FL_NM") AND INTERNAL_FUNCTION("NVL(DTL.SID,'-')")=NVL("RF"."fattr2",'-'))

   filter(INTERNAL_FUNCTION("NVL(DTL.SID,'-')")=NVL("RF"."fattr2",'-') AND   INTERNAL_FUNCTION("RF"."fattr3")=INTERNAL_FUNCTION("FL_NM"))

The INTERNAL_FUNCTION() takes the place of the LNNVL() - presumably using an "OR is null" test.

Regards
Jonathan Lewis

--
http://www.freelists.org/webpage/oracle-l