SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

A second use of an index is to eliminate a run-time sort of data columns by providing an access
path to the data in the order of the desired selection or by reverse order:
SELECT A,B FROM ATABLE
ORDER BY A, B;
A third use of an index is to avoid a full table scan:
SELECT ITEM_NAME, RETAIL_PRICE
FROM INVNTRY
WHERE RETAIL_PRICE = 100;
Without an index on RETAIL_PRICE and assuming RETAIL_PRICE is not the leading column on the
table’s clustering key, NonStop SQL/MX must scan the table and evaluate the predicate that follows
against each of the rows in the table. An index on RETAIL_PRICE would improve query performance
dramatically.
An index on RETAIL_PRICE might not help the next query, which contains an inequality predicate,
however, because ITEM_NAME is not part of the index. Also, if the selection predicate has high
selectivity (for example, relatively few rows have a RETAIL_PRICE of more than 100), the query
can still benefit from using an index instead of a full base table scan:
SELECT ITEM_NAME, RETAIL_PRICE
FROM INVNTRY
WHERE RETAIL_PRICE > 100;
To retrieve the ITEM_NAME values for the selected rows, NonStop SQL/MX bundles many of the
rows’ base table clustering key into a file system request. Within the disk process, the ITEM_NAME
columns are read from the base table by using random access.
If the query selects only columns included in the index, the index on RETAIL_PRICE can help.
Including ITEM_NAME in the index could result in index-only access, which would improve the
performance of this query.
Evaluating the Benefits of a New Index
Creating alternate indexes can help the performance of some, but not all, queries. In some
applications, determining when an index could be efficient might be easier than in others. Examine
the WHERE clauses and ORDER BY clauses of the SELECT statements in your application. You
should consider creating indexes on only the most frequently used columns.
In general, indexes are an efficient way to access data when:
• The number of rows retrieved is small with respect to the total number of rows.
• 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.
38 Understanding and Planning SQL/MX Tables