SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-59
Parallel Execution of Forced Merged Inner Join
---------------------------------------------------------------------------
Plan step 2 : Perform an Inner Join
Join strategy : Merge Join
Plan Forced : Join Method forced by user directive
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.$D30SYS \SQL1.$DATA10 \SQL1.$DSSQA1 \SQL1.$DATA9
4 ESP's will be started to read the repartitioned data
The ESP's will be started in the cpu's numbered
0 2 3 1
Each ESP will start 2 sorts to:
Sort one repartition of the join composite excluding the current table
Sort one repartition of the current table
Each ESP will perform a Merge Join (repartitioned)
Characteristic : Joins a row resulting from plan step 1
---------------------------------------------------------------------------
Operation 2.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. : On sorted 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
Operation 2.1 : Sort
Requested : By the optimizer
Sort rows in the : Join composite excluding current table
Purpose : To order its rows before the Join
Sort technique : FASTSORT
Sort type : Insertion into an entry-sequenced disk file
Sort key columns : B.TENPCT asc
Sort cost : 7049
Example 6-36. EXPLAIN Plan for Parallel Execution of Sort Merge Inner
Join (page 2 of 3)