SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

The use of an alternate index does not ensure that the optimizer will choose the index as the access
path. Index use depends highly on selectivity, described in the SQL/MX Query Guide. In general,
these guidelines apply:
• If the query can be satisfied with an index-only access, the optimizer normally uses the index.
• Low selectivity of a predicate means that many rows are selected. For base table access
through an index, the optimizer performs a random access read against the table. If predicate
selectivity becomes too low, the optimizer scans the table instead of using the index.
Use the EXPLAIN function, which is described in the SQL/MX Query Guide, to determine if the
extra index will be used by NonStop SQL/MX for a particular query. Queries that update index
columns incur the overhead of having one more index to update.
Defining an Index
When you define an index, consider these guidelines:
• For unique indexes, the sum of the lengths of the columns in the index cannot exceed 2048
bytes.
• For nonunique indexes, the sum of the lengths of the columns in the index plus the sum of the
length of the clustering key of the underlying table cannot exceed 2048 bytes.
• 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 a few
extra 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 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
38 Understanding and Planning SQL/MX Tables