biff,
The default selectivity used by the CBO for like predicates with bind
variables is high which is causing a full table scan in your case.
The default selectivity used by the CBO, for the clause "I.COL2 LIKE
:LastName" is 5% and the optimizer may not use the index depending on
several other factors like other predicates in the query, clustering
factor of the index etc.
To reduce the selectivity of this set the parameter
"_like_with_bind_as_equality"=TRUE.
This will cause the CBO to estimate the selectivity of the clause
"I.COL2 LIKE :LastName" the same as the clause "I.COL2 = :LastName".
The selectivity of the clause "I.COL2 = :LastName" is calculated by
the formulae
1/(Number of Distinct Values for I.COL2).
So try setting "_like_with_bind_as_equality"=TRUE and see the result.
regards
Srivenu
Received on Wed Dec 31 2003 - 01:14:29 CST
