SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-49
Evaluation by the Disk Process
•
The optimizer determines that there is a saving in messages when using disk
process aggregation and grouping. This determination is based on costing.
•
The query does not use OR optimization.
GROUP BY Using a Parallel Plan
In a parallel plan, grouping and aggregation are done by the disk process if this
conditions are satisfied:
•
The query accesses a single table.
•
The query uses primary index access against a partitioned table that is in grouping
column order, or the query uses index-only access against a partitioned index that
is in grouping column order. Following is an example of the second condition:
CREATE INDEX ix1 ON empdata (manager, salary)
PARTITION (
$vol1.subvol.ix1p
FIRST KEY ‘I’,
$vol2.subvol.ix1p
FIRST KEY ‘R’);
SELECT manager SUM(salary)
FROM empdata
GROUP BY manager ;
Although the index must be partitioned, the table need not be partitioned. The
example creates an index with three partitions for manager. (Manager ranges from
A to H, I to Q, and R to Z.)
•
The optimizer determines that there is a lower execution cost when using disk
process aggregation and grouping. This determination is based on the ratio
between the number of groups selected and the total number of rows read.
•
The query does not use OR optimization.
GROUP BY and MDAM
When the optimizer chooses MDAM processing, the decision to process GROUP BY
predicates and aggregates in the disk process or in the executor is based, in part, on
the number of columns the optimizer selects for processing by MDAM. (The selected
columns are listed in the EXPLAIN plan under “MDAM predicate set” and “next set.”)
For example, if the EXPLAIN plan shows that the first four columns of a six-column key
are used for MDAM, then a GROUP BY on the first four, five, or all six columns can be
done by the disk process. A GROUP BY on the first, first two, or first three columns
must be done in the executor.
If you do not like the number of columns the optimizer chooses for MDAM processing,
you can use the CONTROL TABLE directive for MDAM and specify the number of key
columns you want MDAM to use. For more information, see Controlling the Number of
Key Columns Used by MDAM on page 4-30 and “CONTROL TABLE Directive” in the
SQL/MP Reference Manual.