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-16
Determining When to Use Indexes
the item. For example, if the index includes VARCHAR columns, the actual stored
record length would be a few bytes larger 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. NonStop SQL/MX can access the index
more efficiently if the index is unique and you specify equality predicates on all
index columns.
•
To create a REFERENTIAL INTEGRITY constraint on a table, you must have a
UNIQUE or PRIMARY KEY constraint defined on the column or set of columns that
make up the referenced column list. If the referenced column list does not exist,
the column list associated with the PRIMARY KEY is used. A UNIQUE constraint is
not sufficient.
•
In general, do not explicitly include clustering key columns in an alternate index.
These columns are already stored at the end of the index. However, if you do use
clustering key columns 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 clustering-key access for that data instead
of alternate index access.
Defining the Key for an Index
The clustering key for an index file includes these columns:
•
Indexed columns: the columns located in the column list in the CREATE INDEX
statement.
•
For nonunique indexes: columns of the clustering key of the underlying table.
These columns are required to identify rows uniquely in the index. The clustering
key of a nonunique index automatically includes the columns of the clustering key
of the underlying table to associate the indexed columns with the rows of the table.
In a unique index, the columns of the clustering key of the underlying table are not
included in the clustering key of the index, but are physically included in the index
file.
The clustering key for an index can contain columns with null values. However, two null
values in the same column are treated as equal values for purposes of unique
constraint violation checking.