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

Operators and Operator Groups
HP NonStop SQL/MX Query Guide—523728-003
7-32
NESTED_JOIN Operator
AND ps_suppkey NOT IN (SELECT s_suppkey
FROM supplier
WHERE s_comment LIKE
'%Better Business
Bureau%Complaints%')
GROUP BY p_brand, p_type, p_size
ORDER BY 4 DESC, 1, 2, 3;
? XX
211862597679263939 11 NESTED_ANTI_SEMI_JOIN
7 1 0
0.0000000E+000 2.8552881E-006 1.9244983E+000 CPU_TIME:
0.821998 IO_TIME: 0.091286 MSG_TIME: 0.115813 IDLETIME: 1.1025
PROBES: 1
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
goes to a scan operation. Normally, the inner scan access is keyed, and the number of
outer probes is small, resulting in an efficient join. The actual join is done in the inner
scan instead of the NESTED_JOIN operator. Nested joins support range operations
(>=, >, <, <=) in addition to equijoins.
The NESTED_JOIN has two child nodes. The description field for this operator
contains:
The example of the NESTED_JOIN operator is based on:
SELECT 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
Token Followed by ... Data Type
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
join_predicate Expression of the join
predicates
expr(text)
parallel_join_type Type1 or Type2, depending
on parallel join algorithm
text
selection_predicates Expression of the selection
predicates
expr(text)