SQL/MX 3.2 Query Guide (H06.25+, J06.14+)

SQL/MX Operators
HP NonStop SQL/MX Release 3.2 Query Guide—663851-002
7-41
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-42 and MERGE_SEMI_JOIN Operator on
page 7-44.
The MERGE_ANTI_SEMI_JOIN operator has two child nodes. The description field for
this operator contains the following:
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
TP663851.fm Page 41 Thursday, August 2, 2012 4:47 PM