SQL/MX 2.x Query Guide (H06.04+, J06.03+)

SQL/MX Operators
HP NonStop SQL/MX Query Guide—540437-005
7-26
HYBRID_HASH_SEMI_JOIN Operator
AND ps_supplycost = (SELECT MIN(ps_supplycost)
FROM partsupp, supplier, nation, region
WHERE p_partkey = ps_partkey
AND s_suppkey = ps_suppkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'EUROPE')
ORDER BY s_acctbal desc, n_name, s_name, p_partkey;
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
join_type .............. inner
join_method ............ hash
hash_join_predicate .... (DETCAT.DETSCH.NATION.N_REGIONKEY =
DETCAT.DETSCH.REGION.R_REGIONKEY)
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-25.
The HYBRID_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 fragment 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: hash text
hash_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 WHERE clause. expr(text)