SQL/MP Installation and Management Guide

Understanding and Planning Database Tables
HP NonStop SQL/MP Installation and Management Guide—523353-004
3-19
Determining When to Use Indexes
Defining an Index
When you define an index, consider these guidelines:
•
The maximum length for the rows of a nonunique index is 253 bytes. The row
length includes the sum of the lengths of the columns declared for the index plus
the sum of the lengths of the columns of the primary key of the underlying table.
•
The maximum length for the rows of a unique index is 508 bytes. The rows can
include 253 bytes for the KEYTAG column and indexed columns and 255 bytes for
the primary key of the underlying table.
•
For varying-length columns (VARCHAR, NCHAR VARYING), the length referred to
in these limits is the defined column length, not the stored length. The stored
length includes two additional bytes in which the RDBMS records the data length
of the item. For example, if the index includes VARCHAR columns, the actual
stored record length would be two bytes greater for each VARCHAR column than
the defined column length.
•
If there are ordering requirements, consider defining the sequence of columns so
that it meets those requirements. Otherwise, a sort will be necessary to fulfill the
ordering requirements.
•
If an index is unique, define it as unique. SQL can access the index more efficiently
if the index is unique and specify equality predicates on all index columns.
•
In general, do not explicitly include primary key columns in an alternate index.
These columns are already stored at the end of the index. However, if primary key
columns are used for positioning or in an ORDER BY clause, consider including
those columns as part of the alternate index. This approach might avoid a sort
operation.
•
If you frequently access a set of columns that is almost contained within your
index, consider adding the remaining columns to the alternate index to create
index-only access for such queries. This approach increases storage requirements
and update processing of those columns, so you should evaluate these trade-offs.
•
If you access a set of information—the same values in several rows, such as all
names equal to Smith—consider using primary-key access for that data instead of
alternate index access.
Defining the Key for an Index
The primary key for an index file includes these columns:
•
KEYTAG—a unique identifier for an index on a base table. KEYTAG is a two-byte
column that can contain either two characters or data of type SMALLINT
UNSIGNED with values from 1 through 65,535. All rows of a given index have the
same KEYTAG value. The KEYTAG values can either be user specified or system
generated, but each value must be unique among the set of KEYTAGS defined on