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-48
NESTED_JOIN Operator
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 following:
The following is an example of the NESTED_JOIN operator:
PREPARE TestQuery4 FROM
SELECT l_orderkey,
CAST(SUM(l_extendedprice*(1-l_discount))AS
NUMERIC(18,2)), o_orderdate, o_shippriority
FROM customer,orders,lineitem
WHERE c_mktsegment = 'BUILDING'
AND c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate < DATE '1995-03-15'
AND l_shipdate > DATE '1995-03-15'
GROUP BY l_orderkey, o_orderdate, o_shippriority
HAVING sum(l_extendedprice)> 100
ORDER BY 2 DESC,3 ASC;
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
join_type .............. inner
join_method .............. nested
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
join_predicate Expression of the join predicate. expr(text)
parallel_join_type Type1 or Type2, depending on parallel join
algorithm.
text
selection_predicate Expression of the WHERE clause. expr(text)
TP663851.fm Page 48 Thursday, August 2, 2012 4:47 PM