FastSort Manual

Sorting From NonStop SQL/MP
FastSort Manual—429834-003
8-5
Optimizing SQL Clause Combinations

UNION (without the ALL option)
and the specified columns do not match a prefix of the index columns.
If you specify more than one SQL ordering or grouping clause in a query, you can often
structure the query to avoid duplicate sorts. For queries in which the optimizer does not
choose a parallel execution plan, you should also use a =_SORT_DEFAULTS DEFINE
to optimize performance. For more information about setting up a
=_SORT_DEFAULTS DEFINE, see Configuring Your SQL/MP Sort Environment on
page 8-2.
Optimizing SQL Clause Combinations
The SQL/MP optimizer attempts to minimize sort operations. However, certain
combinations of SQL clauses can still cause unnecessary or duplicate sorts. The
following examples show how to structure SQL statements to minimize unnecessary
sorts.
Specifying ORDER BY With GROUP BY
You can order and group query results in a single sort when the following occurs:

The ORDER BY list is a subset of the GROUP BY list
For example, only one sort is necessary for the following query:
SELECT ATLANTA, BOSTON, CHICAGO, DALLAS FROM SALES
GROUP BY ATLANTA, BOSTON, CHICAGO, DALLAS
ORDER BY BOSTON, CHICAGO DESC ;
A single sort groups and orders the results of this query. In this case, SQL/MP
sorts on (BOSTON, CHICAGO, ATLANTA, DALLAS).

The GROUP BY list contains n items, which are also the first n items of the
ORDER BY list, as in the following query:
SELECT ATLANTA, BOSTON, CHICAGO, COUNT(*), SUM(ATLANTA)
FROM SALES
GROUP BY ATLANTA, BOSTON, CHICAGO
ORDER BY 1, 2 DESC, 3, 5, 4 ;
A single sort also groups and orders the results of this query. In this case, SQL/MP
sorts on (ATLANTA, BOSTON, CHICAGO).
Specifying GROUP BY With DISTINCT
You can group results and eliminate duplicate rows in a single sort when:
Note. NonStop SQL/MP does not invoke FastSort if the optimizer chooses a query plan that
reads the base table by primary key value.