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

Operators and Operator Groups
HP NonStop SQL/MX Query Guide—523728-003
7-31
NESTED_ANTI_SEMI_JOIN Operator
The example of the MERGE_UNION operator is based on:
SELECT x.c_name, x.c_phone
FROM $sql07.tpcdtab.customer x, $big18a.sch1810.cclone y
WHERE x.c_phone = y.c_phone
UNION SELECT s.c_name, s.c_phone
FROM $sql07.tpcdtab.customer s,
$big18a.sch1810.cclone t
WHERE s.c_name = t.c_name ORDER BY x.c_name;
10 21 22 merge_union
sort_order:
ValueIdUnion(\TESTSYS.$SQL07.TPCDTAB.CUSTOMER.C_NAME,
\TESTSYS.$SQL07.TPCDTAB.CUSTOMER.C_NAME),
ValueIdUnion(\TESTSYS.$SQL07.TPCDTAB.CUSTOMER.C_PHONE,
\TESTSYS.$SQL07.TPCDTAB.CUSTOMER.C_PHONE)
merge_expr: ((indexcol(\TESTSYS.$SQL07.TPCDTAB.CUSTOMER.C_NAME),
indexcol(\TESTSYS.$SQL07.TPCDTAB.CUSTOMER.C_PHONE)) <=
(indexcol(\TESTSYS.$SQL07.TPCDTAB.CUSTOMER.C_NAME),
indexcol(\TESTSYS.$SQL07.TPCDTAB.CUSTOMER.C_PHONE)))
union_type: merge
NESTED_ANTI_SEMI_JOIN Operator
Join Group
The NESTED_ANTI_SEMI_JOIN operator describes a portion of an execution plan
that involves a nested join. This operator returns all rows from the inner table that do
not satisfy the predicates. See NESTED_JOIN Operator on page 7-32.
The NESTED_ANTI_SEMI_JOIN operator has two child nodes. The description field
for this operator contains:.
The example of the NESTED_ANTI_SEMI_JOIN operator is based on:
SELECT p_brand, p_type, p_size, COUNT(DISTINCT ps_suppkey)
FROM partsupp, part
WHERE p_partkey = ps_partkey
AND p_brand <> 'Brand#45'
AND p_type NOT LIKE 'MEDIUM POLISHED%'
AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9)
Token Followed by... Data Type
join_type Inner, left, natural,
inner semi, or inner
anti-semi-join
text
join_method Name of join
method
text
parallel_join_type Type1 or Type2,
depending on
parallel join
algorithm
text