SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-60
Key-Sequenced Merge Join
The plan consists of two steps:
•
Plan step 1 includes a scan of the TENKTUP2 table, which is partitioned across
three disk volumes.
•
Plan step 2 includes a scan of the TENKTUP1 table, which is then inner joined with
the TENKTUP2 table.
Plan step 2 involves the following operations:
•
The scan.
•
Two sort operations, each using FastSort, to satisfy the merge join method. Both
sort operations are requested by the optimizer for the purpose of ordering rows
before the join. The key columns for each sort as well as the cost of each sort are
described.
•
Each operation is performed in parallel. Each ESP reads one of the partitions. The
current table is repartitioned four ways on the join column. An ESP is started to
read the repartitioned data. Each ESP starts two sorts:
°
Operation 2.1 is the first sort required to satisfy the merge join method. The
sort is requested by the optimizer to order rows in the composite table before
completing the join operation.
°
Operation 2.2 is the second sort required to satisfy the merge join method. The
sort is requested by the optimizer to sort rows in the current table (TENKTUP1)
before completing the join operation.
•
Each ESP performs a merge join of the repartitioned data to join a row resulting
from plan step 1.
Key-Sequenced Merge Join
This query chooses the key-sequenced merge join to list orders by date and provide a
count of the parts per order. The join method is forced for the purpose of this example.
Operation 2.2 : Sort
Requested : By the optimizer
Sort rows in the : 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 : A.TENPCT asc
Sort cost : 7049
Total cost : 32307
Example 6-36. EXPLAIN Plan for Parallel Execution of Sort Merge Inner
Join (page 3 of 3)