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 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)
SQL/MX Operators
HP NonStop SQL/MX Query Guide —640323-001 7- 38 MATERIALIZE Operator DESCRIPTION fragment_id ... 0 parent_frag ... (none) fragment_type ... master join_type ... left join_method ... hash hash_join_predicate .... (DETCAT.DETSCH.CUSTOMER.C_NATIONKEY = DETCAT.DETSCH.NATION.N_NATIONKEY)
MATERIALIZE Operator
Materialize Group
When it first executes, the MATERIALIZE operator evaluates the query beneath it one time and stores the result of that evaluation in a temporary table, in addition to
returning the result to the parent. In subsequent requests to the MATERIALIZE
operator, it might return the stored temporary table instead of evaluating its child again. Use the MATERIALIZE operator when using correlated subqueries or in place of a HYBRID_HASH_JOIN when the outer order needs to be retained.
The MATERIALIZE node is not used by default starting from SQL/MX Release 2.x. If needed, enable it by setting the MATERIALIZE default to ON. Starting from SQL/MX Release 2.x, SQL/MX uses the ORDERED_HASH_JOIN Operator to replace the functionality of the MATERIALIZE node.
The MATERIALIZE operator has one child node. The description field for this operator contains:
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
operation_type Hash table. text
values_given_to_child Values whose change causes materialization of the table.
expr(text)
temp_table_key Key for the temporary table. expr(text)
begin_key Begin key predicate. expr(text)
end_key End key predicate. expr(text)
scan_direction Direction in which table is scanned: forward or reverse.
text
check_input_values Expression used to check if input values have changed.
SQL/MX Operators
HP NonStop SQL/MX Query Guide —640323-001 7- 39
MERGE_ANTI_SEMI_JOIN Operator
The following is an example of the MATERIALIZE operator:
control query default materialize 'on'; control query shape
sort_groupby(nested_join(sort(partition_access( scan(path 'TAB1', forward, mdam off))),materialize(
partition_access(scan(path 'TAB2', forward, mdam off))))); prepare TestQuery30 from
select TAB1.col1, TAB1.col2, sum(TAB2.col2), count(*) from TAB1 , TAB2
where TAB1.col1 = TAB2.col1 and TAB2.col2 < 30
group by TAB1.col1, TAB1.col2 order by TAB1.col2; DESCRIPTION fragment_id ... 0 parent_frag ... (none) fragment_type ... master operation_type ... hash scan_direction ... forward values_given_to_child execution_count temp_table_key ... DETCAT.DETSCH.TAB2.COL1 begin_key ... (DETCAT.DETSCH.TAB2.COL1 = DETCAT.DETSCH.TAB1.COL1) check_input_values ... (execution_count = convert(execution_count))
MERGE_ANTI_SEMI_JOIN Operator
Join Group
The MERGE_ANTI_SEMI_JOIN operator returns rows only when no match occurs in the inner table. The operator discards all rows that have a match. Also see
MERGE_JOIN Operator on page 7-40 and MERGE_SEMI_JOIN Operator on page 7-42.
The MERGE_ANTI_SEMI_JOIN operator has two child nodes. The description field for this operator contains:
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.
SQL/MX Operators
HP NonStop SQL/MX Query Guide —640323-001 7- 40
MERGE_JOIN Operator
The following is an example of MERGE_ANTI_SEMI_JOIN:
Control Query Default nested_joins 'off'; Control Query Default hash_joins 'off'; PREPARE TestQuery3a FROM
SELECT s_nationkey, s_suppkey FROM supplier
WHERE s_suppkey NOT IN
( SELECT ps_suppkey from partsupp) 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 anti-semi join_method ... merge
merge_join_predicate ... (DETCAT.DETSCH.SUPPLIER.S_SUPPKEY = DETCAT.DETSCH.PSX1.PS_SUPPKEY)