Dropping indexes that were created to avoid sorts

Indexes that are defined only to avoid a sort for queries with an ORDER BY clause are unnecessary if Db2 can perform a backward scan of another index to avoid the sort.

About this task

In earlier versions of Db2, you might have created ascending and descending versions of the same index for the sole purpose of avoiding a sort operation.

Procedure

To recover the space that is used by these indexes:

Drop indexes that were created to avoid sorts.

For example, consider the following query:

SELECT C1, C2, C3 FROM T
   WHERE C1 > 1
   ORDER BY C1 DESC;

Having an ascending index on C1 would not have prevented a sort to order the data. To avoid the sort, you needed a descending index on C1. Db2 can scan an index either forwards or backwards, which can eliminate the need to have indexes with the same columns but with different ascending and descending characteristics.

For Db2 to be able to scan an index backwards, the index must be defined on the same columns as the ORDER BY and the ordering must be exactly opposite of what is requested in the ORDER BY. For example, if an index is defined as C1 DESC, C2 ASC,Db2 can use:

A forward scan of the index for ORDER BY C1 DESC, C2 ASC
A backward scan of the index for ORDER BY C1 ASC, C2 DESC

However, Db2 does need to sort for either of the following ORDER BY clauses:

ORDER BY C1 ASC, C2 ASC
ORDER BY C1 DESC, C2 DESC