SQL/MP Installation and Management Guide

Understanding and Planning Database Tables
HP NonStop SQL/MP Installation and Management Guide—523353-004
3-21
Determining When to Use Indexes
For another example, suppose that applications frequently access the EMPLOYEE
table by employee name (and the primary key is EMPNUM). You can speed the
execution of these queries by creating an index of employee names based on the
LAST_NAME and FIRST_NAME columns, as shown in this example:
CREATE INDEX XEMPNAME
ON EMPLOYEE (LAST_NAME, FIRST_NAME) ;
Avoiding Sort Operations
Indexes can help the performance of queries that might require a sort operation, such
as queries that contain one or more of these keywords:
DISTINCT
GROUP BY
ORDER BY
Consider this query:
SELECT * FROM INVNTRY
ORDER BY QTY_ORDERED, RETAIL_PRICE ;
If the INVNTRY table is large, the cost of sorting the table might be very high. An index
on the columns QTY_ORDERED and RETAIL_PRICE, defined as follows, might mean
that no sort is required to satisfy the ORDER BY clause:
CREATE INDEX RPRICE
ON INVNTRY (QTY_ORDERED, RETAIL_PRICE) ;
A large cache size would also help ensure the efficiency of such a query.
To avoid a sort, define an index that has the same key columns as the sort key
columns; the sequence of these columns in the ORDER BY clause should then match
the sequence of columns in the index. Ordering requirements should be explicitly
stated, however.
Do not assume that rows will be returned in a specific order because of the primary-
key sequence. Selectivity considerations might cause the optimizer to choose an
alternate index, and the rows might not be in the desired primary-key sequence. For
more information, see the SQL/MP Query Guide.
If the ORDER BY clause is specified for a nonkey column, consider adding the column
to the index, right after the matching index columns. Subsequent ORDER BY
operations would then refer to all preceding matching columns plus these ordering
columns. If the order of key or index columns changes in the database, notify users
and programmers so that they can change ORDER BY clauses to match the new
sequence.