Dear List,
Is there any difference between 8i and 9i in how
selectivity of the predicates with LIKE are estimated
by CBO?
We are migrating some apps running on 8.1.7.4 on HP-UX
11.0 into 9.2.0.3 on the same box and some queries
choose completely different execution plans - HJ with
FTS vs original
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.