SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-46
How the Optimizer Processes Aggregates and
Group-By Operations
Confirm all use of this option with data from the Measure product and verify its use
periodically to account for changes in data distributions and volumes. Reevaluate its
effectiveness with each new version of SQL.
How the Optimizer Processes Aggregates and
Group-By Operations
SQL can use these strategies to evaluate aggregates and GROUP BY operations,
listed from most to least optimal:
•
MIN/MAX optimization
•
Evaluation by the disk process
•
Evaluation by the executor, without sorting
•
Hashed aggregation and grouping
•
Sorted GROUP BY operation
In addition, for partitioned tables, SQL can process aggregations in parallel. To do this,
SQL starts an ESP for each partition. Each server process reads its corresponding
partition, processes the partial aggregate, and sends it to the master ESP. If partitions
are distributed across a network, this strategy can reduce the amount of network traffic
as well as interprocessor traffic.
EXPLAIN output lists the location of aggregate and GROUP BY evaluation (disk
process or executor).
These paragraphs describe each type of evaluation strategy.
MIN and MAX Optimization
The processing of MAX and MIN functions usually requires reading the entire table. If,
however, all of these conditions are met for a query, SQL reads only one row when
evaluating a MAX or MIN function:
•
The FROM clause names only one table.
•
The select list contains only one function.
•
The column operand of the MIN or MAX function is in the begin key or the end key.
For example, consider this query. Suppose that there is an index on (A, B, C):
SELECT MAX(C) FROM T
WHERE A = 10 AND B = 20 ;
This query can be evaluated by reading a single row from the index, even though there
is no predicate on C.