SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-50
Parallel Execution of Hash Join
---------------------------------------------------------------------------
Plan step 2 : Perform an Inner Join
Join strategy : Hybrid Hash Join
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 1 2
Current table and join composite (excluding current table)
will each be repartitioned 4 ways on the join column to:
\SQL1.$DATA7 \SQL1.$DATA4 \SQL1.$DATA6 \SQL1.$DATA3
4 ESP's will be started to read the repartitioned data
The ESP's will be started in the cpu's numbered
2 0 3 1
Each ESP will:
Hash one repartition of the join composite excluding the current table
Hash one repartition of the current table
Each ESP will perform a Hybrid Hash Join (repartitioned)
Characteristic : Joins a row resulting from plan step 1
---------------------------------------------------------------------------
Operation 2.0 : Scan
Table : \SQL1.$DATA2.WISCREG.TENKTUP2
with correlation name B
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. : On hashed rows
A.TENPCT = B.TENPCT
Pred. selectivity : Expect to select 0.0131% of rows from table
Table selectivity : Expect to select 0.0131% of rows from table
Expected row count: 733929 rows after the join
Operation cost : 2276
Example 6-32. EXPLAIN Plan for Hash Join (page 2 of 3)