SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-50
Evaluation by the Executor Component
MDAM can process GROUP BY predicates in queries that use serial or parallel plans.
Evaluation by the Executor Component
If an aggregate or GROUP BY operation does not fit the preceding requirements, it
cannot be evaluated by the disk process. All participating rows are transferred from the
disk process to the executor, where groups are formed and aggregation is done.
These situations also require evaluation by the executor:
•
A query that includes an executor predicate, because a row does not qualify until
the executor predicate is evaluated, as shown
SELECT SUM(A) FROM T WHERE A IN (SELECT * FROM T1) ;
•
An index access with the aggregate column on the base table.
•
A WHERE clause in a LEFT JOIN query that is evaluated on a column from the
inner table after the outer join is done.
•
A query that uses real sequential block buffering (RSBB); for more information, see
Section 4, Improving Query Performance With Environmental Options.
•
Aggregation if any partition of the table is on a node that runs a D10 or earlier
version of SQL, or if information about a partition is not available at compile time.
Evaluation by Both the Disk Process and the Executor
In these situations, aggregation is done by the disk process but is finalized by the
executor process:
•
When OR optimization or MDAM is performed, as shown:
SELECT SUM(A) FROM T WHERE A = 5 OR (A >= 10 AND B = 6) ;
•
If there is a nested join in the query
•
If the user has requested that SQL skip unavailable partitions, by using the
CONTROL TABLE...SKIP UNAVAILABLE PARTITIONS directive
Hashed Aggregation and Grouping
If the table is not already ordered on the GROUP BY columns, SQL can perform
aggregation and GROUP BY processing with either a sort operation or a hash
operation, depending on which one has a lower cost.
Hashing reduces the cost of sorting data and therefore performs better than other
methods when rows are not already ordered on the grouping columns. (For more
information about cost, see Section 5, Selectivity and Cost Estimates.) SQL hashes the
rows on its grouping columns and then computes the aggregate on the result of the
hashed rows.