SQL/MX 2.x Query Guide (H06.04+, J06.03+)

SQL/MX Operators
HP NonStop SQL/MX Query Guide—540437-005
7-39
MERGE_ANTI_SEMI_JOIN Operator
The example of the MATERIALIZE operator is based on:
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 fragment of the
current fragment. The value is (none) for the
master executor.
integer