SQL/MX 3.2.1 Query Guide (H06.26+, J06.15+)

SQL/MX Operators
HP NonStop SQL/MX Release 3.2.1 Query Guide—663851-003
7-28
HYBRID_HASH_JOIN Operator
join_type .............. inner anti-semi
join_method ............ hash
hash_join_predicate .... (DETCAT.DETSCH.PARTSUPP.PS_SUPPKEY =
DETCAT.DETSCH.SUPPLIER.S_SUPPKEY)
HYBRID_HASH_JOIN Operator
Join Group
The HYBRID_HASH_JOIN operator joins the data from two child tables. It creates a
hash table for the inner table and joins the outer table by hashing each outer row and
looking for matches in the hash table. This operator can overflow to disk when the
inner table is too large to fit in memory. Equijoins and cross-products are supported by
this operator.
The HYBRID_HASH_JOIN has two child nodes. The description field for this operator
contains the following:
The following is an example of the HYBRID_HASH_JOIN operator:
PREPARE TestQuery6 FROM
SELECT [FIRST 100] 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
AND p_size = 15
AND p_type like '%BRASS'
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'EUROPE'
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, natural, left, inner semi, or inner anti-
semi-join.
text
join_method Name of join method: hash text
hash_join_predicate Expression of the join predicate. expr(text)
parallel_join_type Type1 or Type2, depending on parallel join
algorithm.
text
selection_predicates Expression of the WHERE clause. expr(text)
TP663851.fm Page 28 Wednesday, January 30, 2013 5:37 PM