Case study 8: Index and order by clause
  • Platform: HP-UNIX 11, Tru64 , Windows
  • Sybase ASE version: 12.5.x
  • Background story: In some views, we've been using some temporary tables to do the data sorting. Because these views were slow we had to analyze them and we concluded that index created on temporay table is not used.
  • Task: Find out why index is not used and create index which will be used by query optimizer
  • Problem description: For educational purposes I will use example table:

    create table TEST_TABLE
    ID1 integer NOT NULL,
    ID2 integer NOT NULL,
    ID3 integer NOT NULL,
    ID4 integer NOT NULL,
    NAME varchar(100) NOT NULL

    alter table TEST_TABLE add constraint XPK_TEST_TABLE_ID1 primary key (ID1)

    Next query has to be performed on this table:

    select ID1, ID2, ID3, NAME from TEST_TABLE order by ID1, ID2, ID3, NAME

    By examining execution plan it can be concluded that no indexes is used in this query, although there is unique index on ID1 column, and query optimizer use full table scan.
  • Solution: I've found solution on Sybase site, and this is the part which solves the problem:

    "To use this optimization, the columns in the order by clause must match the index order. They can be a subset of the keys, but must be a prefix subset, that is, they must include the first key(s). The descending scan optimization cannot be used if the columns named in the order by clause are a superset of the index keys."

    So, for this example, following index has to be created:

    create nonclustered index INDX_TEST_TABLE on TEST_TABLE(ID1, ID2, ID3, NAME)

    After this, when query is executed, it can be seen in execution plan that optimizer uses this index.

Locations of visitors to this page
  powered by myself. DBA-Sybase 2006