Skip to Content
0
Former Member
Jun 20, 2014 at 01:07 PM

Another Sybase Optimizer issue

18 Views

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