SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-32
Hash Join
Parallel Hash Join
The optimizer considers a parallel hash-join strategy if CONTROL EXECUTOR
PARALLEL EXECUTION ON is specified for the query. SQL supports three types of
parallel hash joins:
•
Plain parallel
•
Repartitioned parallel hash
•
Matching partitions
Plain Parallel
SQL can execute hash joins across processors and across disk partitions. A parallel
hash join uses existing partitions and follows these steps:
1. The executor starts an ESP for each partition of the inner table.
2. The executor starts an ESP for each partition of the outer table. (The outer table
must have more than one partition.)
3. Each inner ESP reads its partition, applies selection and projection criteria, and
sends the results to the outer ESPs.
4. Each outer ESP receives a copy of the inner ESP results, stores them in a hash
structure in memory, and reads its own partition in a single pass to perform the join.
For each row, the matching rows are searched in the hash table and result rows
are generated.
Thus, a large join can be split into smaller joins and executed in parallel, and, perhaps
more importantly, the multiple processes can take advantage of a greater amount of
main memory. An inner table that might not fit into memory in a single processor might
fit into memory when divided into smaller pieces and directed to use multiple
processors
For a two-table parallel hash join, a hybrid parallel hash join is used for the parallel
hash join. For a join with three or more tables, where parallel hash join is selected for
the third table, a simple hash join is selected.
As with all parallel plans, order is not preserved after the join operation.
Note. If your system has limited process resources, the number of ESP processes might
cause a performance reduction. In such a case, if you plan to use parallel hash joins, you
might consider defining fewer partitions for the outer table so that fewer ESPs are started up.