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
)
go
alter table TEST_TABLE add constraint XPK_TEST_TABLE_ID1 primary key (ID1)
go
Next query has to be performed on this table:
select ID1, ID2, ID3, NAME from TEST_TABLE order by ID1, ID2, ID3, NAME
go
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)
go
After this, when query is executed, it can be seen in execution plan that optimizer uses this index.
