SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-148
Considerations—CREATE INDEX
If you specify WITH SHARED ACCESS, CREATE INDEX does not lock out
INSERT, DELETE, and UPDATE operations to the table being indexed except for a
relatively brief period during the final phase. CREATE INDEX does not preempt
other processes to acquire its lock even then. In addition, WITH SHARED
ACCESS includes a COMMIT option that allows you to control when the operation
starts the commit phase and whether to retry errors such as time outs during lock
requests.
You cannot perform other DDL operations on the table being indexed until the
CREATE INDEX operation finishes, with or without WITH SHARED ACCESS.
An index inherits the OWNER, SECURE, and AUDIT file attributes from its
underlying table. The security of the underlying table must authorize network
access if the index is to be partitioned across nodes or if the index is created on a
node different from the node on which the table resides.
Only one DDL statement can operate on a given SQL object (or partition of an SQL
object) at a time. An error occurs if you attempt to execute a CREATE INDEX
statement while another process is executing a DDL operation on the same object.
The specific error depends on the DDL operation involved and the phase of the
operation at which the conflict occurs. (For information, see DDL (Data Definition
Language) Statements on page D-20.)

CREATE INDEX operations that use WITH SHARED ACCESS generally take
longer to complete than those that do not. However, because WITH SHARED
ACCESS operations allow concurrent read and write access to the source
partition, they cause far less application downtime than equivalent operations
without WITH SHARED ACCESS.
The duration of a WITH SHARED ACCESS operation increases with the number
and length of transactions on the node that contains the source partition,
particularly with the number and length of transactions that involve the source
partition and the amount of activity on the audit trail used for the source partition.

If a CREATE INDEX operation terminates abnormally, you (or another user with
access to the super ID) must remove the new index with CLEANUP. If the
operation used the WITH SHARED ACCESS option to index a table with the
AUDITCOMPRESS file attribute (the default), you must also use ALTER TABLE to
reset the attribute.
When you create an index with a large number of partitions, the PARTNS catalog
table and associated IXPART01 index might become full. To correct the situation,
distribute object and partition definitions across multiple catalogs. For more
information about partition limits, see Limits
on page L-6.

An operation that uses WITH SHARED ACCESS cannot complete successfully
unless the TMF audit trail generated during the operation is available for reading
later in the operation. If a required audit trail has been overwritten, a WITH
SHARED ACCESS operation cancels changes made to the database and
terminates.