SQL/MX 3.1 Query Guide (H06.23+, J06.12+)

SQL/MX Operators
HP NonStop SQL/MX Release 3.1 Query Guide—663851-001
7-24
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 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 24 Monday, October 17, 2011 11:48 AM