SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-33
Hash Join
Repartitioned Parallel Hash Join
A repartitioned parallel hash join reads from existing partitions and repartitions the data
across all processors, using 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. The executor starts an ESP in each processor on the local system to join
repartitioned inner and outer rows.
4. Data from the inner table is read and repartitioned into ESP processes for each
processor in the system. The inner table (usually the smaller table) is divided into
partitions by applying a hash function to the join attributes and computing the
partition number. Each partition resides on its own processor.
5. Data from the outer table is read and repartitioned into ESP processes for each
processor in the system. It is repartitioned in the same way the inner table was.
After the outer row is repartitioned and sent to an ESP, a hash join is done.
Thus, the parallel repartitioned hybrid hash join uses three levels of hashing:
•
Dividing tables into partitions (for parallelism)
•
Dividing tables into buckets, or clusters (for overflow)
•
Optimizing access and comparison overhead (base level of hashing)
The repartitioned parallel hash join always uses a hybrid hash join strategy (as
opposed to a simple hash join). In general, the repartitioned parallel hash join is more
efficient than a parallel sort merge join because sorting is avoided.
As with all parallel plans, order is not preserved after the join operation.
Matching Partitions
A matching partitions hash join has inner and outer tables that are keyed and
partitioned the same way. This join method uses only one set of ESPs. The method
works best when most of the rows need to be scanned to perform the join. These are
the steps:
1. The executor starts an ESP for each partition of the outer table.
2. Each ESP reads the corresponding inner table partition and hashes the inner rows
into memory.
3. Each ESP reads its outer table partition sequentially, applies the selection and
projection criteria, hashes the key, and checks the inner hash table for a match.
4. Matches are passed to the next step of the query.
As with all parallel plans, order is not preserved after the join operation.