SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-58
Using Indexes
Specifying GROUP BY With DISTINCT
You can specify grouping and the elimination of duplicate rows and still avoid extra
sorts. A single sort satisfies both grouping and the elimination of duplicate rows when
this occurs:
•
The GROUP BY list is a subset of the SELECT DISTINCT list, as follows:
SELECT DISTINCT COUNT(*), B, B-D, D FROM T
GROUP BY B, D ;
SQL performs a single sort (on B, D) to perform the grouping. Because each (B, D)
value is unique after grouping, so is each (B, D, B-D, COUNT (*)) value.
•
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 follows:
SELECT DISTINCT A, C FROM T
GROUP BY A, B, C ;
In this example, the GROUP BY clause is unnecessary. A single sort eliminates
duplicates and performs the grouping (A,C). Because B does not appear in the
select list and there are no aggregates or HAVING clause that depend on the full
grouping, it is not necessary to group by B.
If the DISTINCT column is not already in the GROUP BY list, you can avoid the sort for
the DISTINCT column by adding the DISTINCT column to the list of grouping columns.
Specifying ORDER BY With DISTINCT
You can specify ordering and the elimination of duplicate rows and still avoid extra
sorts, if the ORDER BY list is a subset of the DISTINCT list. Consider this query:
SELECT DISTINCT A, B, C, D FROM T
ORDER BY A, B DESC ;
SQL can evaluate the query with a single sort on (A, B DESC, C, D) to satisfy both the
ordering and the elimination of duplicate rows. The position and sorting order
(ascending or descending) of A and B must match the index chosen to perform the
sort, but C and D can appear in any position after A and B and in either ascending or
descending order.
Using Indexes
Indexes can improve the performance of queries that would otherwise require a sort
operation.
If an index exists that has the same key columns as the ORDER BY columns, you can
avoid a sort if the sequence of columns in the ORDER BY clause matches the
sequence of columns in the index. State your ordering requirements explicitly; do not
assume that rows will be returned in a specific order because of the primary-key
sequence or because there are equality predicates on index columns. When you