Mohamed,
A couple of points about possible differences between your experience and
this query
a) 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.
b) 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
