SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-47
Evaluation by the Disk Process
If the begin-key and end-key predicates contain only equal comparisons (=), then the
MIN or MAX processing of the next key column in sequence, which is not part of the
begin or end key, is also evaluated by reading only one row from the index.
MAX and MIN functions are not optimized when
•
The operand of the MAX or MIN function is an expression; for example:
MAX(-C)
•
The query specifies a GROUP BY or a HAVING clause.
•
The WHERE clause contains any of these subqueries:
°
A correlated subquery
°
An ANY, SOME, or ALL subquery
°
An EXISTS subquery
•
The WHERE clause contains any predicate connected by an OR operator that
might use a keyed access.
•
Both MAX and MIN are requested in one SELECT statement; for example:
SELECT MIN(A), MAX(B) FROM T ;
In such cases, you should write two SELECT statements.
If an index exists on the column that is an argument of the MIN or MAX function and
the column is the prefix of the index, the executor component can read the first or last
row to retrieve the MIN or MAX value; for example:
SELECT MIN (RETAIL_PRICE)
FROM INVNTRY
Assuming that RETAIL_PRICE is the first key column of an index, SQL reads only the
first row of the index to find the minimum value.
Evaluation by the Disk Process
The disk process can evaluate these:
•
Aggregate functions AVG, COUNT, MAX, MIN, and SUM
•
The GROUP BY clause
When an aggregate or GROUP BY operation can be evaluated by the disk process,
the disk process scans the data and returns only the aggregated or grouped values to
the file system. This can reduce the number of messages sent between the file system
and the disk process.
The requirements for disk process aggregation depend on whether there is a GROUP
BY clause in the query.