FastSort Manual

Sorting From NonStop SQL/MP
FastSort Manual—429834-003
8-16
Configuring a LOAD Statement
When you execute a LOAD statement from SQLCI, you invoke FastSort if data is
unsorted and the target table is key-sequenced, or if PARALLEL EXECUTION is set to
ON. This subsection discusses only the LOAD options that affect sort operations. For a
full description of LOAD statement syntax, see the SQL/MP Reference Manual.
The LOAD options that affect sort operations are:

SORTED

MAX

SCRATCH
These options are only valid for loading key-sequenced files and tables.
SORTED
specifies that input records are already sorted in the key-field order of the output
file and are not to be resorted. If you omit the SORTED option and the target file is
key-sequenced, FastSort sorts the records before LOAD writes data to the output
file.
MAX num-records
specifies the number of input records. The range is between 0 and 2,147,483,647.
LOAD uses num-records to determine file and extent size for the initial scratch
file. If you specify the SORTED option, you can omit the MAX option.
When you specify num-records, try to overestimate. If you underestimate the
number of records, the sort can be significantly slower. If you overestimate, the
cost is small.
The default value for MAX is 50,000 records unless a =_SORT_DEFAULTS
DEFINE with VLM ON is in effect. When VLM is on, the default is 1,000,000
records. For more information about that option, see Using VLM
on page 9-10.
MAX is not valid for loading indexes. When you load an index, LOAD uses the size
of the base table to estimate the number of input records and ignores any value
you specify for MAX.
SCRATCH scratch-file
identifies an initial scratch file or volume. For nonparallel load operations, specify
the name of either a disk file or volume for scratch-file. For parallel load
operations, specify only a volume name.
If you omit the SCRATCH option, FastSort creates an initial scratch file on a
suitable volume unless a =_SORT_DEFAULTS DEFINE that specifies a different
initial scratch file or volume is in effect.
When loading a large table, you can use a partitioned scratch file to manage
scratch space. Use the FUP CREATE command to create the partitioned file. Then
specify the file to FastSort in the SCRATCH option or your =_SORT_DEFAULTS