in table tbl, there's an index idx#id#name built on id and name.
declare @id int
declare @name char(15)
select @id = 234
select @name = "DFD"
(@id = -1 or id = @id)
and ( @name = "*" or name = @name )
case 1 :Run the above statements, from the showplan, found no indexs is used, DBMS performs table scan.
case 2 :If I change the where clause, make it becomes
where (@id=-1 or id = @id) and (name = @name)
DBMS will use index idx#id#name rather table scan.
I can NOT undertand why in case 1, DBMS will not use index idx#id#name ?
in both cases, @id and @name are initialized with specific value a....
For case 2, where the index is used, I presume that ASE is using an index scan for the condition
name = @name.
ASE may obtain rows for this condition by reading all the leaf pages of the index (index scan), but who knows how will ASE interpret the boolean condition
@name = "*" or name = @name
You may always change the code by adding and IF condition:
if @id = -1
select ... where ( @name = "*" or name = @name )
select ... where id = @id and ( @name = "*" or name = @name )