Table scan chosen over index if search parameter is over 32 characters long
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);A query on the "str" string column normally results in a fast index range scan:
alter table atest add constraint pk_atest primary key (pk);
alter table atest add constraint uq_atest unique (str, fk);
2