SQL/MX 3.1 Query Guide (H06.23+, J06.12+)

SQL/MX Operators
HP NonStop SQL/MX Release 3.1 Query Guide—663851-001
7-53
ORDERED_HASH_SEMI_JOIN Operator
WHERE s_suppkey = ps_suppkey
GROUP BY s_nationkey, s_suppkey
ORDER BY s_nationkey, s_suppkey;
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
join_type .............. inner
join_method ............ hash
hash_join_predicate .... (DETCAT.DETSCH.SX1.S_SUPPKEY =
DETCAT.DETSCH.PSX1.PS_SUPPKEY)
ORDERED_HASH_SEMI_JOIN Operator
Join Group
The ORDERED_HASH_SEMI_JOIN operator returns the outer rows for all matches.
This operator differs from the HYBRID_HASH_SEMI_JOIN operator in that it preserves
the order of the outer table and does not overflow to disk. The reuse feature enables
reuse of the hash table for subsequent requests within the same query. Choose this
operator when you need to preserve the order of the outer table or if you can benefit
from the reuse feature. It should be chosen only if the inner table is small enough to fit
in memory.
Also see HYBRID_HASH_JOIN Operator on page 7-27.
The ORDERED_HASH_SEMI_JOIN operator has two child nodes. The description
field for this operator contains:
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
hash_join_predicate Expression of the join predicate. expr(text)
join_type Inner, left, natural, inner semi, or inner anti-
semi-join.
text
join_method Name of join method: hash text
join_predicate Expression of the join predicate. expr(text)
TP663851.fm Page 53 Monday, October 17, 2011 11:48 AM