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

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_as_equality=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 Received on Fri Feb 27 2004 - 06:48:21 CST