Database Tuning (MOSC)

MOSC Banner

Table scan chosen over index if search parameter is over 32 characters long

edited Jul 2, 2012 6:08AM in Database Tuning (MOSC) 5 commentsAnswered
I have been trying to understand a sporadic problem in which the optimizer sometimes chooses a scan over an index seek when the search parameter is a long string (over 32 characters).  The behavior has appeared in 9i and 11g, using literal strings and bind variables for the search.  Our table is basically like this:
create table atest (pk number(20) not null, fk number(10) not null, str varchar2(64) not null);
alter table atest add constraint pk_atest primary key (pk);
alter table atest add constraint uq_atest unique (str, fk);
A query on the "str" string column normally results in a fast index range scan:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center