SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-6
Adding Indexes
The WITH SHARED ACCESS option can be used in conjunction with the
PARALLEL EXECUTION ON option if the initiating node, all nodes with base
table partitions, and all nodes that will have index partitions are running version
315 or later of SQL/MP software.
For more information about the WITH SHARED ACCESS option and concurrent
access to tables by multiple users, see Understanding the Implications of
Concurrency on page 14-1 and the “WITH SHARED ACCESS” description in the
SQL/MP Reference Manual.
•
For an audited index, make a TMF online dump of the index immediately after
creating it to prepare for possible file recovery, which might be faster than
rebuilding the index.
For additional guidelines related to index creation, including performance-related
considerations, see Creating Indexes on Base Tables on page 5-42.
Validation Considerations
Adding a new index invalidates the programs that depend on the underlying table
unless you use one of these:
•
The NO INVALIDATE option in the CREATE INDEX statement
•
The CHECK INOPERABLE PLANS compiler option, described under Using
Similarity Checks on page 10-15, with similarity checking enabled for the index
If you do not use one of the preceding options, you should include steps to explicitly
SQL compile the dependent programs to avoid automatic recompilation and to return
the application to a valid state.
The creation of the index does not automatically update the table’s statistics, which the
SQL compiler uses to determine the best access path. You should always follow the
creation of an index with the UPDATE STATISTICS statement to ensure that the table’s
statistics are current. If the statistics are incorrect, the SQL compiler might not choose
the most efficient access path.
Note. SQL tables and indexes with many partitions (typically around 400) might cause
SQLCAT, SQLUTIL, or AUDSERV processes to incur file-system error 31 or 34 or cause the
PARTNS catalog table and its associated index, IXPART01, to become full. For more
information about this situation, see Creating Table Partitions on page 5-32.