SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-49
Parallel Execution of Hash Join
For more information on ESPs, see Processor Assignment by the SQL/MP Optimizer
and Executor for Executor Server Processes (ESPs) on page 2-5.
Parallel Execution of Hash Join
The EXPLAIN plan in Example 6-32 chooses parallel execution for a join operation.
SQL chooses the hash join method to join the tables. The hash join method is often
chosen when joining a large table and a much smaller table. The query follows:
EXPLAIN
SELECT *
FROM TENKTUP1 A, TENKTUP2 B
WHERE A.TENPCT = B.TENPCT ;
The query involves a scan of two tables, TENKTUP1 (correlation name A) and
TENKTUP2 (correlation name B), which are joined according to the following search
condition: WHERE A.TENPCT = B.TENPCT.
.
Example 6-32. EXPLAIN Plan for Hash Join (page 1 of 3)
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1 : Will utilize parallel execution
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
Each operation is performed in parallel for this step
Each ESP will read one of the following partitions:
\SQL1.$DATA3 \SQL1.$DATA4 \SQL1.$DATA2
The ESP's will be started in the cpu's numbered
0 2 3
---------------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA2.WISCREG.TENKTUP1
with correlation name A
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 16 out of 16 columns
Access path 1 : Primary, partitioned, sequential cache
SBB for reads : Virtual
Begin key pred. : None
End key pred. : None
Index selectivity : Expect to examine 100% of rows from table
Index pred. : None
Base table pred. : None
Executor pred. : None
Table selectivity : Expect to select 100% of rows from table
Expected row count: 74999 rows after the scan
Operation cost : 2279