SQL/MP Installation and Management Guide

Understanding and Planning Database Tables
HP NonStop SQL/MP Installation and Management Guide—523353-004
3-20
Determining When to Use Indexes
the table. If SQL generates KEYTAG values, it sequentially numbers all indexes on
a table, beginning with 1. KEYTAG 0 is the primary key.
•
Indexed columns—the columns located in the column list in the CREATE INDEX
statement.
•
For nonunique indexes—columns of the primary key of the underlying table. These
columns are required to identify rows uniquely in the index. The primary key of a
nonunique index automatically includes the columns of the primary key of the
underlying table to associate the indexed columns with the rows of the table. In a
unique index, the columns of the primary key of the underlying table are not
logically included in the primary key of the index, but are physically included in the
index file.
The primary key of an index differs from the primary key of a table because primary
key columns of an index can contain null values. In calculating the length of an index
key, you must include the null indicator (two bytes) in the length for each column that
allows null values.
Creating Indexes for Specific Situations
These subsections describe the use of indexes for specific situations.
Creating an Index for Frequently Used Columns
If an application has many queries that refer to a column in a table, an index on that
column might improve the performance of some of the queries. For example, consider
this query:
SELECT QTY_ORDERED, RETAIL_PRICE FROM INVNTRY
WHERE RETAIL_PRICE = 100 ;
If there is no index on RETAIL_PRICE, SQL must scan the table and evaluate the
predicate (RETAIL_PRICE = 100) against each of the rows in the table.
An index on both columns would enable an index-only access; that is, SQL could
retrieve all required data from the index and not have to read the base table. Consider
this query, for example:
CREATE INDEX RPRICE
ON INVNTRY (RETAIL_PRICE, QTY_ORDERED) ;
Note. If a column such as QTY_ORDERED is frequently updated, an index on the column
incurs the index maintenance cost resulting from several insert and delete operations. This
approach might reduce the benefit obtained from having the index defined on the table.