Sort access method

The sort access method provides an alternative method for those queries that must process data in an ordered method (ORDER BY). An index can be used to sort the data, and is effective in some cases for implementing ordering. However, if the optimizer had to create a temporary index for that query, it would use extra processor time and resources when creating the index before the requested query can be run.

Where this method can be used

The optimizer chooses this method in the following circumstances:

• For SQL (see Effects of the ALWCPYDTA parameter on database performance) when you specify either of the following precompile options:
  • ALWCPYDTA(*OPTIMIZE), ALWBLK(*ALLREAD), and COMMIT(*CHG or *CS)
  • ALWCPYDTA(*OPTIMIZE) and COMMIT(*NONE)
• For OPNQRYF, when you specify either of the following options:
  • ALWCPYDTA(*OPTIMIZE) and COMMIT(*NO)
  • ALWCPYDTA(*OPTIMIZE) and COMMIT(*YES) and commitment control is started with a commit level of *NONE, or *CHG, or *CS
• If a temporary result is required prior to the ordering function.
• If the number of order by keys exceeds 120 or the combined length of the sort keys exceeds 2000 bytes.

How this method works

The sort algorithm reads the rows into a sort space and sorts the rows based on the specified ordering keys. The rows are then returned to the user from the ordered sort space.