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

Operators and Operator Groups
HP NonStop SQL/MX Query Guide—523728-003
7-36
ORDERED_HASH_SEMI_JOIN Operator
1.1000000E+001 2.5856664E-003
8.3582334E-002 CPU_TIME: 0.001746 IO_TIME: 0.000999 MSG_TIME: 0
IDLETIME: 0 PROBES: 1
hash_join_predicate:
(indexcol(TPCDF.SF100F.CUSTOMER.C_NATIONKEY) =
indexcol(TPCDF.SF100F.NATION.N_NATIONKEY))
join_type: inner
join_method: hash
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-16.
The ORDERED_HASH_SEMI_JOIN operator has two child nodes. The description
field for this operator contains:
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 you
use to help build and probe the hash table.
Token Followed by ... Data Type
hash_join_predicate Expression of the join
predicates
expr(text)
join_type Inner, left, natural, inner semi,
or inner anti-semi-join
text
join_method Name of join method text
join_predicate Expression of the join
predicates
expr(text)
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
they change
expr(text)
selection_predicates Expression of the selection
predicates
expr(text)