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

Operators and Operator Groups
HP NonStop SQL/MX Query Guide—523728-003
7-26
MATERIALIZE Operator
The example of the LEFT_ORDERED_HASH_JOIN operator is based on:
SELECT *
FROM customer LEFT JOIN nation ON c_nationkey = n_nationkey
WHERE c_custkey > 1000 AND c_custkey < 1010
ORDER BY order by c_custkey
? XX
211932229677605958 6 LEFT_ORDERED_HASH_JOIN
5 2
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: left
join_method: hash
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 in SQL/MX Release 2.x. If needed,
enable it by setting the MATERIALIZE default to ON. SQL/MX Release 2.x uses the
ORDERED_HASH_JOIN Operator to replace the functionality of the MATERIALIZE
node.
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)