SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-62
Key-Sequenced Merge Join With Executor
Aggregates
The plan contains two steps. Step two shows that the key-sequenced merge join was
chosen.
In plan step 1, the access path is primary and the operation cost is 1.
In plan step 2, the access path is primary and the operation cost for the first operation
is 1. The second operation in step 2 includes a sort by X.ORDER_DATE and
X.ORDERNUM, both ascending. The cost of the sort is 1.
The total cost for the query is 5.
Key-Sequenced Merge Join With Executor Aggregates
This query also chooses the key-sequenced merge join but contains executor
aggregates.
EXPLAIN
SELECT COUNT(*)
FROM TENKTUP1,TENKTUP2
WHERE TENKTUP1.UNIQUE1 = TENKTUP2.UNIQUE1 ;
Executor pred. : None
Table selectivity : Expect to select 7.1429% of rows from table
Expected row count: 67 rows after the join
Operation cost : 1
Operation 2.1 : Sort
Requested : Explicitly in the query
Sort rows in the : Result of a Select
Purpose : To form groups of rows for a Group By
Sort technique : FASTSORT
Sort type : Plan to use User Process Sort
UPS workspace : 24 Kbytes
Sort key columns : X.ORDER_DATE asc, X.ORDERNUM asc
Expected row count: 67 rows after the group by
Sort cost : 1
Total cost : 5
Example 6-37. EXPLAIN Plan for Key-Sequenced Merge Join (page2of2)