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

Operators and Operator Groups
HP NonStop SQL/MX Query Guide—523728-003
7-16
HYBRID_HASH_JOIN Operator
6E+010 8.1857626E+010 CPU_TIME: 8.18576e+10 IO_TIME: 0
MSG_TIME: 0 IDLETIME:
0 PROBES: 1
hash_join_predicate:
(indexcol(TPCDF.SF100F.PARTSUPP.PS_SUPPKEY) =
indexcol(TPCDF.SF100F.SUPPLIER.S_SUPPKEY))
join_type: inner anti-semi
join_method: hash
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 example of the HYBRID_HASH_JOIN operator is based on:
SELECT n_name, CAST(SUM(l_extendedprice*(1-l_discount))
AS NUMERIC(18,2)) AS revenue
FROM customer,orders,lineitem,supplier,nation, region
WHERE c_custkey = o_custkey
AND o_orderkey = l_orderkey
AND l_suppkey = s_suppkey
AND c_nationkey= s_nationkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'ASIA'
AND o_orderdate >= DATE '1994-01-01'
AND o_orderdate < DATE '1994-01-01' + INTERVAL '1' YEAR
Token Followed by ... Data Type
join_type Inner, natural, left, inner
semi, or inner anti-semi-join
text
join_method Name of join method text
hash_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)