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

SQL/MX Operators
HP NonStop SQL/MX Query Guide—540437-005
7-51
ORDERED_HASH_SEMI_JOIN Operator
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.
The example of the ORDERED_HASH_SEMI_JOIN operator is based on:
PREPARE TestQuery7 FROM
SELECT o_orderpriority, COUNT(*)
FROM orders
WHERE o_orderdate >= DATE '1993-07-01'
AND o_orderdate < DATE '1993-10-01'
AND EXISTS (SELECT *
FROM lineitem
WHERE l_orderkey = o_orderkey
AND l_commitdate < l_receiptdate)
GROUP BY o_orderpriority
ORDER BY o_orderpriority;
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
join_type .............. inner semi
join_method ............ hash
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)
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 WHERE clause. expr(text)