SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Understanding and Planning SQL/MX Tables
HP NonStop SQL/MX Installation and Management Guide—523723-004
4-18
Determining When to Use Indexes
•
ORDER BY
•
UNION without ALL
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) ;
To avoid a sort, define an index on the same columns that appear in the ORDER BY
clause. 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.
The index can cover a superset of the ORDER BY columns, a good approach when
the ORDER BY column is the leading column of the index. (For example, when the
index is on PART_NUMBER, SUPPLIER and ORDER_BY is on PART_NUMBER.)
For information about using the Multidimensional Access Method (MDAM) to reduce
the number of indexes you need for queries, see the SQL/MX Query Guide.
Creating Indexes for MIN and MAX Functions
Indexes can also improve the processing of the MIN and MAX functions. For example,
consider these two queries.
Suppose that an index exists on the RETAIL_PRICE column. The same index can be
used to read both the MIN and the MAX example:
SELECT MIN(RETAIL_PRICE) FROM INVNTRY ;
SELECT MAX(RETAIL_PRICE) FROM INVNTRY ;
The first query can be evaluated by reading a single row from the index (the optimizer
chooses a forward read) to satisfy MIN (RETAIL_PRICE). The second query can be
evaluated by reading a single row from the index (the optimizer chooses a backward
read) to satisfy MAX (RETAIL_PRICE).
Creating Indexes to Improve OR Operations
Indexes help improve the performance of the OR operator if the predicates involved
reference index columns.
The term key prefix refers to a set of contiguous key columns taken from the
leftmost key column onwards. For example, if an index contains three key columns (A,
Note. If you specify both the MIN and MAX functions in a single query, a scan of the index is
necessary.