Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Shanghai,China
    Posts
    76

    Unanswered: Why sybase didn't use nonclustered index?

    My table has a nonclustered index.But where i use search arguments of the indexed column.Sybase didn't use the nonclusterd index,but did a full table scan.

    Create Table Test(c1 int not null,c2 money default 0,c3 varchar(20),
    constraint PK_Test primary key(c1))
    go

    create index ind_c2_Test on Test(c2)
    go

    1> set showplan on
    2> go
    1> select * from Test where c2=200
    2> go

    QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
    The type of query is SELECT.

    FROM TABLE
    Test
    Nested iteration.
    Table Scan.
    Forward scan.
    Positioning at start of table.
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.

    c1 c2 c3
    ----------- ------------------------ --------------------

    (0 rows affected)

  2. #2
    Join Date
    Aug 2004
    Posts
    18
    What plan it uses is going to depend on the statistics it has about the data in the table. If there are only a few rows in the table, or it thinks you are going to get back a large percentage of the total number of rows, a full table scan will be more efficient than an indexed scan.

  3. #3
    Join Date
    Aug 2004
    Posts
    8

    Plan!

    dude what plan it is going to use depends entirely on the data you have.
    if the data is less , or you are getting back most of the rows of the table as a result then probably a full table scan will be better choice than other stufff!

Posting Permissions

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