SORT operator

This operator represents the sorting of rows in a table.

Operator name: SORT

Represents: The sorting of the rows in a table into the order of one or more of its columns, optionally eliminating duplicate entries.

Sorting is required when no index exists that satisfies the requested ordering, or when sorting would be less expensive than an index scan. Sorting is usually performed as a final operation after the required rows are fetched, or to sort data before a join or a group by.

If the number of rows is high or if the sorted data cannot be piped, the operation requires the costly generation of temporary tables.

Performance suggestions:
  • Consider adding an index on the sort columns.
  • Ensure that you have used predicates that retrieve only the data you need. For example, ensure that the selectivity value for the predicates represents the portion of the table that you want returned.
  • Check that the prefetch size of the system temporary table space is adequate, that is, it is not I/O bound.
  • If frequent large sorts are required, consider increasing the values of the following configuration parameters:
    • Sort heap size (sortheap). To change this parameter, use the UPDATE DATABASE CONFIGURATION command.
    • Sort heap threshold (sheapthres). To change this parameter, use the UPDATE DATABASE CONFIGURATION command.
  • If statistics are not current, update them using the RUNSTATS command.