SQL/MP Installation and Management Guide

Creating a Database

HP NonStop SQL/MP Installation and Management Guide—523353-004

5-49

Specifying Parallel Loading of Index Partitions

To load the partitions of a partitioned index in parallel, use the PARALLEL EXECUTION

ON option of the CREATE INDEX statement or the LOAD command. The parallel

feature loads all partitions of an index at the same time by using multiple processes in

parallel. The parallel feature does not load more than one index at the same time.

For example, this statement specifies parallel processing for loading index partitions:

>> CREATE INDEX AGEINDEX ON CUSTABLE PARALLEL EXECUTION ON

+> PARTITION ( $VOL2.CUSTOMER.AGEINDEX FIRST KEY 36 ) ;

To load the index partitions in parallel, SQL starts processes called record generators

and sort processes. The record generators read the base table; the sort processes sort

rows and write them to the index. SQL creates one record generator process for each

partition of the base table and one sort process for each partition of the index. If the

base table is not partitioned, the RDBMS creates only one record generator process

and one sort process.

If the target base table in a LOAD command has more than one partitioned index, then

the partitions of the first index are loaded in parallel. After the first index has been

loaded, the partitions of the second index are loaded in parallel, and so forth.

Performance Considerations

Although the processor cycles and disk processes used in parallel processing might be

approximately equal to the processor cycles and disk processes used in serial

(nonparallel) processing, parallel processing uses more of these cycles and processes

at the same time and might temporarily monopolize system resources.

For best performance, the disk processes for the volumes used should be distributed

evenly across all processors. If the index is partitioned, the processor for the volume's

disk process should be available for loading each partition.

By default, parallel index loading uses SORTPROG and RECGEN processes located

on the nodes where the partitions reside. When the total number of table and index

partitions nears 750, however, a load operation might stop with an SQL error 1910 and

a sort start error 10, or with an SQL error 1928, record generator error 10. When any of

these errors occur, increase the process file segment (PFS) space of the SQLCAT

process by using the BIND statement SET PFS command. Alternatively, for programs

run from TACL, you can specify the PFS size in the TACL RUN command. Save the

original copy of SQLCAT and license the new copy.