selectivity of predicates with LIKE - diff between 8i and 9i

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

RE: selectivity of predicates with LIKE - diff between 8i and 9i

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

RE: selectivity of predicates with LIKE - diff between 8i and 9i

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

Re: selectivity of predicates with LIKE - diff between 8i and

So far I have not observed a difference in selectivity of predicates between 8i and 9i. Is there something special about the table or index? I tried to create a mock table with your statistics and the 9i optimizer came up with the same cardinality and cost estimates as 8i. Can you post (or

RE: selectivity of predicates with LIKE - diff between 8i and 9i

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.