SQL/MX 3.x Query Guide (H06.22+, J06.11+)

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.
expr(text)