FastSort Manual

FastSort Manual—429834-003
8-1
8 Sorting From NonStop SQL/MP
Under certain circumstances, SQL/MP invokes FastSort in a manner that is
transparent to the user. SQL/MP invokes FastSort when you do any of the following:

Specify ordering or grouping options in an SQL query statement

Execute a query that results in a sort merge join operation

Use a CREATE INDEX or LOAD statement to load data in parallel
Because SQL/MP automatically invokes FastSort, this section describes how a sort
operation is implemented. This section also contains guidelines on how to minimize
SQL sorts and configure your FastSort environment.
How SQL/MP Implements a Sort
The SQL optimizer analyzes each SQL statement and determines if a sort is needed. If
needed, SQL/MP implements the sort in one of two ways.
In-memory Sorts
An in-memory sort is the fastest type of sort operation because it requires no
SORTPROG process or scratch files. FastSort can sort records within the executor’s
extended memory segment if all of the following conditions apply:

The data to sort is less than 4 MB

The number of rows to sort is less than 32,768

The number of columns to sort is less than 63
For the serial portion of a parallel plan, the optimizer can also choose an in-memory
sort if all of the following conditions apply:

The master executor server process (ESP) uses an in-memory sort to execute an
ORDER BY clause.

A GROUP BY clause precedes the ORDER BY clause.

SQL/MP uses hash grouping to execute the GROUP BY clause.
External Physical Sorts
SQL/MP uses an external physical sort if the memory segment is too small to hold all
of the rows. An external physical sort is one of the following:
Note. The optimizer does not choose an in-memory sort if the table to sort is the inner table of
a sort merge join operation.