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-54
ORDERED_UNION Operator
The difference between join_predicate and hash_join_predicate tokens is that the
former are the non-equijoin predicates, while the latter are equijoin predicates that you
use to help build and probe the hash table.
The following is an example of the ORDERED_HASH_SEMI_JOIN operator:
PREPARE TestQuery7 FROM
SELECT o_orderpriority, COUNT(*)
FROM orders
WHERE o_orderdate >= DATE '1993-07-01'
AND o_orderdate < DATE '1993-10-01'
AND EXISTS (SELECT *
FROM lineitem
WHERE l_orderkey = o_orderkey
AND l_commitdate < l_receiptdate)
GROUP BY o_orderpriority
ORDER BY o_orderpriority;
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
join_type .............. inner semi
join_method ............ hash
hash_join_predicate .... (DETCAT.DETSCH.ORDERS.O_ORDERKEY =
DETCAT.DETSCH.LX5.L_ORDERKEY)
ORDERED_UNION Operator
The ORDERED_UNION operator ensures that its left and right children work one at a
time. It first receives rows from its left child. It then enables the right child to work. In
effect, for the same request, ORDERED_UNION operator produces rows from left child
followed by rows from right child.
The description field for this operator contains the following:
parallel_join_type Type1 or Type2, depending on parallel join
algorithm.
text
reuse_comparison_values List of values that cause the hash table to be
rebuilt when they change.
expr(text)
selection_predicates Expression of the WHERE clause. expr(text)
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 54 Thursday, August 2, 2012 4:47 PM