SQL/MX 3.2 Query Guide (H06.25+, J06.14+)

SQL/MX Operators
HP NonStop SQL/MX Release 3.2 Query Guide—663851-002
7-43
MERGE_JOIN Operator
The MERGE_JOIN operator has two child nodes. The description field for this operator
contains the following:
The following is an example of the MERGE_JOIN operator:
PREPARE TestQuery10 FROM
SELECT l_orderkey,
CAST(SUM(l_extendedprice*(1-l_discount))AS
NUMERIC(18,2)), o_orderdate, o_shippriority
FROM customer,orders,lineitem
WHERE c_mktsegment = 'BUILDING'
AND c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate < DATE '1995-03-15'
AND l_shipdate > DATE '1995-03-15'
GROUP BY l_orderkey, o_orderdate, o_shippriority
ORDER BY 2 DESC,3 ASC;
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
join_type .............. inner
join_method ............ merge
merge_join_predicate ... (DETCAT.DETSCH.SUPPLIER.S_SUPPKEY =
DETCAT.DETSCH.PSX1.PS_SUPPKEY)
selection_predicates ... (DETCAT.DETSCH.PSX1.PS_SUPPLYCOST <
DETCAT.DETSCH.SUPPLIER.S_ACCTBAL)
Token Followed by ... Data Type
fragment_id A sequential number assigned to the fragment. 0
is always the master executor and 1 is reserved
for the EXPLAIN plan. Numbers 2 to n will be
ESP or DAM fragments.
integer
parent_frag The fragment_id for the parent of the current
fragment. The value is (none) for the master
executor.
integer
fragment_type Master, ESP, or DP2. text
join_type Inner, left, natural, inner semi, or inner anti-semi-
join.
text
join_method Name of join method: merge text
merge_join_predicate Expression of the join predicate. expr(text)
parallel_join_type Type1 or Type2, depending on parallel join
algorithm.
text
selection_predicates Expression of the HAVING clause. expr(text)
TP663851.fm Page 43 Thursday, August 2, 2012 4:47 PM