By any chance are you using CURSOR_SHARING parameter in your 8i version?
I think the default selectivity of 5% is used while costing the like
operator and with the binds (and with an underscore parameter which
I think defaults TRUE) it is treated as equality .
If not you can set the underscore
Thanks, KG
No, CURSOR_SHARING is not set (meaning it defaults to
EXACT on both 8i and 9i)
No bind variables. The query given with ... LIKE
'LOVE%' is the real one
Yes, I can try setting _LIKE_WITH_BIND_AS_EQUALITY.
One additional piece of info - we don't have
OPTIMIZER_INDEX_* parameter set on
Aha, just come accross note 94051.1 explaining
_LIKE_WITH_BIND_AS_EQUALITY... but it's probably not
relevant in my case as my query doesn't make use of
bind variables. And yes I tried it (at the session
level) just for kicks with no effect on the execution
plan
Thanks anyway,
Boris Dali.