Avoiding or Simplifying Sort Operations

Sorting is not necessarily a liability. The sort algorithm is highly tuned and extremely efficient. It is as fast as any external sort program that you might apply to the same data. You need not avoid infrequent sorts or sorts of relatively small numbers of output rows.

Try to avoid or reduce the scope of repeated sorts of large tables. The optimizer avoids a sort step whenever it can use an index to produce the output in its proper order automatically. The following factors prevent the optimizer from using an index:

• One or more of the ordered columns is not included in the index.
• The columns are named in a different sequence in the index and the ORDER BY or GROUP BY clause.
• The ordered columns are taken from different tables.

For another way to avoid sorts, see Using Temporary Tables to Reduce Sorting Scope.

If a sort is necessary, look for ways to simplify it. As discussed in Sort-Time Costs,, the sort is quicker if you can sort on fewer or narrower columns.