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

Operators and Operator Groups
HP NonStop SQL/MX Query Guide—523728-003
7-34
ORDERED_HASH_ANTI_SEMI_JOIN
ORDERED_HASH_ANTI_SEMI_JOIN
Join Group
The ORDERED_HASH_ANTI_SEMI_JOIN operator returns only one row for every
outer row when no match occurs. This operator preserves the order of the outer table
and does not overflow to disk. The reuse feature allows 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.
The ORDERED_HASH_ANTI_SEMI_JOIN operator has two child nodes. The
description field for this operator contains:
The example of the ORDERED_HASH_ANTI_SEMI_JOIN operator is based:
SELECT *
FROM customer, nation
WHERE c_custkey > 10000 AND c_custkey < 10010
AND c_nationkey NOT IN
(select n_nationkey from nation where n_regionkey = 10)
ORDER BY c_custkey
? XX
211932229593011348 9 ORDERED_HASH_ANTI_SEMI_JOIN
8 2
8.7000000E+001 1.4744397E-002 1.1843304E-001 CPU_TIME:
0.015609 IO_TIME: 0.000908 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
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
values change
expr(text)
selection_predicates Expression of the selection
predicates
expr(text)