SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

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 more 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).
NOTE: If you specify both the MIN and MAX functions in a single query, a scan of the index is
necessary.
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, B, C), there are three key prefixes:
A, AB, and ABC. The prefix ABC corresponds to the full key, the other prefixes form a partial key.
In this example, COL1 and COL2 are key prefixes from two different indexes. NonStop SQL/MX
uses the indexes to retrieve all the rows that satisfy the predicate that COL2 = 20 or COL1 = 10:
SELECT * FROM T
WHERE COL1 = 10 OR COL2 = 20 ;
Evaluating the query by using the indexes is much more efficient than scanning the entire table.
Determining a Database Layout 41