SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Operators and Operator Groups
HP NonStop SQL/MX Query Guide—523728-003
7-17
HYBRID_HASH_SEMI_JOIN Operator
GROUP BY n_name
ORDER BY revenue desc;
? XX
211862597542574407 13 HYBRID_HASH_JOIN
1 2 1 0
1.1108888E+001 2.4975137E-003 1.8145949E+000
CPU_TIME: 0.002874 IO_TIME: 0 MSG_TIME: 0 IDLETIME: 0
PROBES: 1
hash_join_predicate:
(indexcol(\TESTSYS.$DATA14.SPTPCD.NATION.N_REGIONKEY) =
indexcol(\TESTSYS.$DATA14.SPTPCD.REGION.R_REGIONKEY))
join_type: inner
join_method: hash
HYBRID_HASH_SEMI_JOIN Operator
Join Group
The HYBRID_HASH_SEMI_JOIN returns only one row for every outer row, regardless
of the number of matches. The HYBRID_HASH_SEMI_JOIN operator differs from the
HYBRID_HASH_JOIN operator only when it finds multiple matches in the inner table.
In the HYBRID_HASH_JOIN case, a result row is returned for every match in the inner
table. See HYBRID_HASH_JOIN Operator on page 7-16.
The HYBRID_HASH_SEMI_JOIN operator has two child nodes. The description field
for this operator contains:
The example of the HYBRID_HASH_SEMI_JOIN operator is based on:
SELECT o_orderpriority, COUNT(*)
FROM $big18a.tpcd2x.orders
WHERE o_orderdate >= DATE '1993-07-01'
AND o_orderdate < DATE '1993-10-01'
AND EXISTS (SELECT *
FROM $big18a.tpcd2x.lineitem
WHERE l_orderkey = o_orderkey
AND l_commitdate < l_receiptdate)
Token Followed by ... Data Type
join_type Inner, left, natural, inner semi,
or inner anti-semi-join
text
join_method Name of join method text
hash_join_predicate Expression of the join
predicates
expr(text)
parallel_join_type Type1 or Type2, depending
on parallel join algorithm
text
selection_predicates Expression of the selection
predicates
expr(text)