SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-58
Parallel Execution of Forced Merged Inner Join
Parallel Execution of Forced Merged Inner Join
This EXPLAIN plan chooses parallel execution for an inner join query. The merge join
method was chosen to join the tables. The query for this example is the same as the
one for Example 6-32 on page 6-49, but the CONTROL TABLE directive differs:
CONTROL TABLE * JOIN METHOD MERGE;
EXPLAIN
SELECT *
FROM TENKTUP1 A, TENKTUP2 B
WHERE A.TENPCT = B.TENPCT ;
In Example 6-32, the query is run by default and the optimizer chooses a hybrid hash
join. But in Example 6-36, a join method is forced, so the optimizer chooses a parallel
sort merge join.
Example 6-36. EXPLAIN Plan for Parallel Execution of Sort Merge Inner
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 3 1
---------------------------------------------------------------------------
Operation 1.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. : None
Table selectivity : Expect to select 100% of rows from table
Expected row count: 74666 rows after the scan
Operation cost : 2276