SQL and Access Paths › Sorting › Avoiding a Sort for an ORDER BY

Avoiding a Sort for an ORDER BY

DB2 can avoid a sort for an ORDER BY by using the leading columns of an index to match the ORDER BY columns. If the columns are the leading columns of the index that are used to access a single table or the leading columns of the index to access the first table in a join operation. DB2 uses order by pruning to avoid a sort.

As an example, in this query, we have an index on WORKDEPT, LASTNAME, and FIRSTNME. DB2 avoids this sort if it uses the index on those three columns to access the table.

SELECT *
FROM   EMP
WHERE  WORKDEPT = 'D01'
ORDER BY WORKDEPT, LASTNAME, FIRSTNME

DB2 can also avoid a sort in one of the following situations:

• When any number of the leading columns of an index matches the ORDER BY clause.
• When the query contains an equals predicate on the leading columns ORDER BY pruning.

In this example, the following queries avoid a sort if the index is used:

SELECT *
FROM   EMP
ORDER BY WORKDEPT

SELECT *
FROM   EMP
WHERE  WORKDEPT = 'D01'
ORDER BY WORKDEPT, LASTNAME