SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-45
Creating an Index
•
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:
°
Default locking requires 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.
°
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.
°
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. The WITH SHARED ACCESS option, when used with the
CREATE INDEX statement, allows you to take the online dump while the CREATE
INDEX operation is progressing.
•
Creating an index invalidates any registered programs that use the underlying
table unless you use one of these:
°
The CHECK INOPERABLE PLANS compiler option, described in Using
Similarity Checks on page 10-15, with similarity checking enabled for the index.
The use of similarity checking is the recommended method for avoiding
automatic recompilations.
°
The NO INVALIDATE option in the CREATE INDEX statement
An invalidated program can be executable, but you must explicitly SQL compile the
program to revalidate it. If you do not use similarity checking, or if the similarity
check does not succeed, you must recompile to avoid automatic recompilation.
•
You can influence the optimizer’s choice of index by using the CONTROL QUERY
directive. For more information, see the SQL/MP Query Guide and the SQL/MP
Reference Manual.
•
If you are running SQL/MP on a system using the SMF product, you can specify a
virtual volume for the index. The virtual volume is associated with a storage pool;
SMF places the index file on a physical volume in that storage pool. SMF chooses