SQL/MX 3.2 Query Guide (H06.25+, J06.14+)

SQL/MX Operators
HP NonStop SQL/MX Release 3.2 Query Guide—663851-002
7-39
LEFT_ORDERED_HASH_JOIN Operator
LEFT_ORDERED_HASH_JOIN Operator
Join Group
The LEFT_ORDERED_HASH_JOIN operator returns an unmatched outer row even
when it does not find a match in the inner table. Null values are supplied for the
missing inner rows. The LEFT_ORDERED_HASH_JOIN operator differs from the
LEFT_HYBRID_HASH_JOIN in that it preserves the order of the outer table and does
not overflow to disk. In addition, 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.
The LEFT_ORDERED_HASH_JOIN operator has two child nodes. The description
field for this operator contains the following:
The following is an example of the LEFT_ORDERED_HASH_JOIN operator:
Control Query Default nested_joins 'off';
prepare TestQuery37a from
SELECT *
FROM customer LEFT JOIN nation ON c_nationkey = n_nationkey
WHERE c_custkey > 1000 AND c_custkey < 1010
ORDER BY c_custkey;
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 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)
TP663851.fm Page 39 Thursday, August 2, 2012 4:47 PM