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-15
Determining When to Use Indexes
•
The result is presented in a certain order or grouped according to certain columns,
such as queries that use a DISTINCT, GROUP BY, ORDER BY, or UNION clause,
which can require a sort operation if indexes are not available.
•
An application has many queries that reference a column in a table.
•
All the necessary information can be obtained from the index (index-only access).
•
The column is an argument of the MIN or MAX function.
Index performance is affected by the ratio of INSERT, UPDATE, and DELETE
operations to the use of the index, either as a selection predicate for selecting rows or
to satisfy ORDER BY. For example, an index might be built to support one execution of
a SELECT statement. However, the benefit to the SELECT statement would be far
outweighed by the processing required to build the index.
When a column that is also an index column is updated, both the table and index
require updating. The system automatically updates the index when it updates the
table. Updating the index slightly degrades performance for the update operation. The
index, however, might improve performance for the selection operation.
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 255 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 255
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