SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-61
Key-Sequenced Merge Join
CONTROL TABLE * JOIN METHOD KEY SEQUENCED MERGE;
EXPLAIN
SELECT X.ORDER_DATE, X.ORDERNUM, COUNT(*)
FROM ORDERS X,ODETAIL Y
WHERE X.ORDERNUM = Y.ORDERNUM
GROUP BY X.ORDERNUM,X.ORDER_DATE
ORDER BY X.ORDER_DATE,X.ORDERNUM ;
Example 6-37. EXPLAIN Plan for Key-Sequenced Merge Join (page1of2)
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
---------------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA8.SALES.ORDERS
with correlation name X
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 2 out of 5 columns
Access path 1 : Primary
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
Executor aggr. : Computed for each group
COUNT ( * )
Table selectivity : Expect to select 100% of rows from table
Expected row count: 13 rows after the scan
Operation cost : 1
---------------------------------------------------------------------------
Plan step 2 : Perform an Inner Join
Join strategy : Key-Sequenced Merge Join
Plan Forced : Join Method forced by user directive
Characteristic : Joins a row resulting from plan step 1
---------------------------------------------------------------------------
Operation 2.0 : Scan
Table : \SQL1.$DATA8.SALES.ODETAIL
with correlation name Y
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 1 out of 4 columns
Access path 1 : Primary
SBB for reads : Virtual
Begin key pred. : X.ORDERNUM = Y.ORDERNUM
End key pred. : None
Index selectivity : Expect to examine 7.1429% of rows from table
Index pred. : None
Base table pred. : None