Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006
    Posts
    13

    Unanswered: Strange behaviour about index

    in table tbl, there's an index idx#id#name built on id and name.

    ////////////////////statements////////////////////////
    declare @id int
    declare @name char(15)

    select @id = 234
    select @name = "DFD"

    select
    id,name
    from
    tbl
    where
    (@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....

  2. #2
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    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 )
    else
    select ... where id = @id and ( @name = "*" or name = @name )

    Regards,
    Mariano Corral

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •