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

SQL/MX Operators
HP NonStop SQL/MX Query Guide—540437-005
7-49
ORDERED_HASH_JOIN Operator
( SELECT ps_suppkey from partsupp)
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 anti-semi
join_method ............ hash
hash_join_predicate .... (DETCAT.DETSCH.SX1.S_SUPPKEY =
DETCAT.DETSCH.PSX1.PS_SUPPKEY)
ORDERED_HASH_JOIN Operator
Join Group
The ORDERED_HASH_JOIN operator joins the data from two child tables. This
operator preserves the order of the outer table and does not overflow to disk. It creates
a hash table from the inner table, joins the outer table by hashing each outer row, and
looks for matches in the hash table. 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.
Equijoins and cross-products are supported by this operator.
The ORDERED_HASH_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
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)