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

SQL/MX Operators
HP NonStop SQL/MX Query Guide—540437-005
7-50
ORDERED_HASH_SEMI_JOIN Operator
The difference between join_predicate and hash_join_predicate tokens is that the
former are the non-equijoin predicates, while the latter are equijoin predicates that are
used to help build and probe the hash table.
The example of the ORDERED_HASH_JOIN operator is based on:
PREPARE TestQuery9 FROM
SELECT s_nationkey, s_suppkey
FROM supplier, partsupp
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-25.
parallel_join_type Type1 or Type2, depending on parallel join
algorithm.
text
reuse_comparison_values List of values that cause the hash table to be
rebuilt when values change
.
expr(text)
selection_predicates Expression of the WHERE clause. expr(text)