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-47
NESTED_JOIN Operator
The NESTED_ANTI_SEMI_JOIN operator has two child nodes. The description field
for this operator contains the following:.
The following is an example of the NESTED_ANTI_SEMI_JOIN operator:
PREPARE TestQuery12 FROM
SELECT *
FROM customer, nation
WHERE c_custkey > 10000 AND c_custkey < 10010
AND c_nationkey NOT IN
(select n_nationkey from nation where n_regionkey = 10)
ORDER BY c_custkey;
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
join_type .............. inner anti-semi
join_method .............. nested
NESTED_JOIN Operator
Join Group
The NESTED_JOIN operator describes a portion of an execution plan that involves a
nested join. This operator sends each outer row to the inner child, where it eventually
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
join_type Inner, left, natural, inner semi, or inner anti-
semi-join.
text
join_method Name of join method: nested or in-order
nested
text
parallel_join_type Type1 or Type2, depending on parallel join
algorithm.
text
join_predicate Expression of the ON clause that has not
been pushed down to the inner scan, typically
empty.
expr(text)
selection_predicate Expression of the WHERE clause that is not
included in the merge_join_predicate or in a
selection_predicate of any children.
expr(text)
TP663851.fm Page 47 Thursday, August 2, 2012 4:47 PM