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-25
HASH_PARTIAL_GROUPBY_LEAF Operator
(sum(DETCAT.DETSCH.LX3.L_EXTENDEDPRICE) >
cast((100 * 100) AS NUMERIC(18,2) SIGNED))
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.
The HASH_PARTIAL_GROUPBY_LEAF operator has one child node. The description
field for this operator contains the following:
The following is an example of the HASH_PARTIAL_GROUPBY_LEAF operator:
control query shape
hash_groupby(partition_access(hash_groupby(scan)));
prepare TestQuery32 from
select d from t016pt1 group by d;
DESCRIPTION
fragment_id ............ 2
parent_frag ............ 0
Note. The query mentioned in the example provides the HASH_GROUPBY operator in
Windows NT. To get this operator in the NonStop operating system, use some more CQS.
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
aggregates Expression of the aggregate function. expr(text)
selection_predicates Expression of the HAVING clause. expr(text)
grouping_columns Expression of the grouping columns. expr(text)
TP663851.fm Page 25 Wednesday, January 30, 2013 5:37 PM