FastSort Manual

Sorting From NonStop SQL/MP
FastSort Manual—429834-003
8-6
Using a Sort Merge Join

The GROUP BY list is a subset of the SELECT DISTINCT list, as in the following
query:
SELECT DISTINCT COUNT(*), BOSTON, BOSTON-DALLAS, DALLAS
FROM SALES
GROUP BY BOSTON, DALLAS ;
A single sort on (BOSTON, DALLAS) groups the query results. Because each
(BOSTON, DALLAS) value is unique after grouping, each (BOSTON, DALLAS,
BOSTON-DALLAS, COUNT(*)) value is also unique.

The SELECT DISTINCT list is a subset of the GROUP BY list, there are no
expressions in the SELECT list, and no aggregates in a HAVING clause, as in the
following query:
SELECT DISTINCT ATLANTA, CHICAGO FROM SALES
GROUP BY ATLANTA, BOSTON, CHICAGO ;
In this case, only a single sort is required because the GROUP BY clause is
unnecessary. Because BOSTON is not in the SELECT list and no aggregates or
HAVING clauses rely on the full grouping, there is no need to group by BOSTON.
To build this logic into your query and avoid the unnecessary sort, add the
DISTINCT column to the GROUP BY list.
Specifying ORDER BY With DISTINCT
You can order query results and eliminate duplicate rows in a single sort if the ORDER
BY list is a subset of the DISTINCT list, as in the following query:
SELECT DISTINCT ATLANTA, BOSTON, CHICAGO, DALLAS FROM SALES
GROUP BY ATLANTA, BOSTON DESC ;
In this case, a single sort on (ATLANTA, BOSTON DESC, CHICAGO, DALLAS) orders
results and eliminates duplicate rows. The position and sorting order, ascending or
descending, of ATLANTA and BOSTON must match the index used for the sort.
However, CHICAGO and DALLAS can occur in any order after ATLANTA and
BOSTON, and in either ascending or descending order.
Using a Sort Merge Join
A join operation combines data from two tables or views. The sort merge join is one of
four join methods available to the SQL/MP optimizer. The optimizer evaluates query
cost and decides which type of join to perform.
For the optimizer to choose a sort merge join, these conditions must exist:

The joining columns of outer and inner tables must be in ascending or descending
order

The query must be an equijoin query
During a sort merge join, FastSort always sorts the data from the inner table and stores
it in a temporary entry-sequenced table. If the outer table is not already sorted on the