SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-48
Evaluation by the Disk Process
If there is no GROUP BY clause, disk process aggregation is selected if these
conditions are true:
•
The query does not contain any executor predicates (except HAVING); for
example:
SELECT SUM(SALARY)
FROM EMPDATA
WHERE SALARY >= 50000;
•
The query uses primary index access or index-only access; that is, all columns
referenced by the query can be found in the index.
•
If the query includes a join operation, aggregation must be on the innermost table
of the join, as follows:
SELECT SUM(EMPDATA.SALARY)
FROM DEPT, EMPDATA
WHERE DEPT.DEPTNO = EMPDATA.DEPTNO ;
If there is no GROUP BY clause, then either serial or parallel processing might occur.
If there is a GROUP BY clause, then grouping and aggregation are done by the disk
process only if certain additional conditions are satisfied. These additional conditions
vary, depending on whether the user has requested parallel processing. You can use
the =_SQL_CMP_PARALLEL DEFINE or a CONTROL EXECUTOR directive to
request the optimizer to consider a parallel plan. (For information on DEFINES, see the
SQL/MP Reference Manual. For information on the CONTROL EXECUTOR directive,
see Section 4, Improving Query Performance With Environmental Options and also the
SQL/MP Reference Manual.) If neither the CONTROL EXECUTOR directive nor the
DEFINE is used, the default is a serial plan.
VSBB is disabled for disk-process aggregation.
GROUP BY Using a Serial Plan
In a serial plan, grouping and aggregation are done by the disk process if these
conditions are satisfied:
•
All aggregate columns and grouping columns are on the same table, or the
aggregate columns are on different tables but are in sorted order before the
GROUP BY operation is performed.
•
The access path satisfies the order of the group-by columns, as follows:
CREATE INDEX ix1 ON empdata (manager, salary);
SELECT SUM(salary)
FROM empdata
GROUP BY manager ;
•
The query only references one table in the FROM clause.
Note: If a left join operator is present with a COUNT (*) operation, the executor process
must perform null augmentation prior to aggregation.