Re: SQL fast, PL/SQL slow, optimizer confused.

srivenu wrote:

> Set the init.ora parameter _like_with_bind_as_equality=TRUE.
> Even Oracle APPS recommends it.
> This will cause the optimizer to favour the Index scan for LIKE
> predicates.
> Normally LIKE predicates with binds (since it is PL/SQL) will have a
> selectivity of 5%. So the optimizer may go in for FTS depending on
> other factors.
> If you set _like_with_bind_a_esquality=TRUE then the optimizer will
> evaluate the selectivity of the LIKE predicate the same as a EQUALITY
> predicate.
> (The selectivity of the clause c1 = :bind1 (Num of Rows - Num of Nulls
> in column c1) / (Number of Distinct Values).)
>
> regards
> Srivenu

Thanks!

Bjørn Received on Mon Mar 01 2004 - 15:29:30 CST