SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
P-5
Parallel Index Loading
Parallel Index Loading
If you create an index on a base table that already contains data, SQL automatically
loads the index file with data from the base table. If the index is partitioned, you can
specify the PARALLEL EXECUTION option on the CREATE INDEX statement to direct
SQL to load partitions of the index in parallel. A similar clause on the LOAD command
serves the same function there.
When you execute CREATE INDEX or LOAD with the PARALLEL EXECUTION ON
option and the table being indexed or loaded is not empty, SQL starts a record
generator (RECGEN) process for each partition of the table and a sort process
(SORTPROG) for each partition of the index. Record generator processes read the
base table. Sort processes sort the rows and write them to the index.
The default location for the record generator program file is
$SYSTEM.SYSnn.RECGEN. You can specify a different location with the
=_SQL_RECGEN_node DEFINE.
Parallel processing uses more processor cycles and disk processes at the same time
than serial (nonparallel) processing, and thus might temporarily monopolize system
resources.
For best performance, the disk processes for the volumes used should be distributed
evenly across all processors.
Specifying certain attributes of the =_SORT_DEFAULTS DEFINE can cause problems
with multiple sort processes. For more information, see the FastSort Manual.
Default Configuration for Parallel Index Loading
You can use the PARALLEL EXECUTION clause to specify a configuration file that
describes attributes of record generator and sort processes. If you specify the
PARALLEL EXECUTION clause but do not specify a configuration file, SQL uses these
defaults:

Priority—Record generators and sort processes use the same execution priority as
the process that creates the index.

processor—If the partition is local, the record generator or sort process runs in the
same processor that runs the primary disk process for the partition's disk. If that
processor is not available or the partition is remote, the processor is chosen
arbitrarily and then in a sequential, circular fashion. (Note that more than one
record generator or sort process might run in the same processor.)

Scratch file—By default, the sort process determines a volume for the scratch file.
Record generators do not have scratch files.

Number of records (sort scratch file size)—SQL estimates the number of records
that each sort process reads as three times the estimated total number of records
in the base table divided by the number of partitions in the index: