Avoid or simplify sort operations

In many situations you can determine how to avoid or reduce frequent or complex sort operations.

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 do not need to avoid infrequent sorts or sorts of relatively small numbers of output rows.

However, you should 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 Use 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.


Copyright© 2018 HCL Technologies Limited