SQL/MP Installation and Management Guide

Understanding and Planning Database Tables
HP NonStop SQL/MP Installation and Management Guide—523353-004
3-17
Determining When to Use Indexes
Each index is assigned a name and is physically stored in a separate key-sequenced
file that possesses the same file name as the index. Index files are not tables, and they
cannot be queried directly through SQL; they are only a tool for providing faster access
to tables.
Performance Benefits of Indexes
Indexes can improve performance by eliminating the need for the disk process to
access the underlying table. If the query can be satisfied by the columns contained in
the index and the access returns unique rows, the underlying table will not be
accessed. By using only the index, you reduce I/O to the table.
For example, consider this query in which ATABLE has a unique index named
AINDEX, which contains columns A and B, in that order, from ATABLE:
SELECT A,B FROM ATABLE
WHERE A > 100
ORDER BY A,B;
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.
Another 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.
A third use of an index is to avoid a full table scan. Consider this query:
SELECT ITEM_NAME, RETAIL_PRICE
FROM INVNTRY
WHERE RETAIL_PRICE = 100
Without an index on RETAIL_PRICE, SQL must scan the table and evaluate the
following predicate 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 this query that contains an inequality
predicate, however, because ITEM_NAME is not part of the index:
SELECT ITEM_NAME, RETAIL_PRICE
FROM INVNTRY
WHERE RETAIL_PRICE > 100
For every index row that satisfies the predicate, an I/O operation (a request from the
file system to the disk process) must be incurred to retrieve the column ITEM_NAME
from the base table.
If the query selects only columns included in the index, the index on RETAIL_PRICE
can help performance. Including ITEM_NAME in the index could cause index-only
access, which would improve the performance of this query.