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

Operators and Operator Groups
HP NonStop SQL/MX Query Guide—523728-003
7-13
HASH_PARTIAL_GROUPBY_LEAF Operator
The example of the HASH_GROUPBY operator is based on:
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;
? XX
211862597497309538 13 HASH_GROUPBY 12 ?
3.3329999E-001 7.2278591E-005 2.2941484E+000
CPU_TIME: 3.70538e-07 IO_TIME: 0 MSG_TIME: 0 IDLETIME: 0
PROBES: 1
grouping_columns:
indexcol(\TESTSYS.$DATA14.SPTPCD.ORDERS.O_ORDERKEY),
indexcol(\TESTSYS.$DATA14.SPTPCD.ORDERS.O_ORDERDATE),
indexcol(\TESTSYS.$DATA14.SPTPCD.ORDERS.O_SHIPPRIORITY)
aggregates:
sum((cast(indexcol(\TESTSYS.$DATA14.SPTPCD.LINEITEM.L_EXTENDEDPR
ICE)) * cast((cast((1 * 100)) -
indexcol(\TESTSYS.$DATA14.SPTPCD.LINEITEM.L_DISCOUNT)))))
HASH_PARTIAL_GROUPBY_LEAF Operator
Groupby Group
The HASH_PARTIAL_GROUPBY_LEAF operator executes a partial group by
operation as close to where the data is read as is cost effective. This strategy reduces
the amount of data that must be relocated for a query. When executed in DAM, the
HASH_PARTIAL_GROUPBY_LEAF is limited to a small amount of memory. Any
group that does not fit in memory is passed on ungrouped, with the full grouping
occurring at the HASH_PARTIAL_GROUPBY_ROOT. If the
HASH_PARTIAL_GROUPBY_LEAF is not executed in DAM, more memory is
available, and all rows are grouped. The groups from multiple processes are then
rolled up in the HASH_PARTIAL_GROUPBY_ROOT.
The operator must always be accompanied by a HASH_PARTIAL_GROUPBY_ROOT
operator above it in the tree, which finalizes the query.