SORT

SORT

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 once the required rows are fetched, or to sort data prior to 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.

For more information on sorts, see the Administration Guide.

Performance suggestions:

• Consider adding an index on the sort columns.

  For guidelines about indexes, see Creating appropriate indexes.

• 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. (To check this, select Statement->Show statistics->Table spaces.)
• If frequent large sorts are required, consider increasing the values of the following configuration parameters:
  • Sort heap size (sortheap). To change this parameter, right-click on the database in the Control Center, and then select Configure from its pop-up menu. Select the Performance tab from the notebook that appears.
  • Sort heap threshold (sheapthres). To change this parameter, right-click on the database instance in the Control Center, and then select Configure from its pop-up menu. Select the Performance tab from the notebook that appears.
• If statistics are not current, update them using the runstats command.