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-14
Determining When to Use Indexes
The query can be satisfied by accessing only AINDEX, which contains all the columns
requested. This type of index-only retrieval can be effective on both unique and
nonunique indexes.
A second use of an index is to eliminate a run-time sort of data columns by providing
an access path to the data in the order of the desired selection or by reverse order:
SELECT A,B FROM ATABLE;
ORDER BY A, B;
A third use of an index is to avoid a full table scan:
SELECT ITEM_NAME, RETAIL_PRICE
FROM INVNTRY
WHERE RETAIL_PRICE = 100;
Without an index on RETAIL_PRICE and assuming RETAIL_PRICE is not the leading
column on the table’s clustering key, NonStop SQL/MX must scan the table and
evaluate the predicate that follows against each of the rows in the table. An index on
RETAIL_PRICE would improve query performance dramatically.
An index on RETAIL_PRICE might not help the next query, which contains an
inequality predicate, however, because ITEM_NAME is not part of the index. Also, if
the selection predicate has high selectivity (for example, relatively few rows have a
RETAIL_PRICE of more than 100), the query can still benefit from using an index
instead of a full base table scan:
SELECT ITEM_NAME, RETAIL_PRICE
FROM INVNTRY
WHERE RETAIL_PRICE > 100;
To retrieve the ITEM_NAME values for the selected rows, NonStop SQL/MX bundles
many of the rows’ base table clustering key into a file system request. Within the disk
process, the ITEM_NAME columns are read from the base table by using random
access.
If the query selects only columns included in the index, the index on RETAIL_PRICE
can help. Including ITEM_NAME in the index could result in index-only access, which
would improve the performance of this query.
Evaluating the Benefits of a New Index
Creating alternate indexes can help the performance of some, but not all, queries. In
some applications, determining when an index could be efficient might be easier than
in others. Examine the WHERE clauses and ORDER BY clauses of the SELECT
statements in your application. You should consider creating indexes on only the most
frequently used columns.
In general, indexes are an efficient way to access data when:
•
The number of rows retrieved is small with respect to the total number of rows.