NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-139
Considerations—CREATE INDEX
While the index is being created, other processes can execute SELECT statements
and read-only utility operations on the table, except during the final phase of the
operation, when no access by other processes is allowed.
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. (See DDL (Data Definition Language)
Statements on page D-19 for more information.)
•
Performance considerations
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.
•
Failure considerations
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-5.
•
TMF audit trail requirements