SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-5
Adding Indexes
To test the effect of an index on performance, follow these steps:
1. Test a sample set of queries against the production tables by using the DISPLAY
STATISTICS command to obtain the statistical information.
2. Duplicate the table or tables involved to a test location.
3. Create the new index.
4. Enter an UPDATE STATISTICS statement to update the statistical information
stored in the catalog and get statistics on the new index. The CREATE INDEX
statement does not automatically update statistical information.
5. SQL compile (recompile) any programs that use the table with the EXPLAIN option
to determine whether the index is the chosen path.
6. Test the same queries against the tables by using DISPLAY STATISTICS to obtain
the new statistical information.
7. Determine any improvement in performance.
8. If the query execution plans include using the new index and if you determine that
the performance improvement is sufficiently advantageous over the increased
system overhead of maintaining the index, add the index to the production
database.
9. If you add the index, recompile programs that use the table.
Consider these guidelines when adding an index to an existing table:
•
You should not create an index on a loaded table within a user-defined TMF
transaction because the transaction could overflow the TMF audit trails and cause
an error. The CREATE INDEX operation automatically initiates several TMF
transactions, as necessary, but loads the index outside a TMF transaction. With
this loading technique and the automatic transactions, the operation minimizes the
TMF overhead of a potentially very long transaction and reduces output to the
audit trails.
•
Index creation can be a long operation, depending on the size of the table and the
load on the system. Therefore, two locking strategies are available:
°
The default locking strategy acquires a shared table lock on the underlying
table. The shared table lock ensures that no users can modify rows during the
creation of the index. This lock can prohibit access to the table by other users
that make write requests.
°
The WITH SHARED ACCESS option for the CREATE INDEX statement allows
access to the table for DML operations during all but the short final stage of
index creation. The option includes a reporting feature for monitoring index
creation. In addition, you can request a time window or request explicit
operator authorization for the final stage of index creation that requires table
locking.