SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-26
Supporting Sort Operations
For detailed information about the CONTROL TABLE statement, including the
SEQUENTIAL BLOCKSPLIT option, see the SQL/MP Reference Manual or SQLCI
online help.
Supporting Sort Operations
For certain operations, SQL/MP requires the features of the FastSort sort/merge
program. The SQL/MP software requests these services automatically without user
interaction or input.
Specifically, SQL/MP calls FastSort during the execution of:
•
LOAD commands, when used without the SORTED option to load records into
key-sequenced target tables. Enter these commands by using SQLCI.
•
CREATE INDEX statements, when used to create indexes on existing nonempty
base tables. Enter these statements by using SQLCI or application programs.
•
Some queries that require ordering rows in an order different from that of the
primary key order or any index. Enter these queries by using SELECT statements
in SQLCI or by using cursor operations in application programs.
For an SQL SELECT, DELETE, INSERT, or UPDATE statement, you can determine if a
sort occurs by using the EXPLAIN utility, accessed either through SQLCI or the SQL
compiler. EXPLAIN reports any sort operations required to run the query.
Although you do not explicitly issue calls to FastSort when using SQL/MP, you can
influence the effectiveness of some SQL operations by using =_SORT_DEFAULTS
DEFINEs or file-partitioning techniques. In most cases, these techniques are
unnecessary; the standard FastSort parameter values are normally sufficient. When
working with large tables or indexes, however, you might need these techniques to
ensure sufficient disk space or to improve DML statement performance.
You can specify =_SORT_DEFAULTS DEFINEs through your programs at run-time, or
you can enter them by using the operating system’s command interpreter or SQLCI.
For information about =_SORT_DEFAULTS DEFINE syntax conventions, see the
SQL/MP Reference Manual. For more information about FastSort, see the FastSort
Manual.
Specifying Scratch Volumes
When processing input files, FastSort either sorts records in memory or uses one or
more scratch files to store intermediate data, as follows:
•
For files smaller than 200 KB when the MINTIME option is on, or 100 KB when the
AUTOMATIC (default) option is on, the FastSort SORTPROG process performs
the entire sort in memory.
•
For larger files, SORTPROG uses scratch files to temporarily store intermediate
data in groups of records called “runs.” SORTPROG sorts each run, merges the
records into an output file, and returns the results to SQL/MP.