SQL/MP Installation and Management Guide

Understanding and Planning Database Tables
HP NonStop SQL/MP Installation and Management Guide—523353-004
3-18
Determining When to Use Indexes
Evaluating the Benefit 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 if the following are true:
•
The number of rows retrieved is small.
•
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 cause a sort operation if indexes are not available.
•
An application has many queries that refer to 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.
Columns used as selection criteria, but that are also frequently updated, might not
improve overall performance as an 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/MP
Query Guide. In general, these guidelines apply:
•
If the query can be satisfied with an index-only access, the optimizer uses the
index.
•
For base-table access through an index, the optimizer performs a random access
read against the base table. If index selectivity becomes too high, the optimizer
scans the base table instead of using the index.
You can use the EXPLAIN facility (described in the SQL Query Guide) to determine if
the extra index will be used by SQL for a particular query. Furthermore, each additional
index adds overhead during updates. Queries that update index columns incur the
overhead of having one more index to update.
Note. After you create an index for a table, run UPDATE STATISTICS. Otherwise, SQL returns
a warning for subsequent operations that access that table.