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

Operators and Operator Groups
HP NonStop SQL/MX Query Guide—523728-003
7-27
MATERIALIZE Operator
The MATERIALIZE operator has one child node. The description field for this operator
contains:
The example of the MATERIALIZE operator is based on:
SELECT [FIRST 100] s_acctbal, s_name, n_name, p_partkey, p_mfgr,
s_address, s_phone, s_comment
FROM part,supplier,partsupp, nation, region
WHERE p_partkey = ps_partkey
AND s_suppkey = ps_suppkey
AND p_size = 15
AND p_type like '%BRASS'
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'EUROPE'
AND ps_supplycost = (SELECT MIN(ps_supplycost)
FROM partsupp, supplier, nation, region
WHERE p_partkey = ps_partkey
AND s_suppkey = ps_suppkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'EUROPE')
ORDER BY s_acctbal desc, n_name, s_name, p_partkey;
? XX
211862597476194167 26 MATERIALIZE
2 4 ?
1.0000000E+000 3.6709364E-003 2.6314377E-001 CPU_TIME:
0.001469 IO_TIME: 0 MSG_TIME: 0 IDLETIME: 0
PROBES: 10
values_given_to_child: execution_count
temp_table_key: indexcol(\TESTSYS.$DATA14.SPTPCD.SX1.S_SUPPKEY)
begin_key: (indexcol(\TESTSYS.$DATA14.SPTPCD.SX1.S_SUPPKEY) =
indexcol(\TESTSYS.$DATA14.SPTPCD.PARTSUPP.PS_SUPPKEY))
check_input_values: (execution_count = convert(execution_count))
operation_type: hash
scan_direction: forward
Token Followed by ... Data Type
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 Forward or reverse text
check_input_values Expression used to
check if input values
have changed
expr(text)