NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-138
Considerations—CREATE INDEX
Attributes marked with an asterisk (*) default to the same value as the
corresponding attribute in the underlying table. For more detail, see the entry for a
specific attribute.
Considerations—CREATE INDEX
•
Authorization and access requirements
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 UDPATE 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.
CLEARONPURGE* Controls disk erasure when file is dropped.
DCOMPRESS* Controls key compression in data blocks.
DSLACK Sets percent of slack in data blocks. Default is value of the
SLACK attribute.
EXTENT Sets extent sizes. Default is 16 pages for the first extent, 64 for
others.
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.