SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-56
Sort Operations
Sort Implementations
Depending upon the query request, SQL determines if a sort of the data rows is
required and automatically initiates the sort process. A logical sort can be implemented
in one of these ways:
•
An in-memory user process sort (UPS). If the optimizer estimates that the data
meets all of these requirements, the sort can be performed within the executor’s
extended segment:
°
The data to be sorted is less than 4 megabytes (MB)
°
The number of rows is less than 32,767
°
The number of sort keys is less than 63
At runtime, if the segment is discovered to be too small to hold all of the rows, SQL
invokes an external physical sort. When performed in memory, the sort does not
require startup of a sort process, nor does it use scratch files, thus reducing
elapsed time for queries.
SQL does not choose an in-memory sort if the table to be sorted is the inner table
of a sort merge join operation.
SQL can choose an in-memory sort for the serial portion of a parallel plan if all of
these conditions apply:
°
UPS is used for an ORDER BY clause executed by the master ESP
°
The ORDER BY clause is preceded by a GROUP BY clause
°
The GROUP BY order is achieved by hash grouping
•
An external physical sort. SQL can invoke these:
°
An external FastSort process if the optimizer estimates that the data to be
sorted might exceed 4 MB, the number of rows is less than 32,767, and the
number of sort keys is less than 63.
°
An external physical sort done by a series of inserts into a temporary key-
sequenced table if the number of rows is less than 500, the number of columns
is greater than 63, and the total key length is less than 255 bytes. (If the total
key length is greater than 255 bytes, the sort process returns an error.)
•
A primary-key scan
•
An alternate-index scan. For example, if an index is available and is chosen by
SQL to satisfy an ORDER BY clause, a physical sort is not necessary.
If a sort process is initiated, the =_SORT_DEFAULTS DEFINE can define the location
of swap and scratch files. (For more information on using this and other DEFINEs, see
the SQL/MP Reference Manual.)