Sybase nonclustered index selection

We have a table with two nonclustered indexs. The two indexes both have the same three columns, in the same order they only differ in that one is s开发者_如何学Corted ascending the other descending. A developer had created a stored procedure that does a select where he intended to (but forgot!) to force the use of an index rather than do an Order by. When one user runs the query one index is consistently selected (ironically the correct one which masked this error for some time), when another user runs the procedure the other index is returned. What would be different between two users running the exact same procedure that would influence index selection?

(Note: this code will be rewritten, but I am trying to come to an understnading of what went on here for an After Action Report).

Thanks in Advance

You have not specified which Sybase you have. I will assume ASE.

Index selection is dependent on several factors.

Given your case, where the code has not changed, and the two users are using the same stored proc, there are two possibilities:

• check that statistics are up to date. Depending on how your DBA has automated the UPDATE STATISTICS function, and the level (either the index or table level); one index could be up to date and the other could be out of date. Unlike the ASE 12.5.4 Optimiser, the ASE 15.x Optimiser is sensitive to statistics.

• Each user is using a different set of data, Search Arguments, variables, etc that they supply as input to the same stored proc. ASE makes index choices at run time, based on (a) the exact input data (Search Arguments) vs (b) the usefulness of the indices. And all it knows it the statistics info as per the last UPDATE STATS.

Indexes are little more complex than they seem. A database system decides to use index (or not) based on the query plan, table volume, number of rows, database cache. The Database system does a cost estimation (cardinality probability, i/o estimates etc) based on query and above data.

If you have two similar indexes with different sort schemes, there is a chance that the required index key (i) is located at almost n/2 WHERE n=index size

There is also a possibility that based on the data (duplicate data / serial data) in the table, sybase doesn't care about indexes and thus can't decide which one to use.

Drop one index at a time and see what happens.