SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-147
Considerations—CREATE INDEX
Attributes marked with an asterisk (*) default to the same value as the
corresponding attribute in the underlying table. For information, see the entry for a
specific attribute.
Considerations—CREATE INDEX

Note that CREATE INDEX effectively invalidates online dumps of the table
underlying the new index. To ensure TMF file-recovery protection, make new
online dumps of all partitions of the table and its indexes. For information about
online dumps, see the SQL/MP Installation and Management Guide.

To create an index, you must be a generalized owner of the underlying table. You
must also have authority to read and write to the underlying table, authority to write
to the USAGES table of catalogs that describe the table, and authority to write to
catalogs that receive the description of the index and partitions of a partitioned
index, and catalogs of dependent programs.
The underlying table and any protection views declared on that table must be
accessible at the time an index is created. If the table is partitioned, all partitions
must be accessible.
If you omit WITH SHARED ACCESS, CREATE INDEX locks out write operations
(including INSERT, DELETE, and UPDATE operations) on the table being indexed
throughout the operation. If other processes have rows in the table locked when
the operation begins, CREATE INDEX waits until its lock request is granted or
timeout occurs. If other processes are performing cursor SELECT or set-oriented
INSERT, UPDATE, or DELETE operations on the table and the CREATE INDEX
statement does not specify NO INVALIDATE, CREATE INDEX preempts those
processes to acquire its own lock, causing error 60 or error 8204 in the preempted
processes. 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.
ICOMPRESS* Controls key compression in index blocks.
ISLACK Sets percent of slack in index blocks. Default is value of the
SLACK attribute.
LOCKLENGTH Sets number of leading bytes in the key to use for generic
locks. Default is 0, which specifies the entire key.
MAXEXTENTS Sets maximum extents. Default is 160.
NOPURGEUNTIL Sets date after which drop is allowed. Default allows
immediate drop.
SERIALWRITES* Specifies serial or parallel writes.
SLACK Sets percent of slack in blocks if not specified by DSLACK or
ISLACK. Default is 15 percent.
TABLECODE* Sets tablecode. Default is 0.
VERIFIEDWRITES* Controls verification of writes to disk.