SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-57
Optimizing Combinations of Clauses
You can use the EXPLAIN utility to check the type of sort operation performed and the
size of the workspace allocated by the executor (UPS workspace) for in-memory sorts.
For more information on EXPLAIN, see Section 6, Analyzing Query Performance.
Optimizing Combinations of Clauses
The SQL optimizer attempts to minimize the number of sorts required for a query. You
can assist this process by using the guidelines in this subsection.
Specifying ORDER BY With GROUP BY
You can specify order and grouping and still eliminate extra sorts. A single sort orders
and groups results when this occurs:
•
The ORDER BY list is a subset of the GROUP BY list.
•
For example, consider this query:
SELECT A, B, C, D FROM T
GROUP BY A, B, C, D
ORDER BY B, C DESC ;
A single sort (on B, C DESC, A, D) performs both the grouping and the ordering.
•
The GROUP BY list contains n items, and those items are also the first n items of
the ORDER BY list; for example:
SELECT A, B, C, COUNT(*), SUM(A) FROM T
GROUP BY A, B, C
ORDER BY 1, 2 DESC, 3, 5, 4 ;
SQL need only perform a single sort (on A, B DESC, C) to perform both the
grouping and the ordering.
The formation of groups requires the groups to be hashed in memory or to ordered by
the grouping columns. If they are already in order, then no sorting or hashing is
needed. If they are not in order, the optimizer compares a plan that sorts columns first
with a plan that hashes the columns.
When both an ORDER BY clause and a GROUP BY (or DISTINCT) clause are used in
a query, SQL can choose to combine the lists into a single sort. This is possible only if
the GROUP BY list is a prefix of the ORDER BY list. In such a case, a single sort can
fulfill both the GROUP BY and ORDER BY requests. This can save a sort and make
the plan more efficient, but might still be less efficient than hashing the groupings and
then sorting a small number of resulting rows.