SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-57
Parallel Execution of Nested Inner Join
The plan contains the following information:
•
Both steps are performed in parallel. For a plan involving parallel execution, each
partition is read by an ESP in a separate processor. The EXPLAIN plan states
which partitions will be read by each of the ESPs and in which processors.
•
Step 2 indicates that the join was forced by user directive. In practice, this directive
should be used with caution. To force a join method, use the CONTROL TABLE
directive. For more information, see Specifying a Join Method on page 3-43.
Without this directive, SQL would choose the hash join method for this query; its
cost, listed in Example 6-32 on page 6-49, is lower than that of the nested join.
If the total cost for the nested inner join seems large in comparison to the operation
costs for operations 1.0 and 2.0, the reason is that SQL calculates the total cost by first
multiplying the cost of the scan by the number of probes (outer composite expected
row counts). Note that this amount is then reduced to reflect the benefit of using cache.
---------------------------------------------------------------------------
Plan step 2 : Perform an Inner Join
Join strategy : Nested Join
Plan Forced : Join Method forced by user directive
Each operation is performed in parallel for this step
Each ESP from previous step will join one of the following partitions:
\SQL1.$DATA3 \SQL1.$DATA4 \SQL1.$DATA2
Each ESP will perform a Nested Join (parallel access)
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 : Not used
Begin key pred. : None
End key pred. : None
Index selectivity : Expect to examine 100% of rows from table
Index pred. : None
Base table pred. : Will be evaluated by the disk process
A.TENPCT = B.TENPCT
Pred. selectivity : Expect to select 0.0131% of rows from table
Executor pred. : None
Table selectivity : Expect to select 0.0131% of rows from table
Expected row count: 733929 rows after the join
Operation cost : 6297
Total cost : 56658817
Example 6-35. EXPLAIN Plan for Nested Inner Join (page 2 of 2)