FastSort Manual

Sorting From NonStop SQL/MP
FastSort Manual—429834-003
8-4
Ordering and Grouping Query Results
Guidelines for =_SORT_DEFAULTS DEFINE Attributes
To optimize sort performance for SQL/MP, HP recommends you follow these guidelines
for SORT and SUBSORT attributes in your =_SORT_DEFAULTS DEFINE:
These attributes are fully described in Section 7, Using SORT and SUBSORT
DEFINEs. Check with your system manager to learn how resources are allocated on
your node. For more information on how to allocate sort workspace, see Section 9,
Optimizing Sort Performance.
Ordering and Grouping Query Results
This subsection describes situations in which your query causes SQL/MP to invoke
FastSort, and how to structure a query to use logic built into the SQL optimizer. For
more information about the SQL clauses mentioned in this subsection, see the
NonStop SQL/MP Query Guide.
In general, SQL/MP uses FastSort to order and group query results. SQL/MP
automatically invokes FastSort in certain cases when you specify an ORDER BY
clause in a query statement and SQL/MP retrieves data from the base table. SQL/MP
also uses FastSort when you specify one of the following in a query statement:

GROUP BY

DISTINCT
Attribute Recommended value
VLM OFF
ON for nonparallel LOAD operations
PRI 180 for high priority users and queries
160 for most users and queries
80 for routine load operations and queries
PROGRAM A local file
CPU A processor that is less than 50 to 60 percent busy
SCRATCH On a local volume other than $SYSTEM. Avoid the volume
where the SORTPROG is running. Avoid using volumes on
mirrored disks. An empty volume is best.
SWAP On a local volume other than $SYSTEM and other than the
scratch volume
Caution. For any parallel query, parallel CREATE INDEX operation, or parallel load operation,
specify only volume names for the SCRATCH and SWAP attributes in a =_SORT_DEFAULTS
DEFINE. Do not specify fully qualified file names for these attributes. If you specify fully
qualified scratch and swap file names for a parallel sort operation, processor and disk space
contention problems can result.