Another Sybase Optimizer issue

[Former Member]

Run below code to create tables.

drop table mytab_1
go
create table mytab_1(col1 int identity not null primary key,col2 int not null,col3 char(200) not null,col4 date not null,col5 int null)
lock datarows
go
insert into mytab_1(col2,col3,col4)
select top 20000
convert(int,5000) ,name + ': name',dateadd(dd,-1*colid,getdate())
from syscolumns
go 10
insert into mytab_1(col2,col3,col4,col5)
select top 20000
convert(int,5000) ,name + ': name',dateadd(dd,-1*colid,getdate()),1
from syscolumns
go

create nonclustered index idx_col1_1 on mytab_1(col4)
go
create nonclustered index idx_col1_2 on mytab_1(col2)
go
create nonclustered index idx_col1_5 on mytab_1(col5,col2)
go

update index statistics mytab_1
go

Look at the plan of below statement and see why sybase optimizer made this choice. It is bad on Sybase part. It should not have made this choice .

It is happening for datarows locking and is not happening on allpages. Too many null values make optimizer thinks that there needs to be really less number of IO's in picking up the index idx_col1_5.

set showplan on
set statistics io,time,plancost on
go

select * from mytab_1 where col2 = 5000 and col4 = '06/18/2014'

go

Sybase ase 15.0.3

[simon_ogden]

This is an unfortunate 'feature' of the ASE 15 optimizer. In circumstances where the costings come back as the same (or as in this case cheaper for the full index scan)  it can sometimes select a non-filtering index scan as oppose to a filtering one.

You can disable this behaviour by setting setting full_index_filter to 1.

set full_index_filter 1

or set mnc_full_index_filter to 3 at server level

sp_configure mnc_full_index_filter, 3

[Former Member]

In my case actually leading column has all null values and thus I dropped the index as it wasn't making any sense and it was more of a design issue.

Thanks