SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-14
How Parallel Processing Is Implemented
The optimizer does not choose a nested parallel join when stable access is specified
and the tables are not partitioned exactly the same and on a single column.
The CONTROL EXECUTOR PARALLEL EXECUTION ON directive stays in effect until
the end of an SQLCI session or until the directive is specified again.
You can also specify CONTROL EXECUTOR PARALLEL EXECUTION OFF to prevent
the SQL compiler from generating an execution plan that uses multiple executors. This
option is the default.
In a host language program, specific placement rules might apply to the CONTROL
EXECUTOR directive. For more information, see the SQL/MP Programming Manual
for your host language.
For complete syntax of the CONTROL EXECUTOR directive, see the SQL/MP
Reference Manual.
How Parallel Processing Is Implemented
To implement parallel processing of queries, SQL uses an executor server process
(ESP), which uses parallel processing when executing SQL statements.
A master executor invokes several ESPs in parallel to process a statement or part of a
statement. The ESPs perform the work and return either data or status information to
the master executor. The master executor then processes that information and returns
the end result to the user.
Tables and indexes need not be physically partitioned to achieve parallel execution if
they are participating in a join query. If a table or index is not partitioned then, for the
purposes of processing the query, the data is physically broken into multiple parts
(repartitioned), and each part is processed in parallel. Parallel processing, however,
operates best when a table or index is partitioned.
Consider this query, which requests the average salary of all employees:
CONTROL EXECUTOR PARALLEL EXECUTION ON ;
SELECT AVG(SALARY) FROM EMPLOYEE ;
Because the query is processed in parallel, each partition of the EMPLOYEE table is
processed by an executor server process (ESP). Each ESP computes a partial sum
and count of the SALARY values in the rows in that partition. (The sum and count are
used by the master executor to calculate the average.) During the final processing
stage of the query, the partial results of each ESP are combined to determine the
average for the entire query.
ESPs can be reused by the same SQLCI session.
Requesting Parallel Operations on Partitioned Data
Partitioning a table and its indexes increases the likelihood that the optimizer will
choose a parallel execution plan. Partitions might reside on one system or across
many systems.