SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-55
Sort Operations
the index (the first column is 0). You can also retrieve this information from the
indexes table in the catalog.
•
Use predicates for the leading key columns where possible, because SQL uses
predicates for key positioning. As the number of leading key columns with
predicates increases, the key positioning becomes more precise.
•
When you use an ORDER BY clause with an index, include all leading columns of
the index being used. This strategy avoids a sort operation. When reading in
reverse, use all leading columns and specify DESCENDING (assuming that the
columns are in ascending order), to avoid the sort.
•
When you use a UNION operator, specify UNION ALL. Specifying UNION without
ALL causes a sort operation to eliminate duplicate rows.
•
Include DISTINCT columns at the beginning of an ORDER BY list when both
clauses are included in a query.
An alternate option that requires system management knowledge is to maintain
indexes that provide SQL with a naturally sorted order for the sequencing of rows most
often requested.
The next subsection describes sort operations. The following subsections describe
how to use ORDER BY, GROUP BY, and DISTINCT in combination and how an index
can improve performance of a query.
Sort Operations
A logical sort returns values in a specified order. The ORDER BY clause requires a
logical sort operation. The GROUP BY clause, DISTINCT clause, UNION clause
without the ALL option, and noncorrelated subqueries can also require a sort
operation, but these operations can sometimes use a hashing operation, which is more
efficient than a sort. A noncorrelated subquery causes a sort only if it would perform a
sort if it were performed alone.
A sort is logically required when a query specifies that:
•
The result is presented in a certain order (using the ORDER BY clause).
•
Duplicates are removed (using the DISTINCT key word).
•
The result is grouped (using the GROUP BY clause) on certain columns.
Avoiding Sorts
Because sorting is an expensive operation, the optimizer attempts to minimize the
number of sorts that must be performed for a query. The optimizer avoids a sort for an
ORDER BY, GROUP BY, DISTINCT, or UNION operation if the sort specification (such
as the ORDER BY columns) matches a prefix of the index columns.
SQL also avoids unnecessary sorts by removing an ORDER BY clause if no column is
present in the SELECT list.