SQL/MP Installation and Management Guide

Understanding and Planning Database Tables
HP NonStop SQL/MP Installation and Management Guide—523353-004
3-22
Determining When to Use Indexes
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 (specifying a
forward read) to satisfy MIN (RETAIL_PRICE). The second query can be evaluated by
reading a single row from the index (specifying 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 involve
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 I contains 3 key columns (A, B, C), then
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. SQL
uses the indexes to retrieve all the rows that satisfy the predicate COL2 = 20 and do
not have 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.
Note. If you specify both the MIN and MAX functions in a single query, a scan of the index is
necessary.