When Adaptive Server optimizes queries that require sorts:
It computes the cost of using an index that matches the required sort order, if such an index exists.
It computes the physical and logical I/O cost of creating a worktable and performing the sort for every index where the index order does not match the sort order. It computes the physical and logical I/O cost of performing a table scan, creating a worktable, and performing the sort.
Adding the cost of creating and sorting the worktable to the cost of index access and the cost of creating and sorting the worktable favors the use of an index that supports the order by clause. However, when comparing indexes that are very selective, but not ordered, versus indexes that are ordered, but not selective:
Access costs are low for the more selective index, and so are sort costs.
Access costs are high for the less selective index, and may exceed the cost of access using the more selective index and sort.
