SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-59
Using Indexes
specify complete column order, performance is best, but when you cannot, MDAM
preserves the order of the key.
For information about creating indexes, see the SQL/MP Installation and Management
Guide.
Examples
Consider this query:
SELECT * FROM INVNTRY
ORDER BY ITEM, RETAIL_PRICE ;
If the INVNTRY table is large, the cost of sorting the table might be very high. An index
on the columns ITEM and RETAIL_PRICE would mean that no sort is required to
satisfy the ORDER BY clause:
CREATE INDEX RPRICE
ON INVNTRY (ITEM, RETAIL_PRICE) ;
It is still possible, however, that a scan and sort might be less expensive than an index
access. If so, SQL ignores the index and scans the base table instead.
For another example, consider a query that specifies the elimination of duplicate rows:
SELECT DISTINCT A, B, C FROM T ;
A unique index on any subset of columns A, B, and C would guarantee that all A, B,
and C values are unique. So, if SQL chooses the unique index as the access path,
there is no need to eliminate duplicates during query execution.
CREATE UNIQUE INDEX UI
ON T (A, B) ;
As a final example, consider this query:
SELECT COUNT(*) FROM T1
GROUP BY T1.A, T1.B, T1.C ;
All items in the GROUP BY list are from a single table, so a unique index on any
subset of the GROUP BY list can be used to perform the grouping, as follows:
CREATE UNIQUE INDEX UI
ON T1 (T1.A, T1.C) ;