NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-140
Examples—CREATE INDEX
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.
When performed on a base table whose partitions have valid TMF online dumps, a
CREATE INDEX operation that uses WITH SHARED ACCESS generates audit
information for each of the new index partitions. The index partitions might not
audit to the same audit trail as the source.
In addition, a CREATE INDEX operation that uses WITH SHARED ACCESS turns
off the AUDITCOMPRESS file attribute for the table being indexed for the duration
of the operation. This increases the amount of audit information for the table during
this period.
Lengthy operations that use WITH SHARED ACCESS might require an operator to
mount tapes of previously taken TMF audit dumps. (Requests to mount TMF audit
dump tapes for WITH SHARED ACCESS operations are not distinguishable from
other requests to mount TMF audit dump tapes. Such requests are generally sent to
an operator's console. SQL does not return information about such requests to the
terminal or process that started the operation.)
•
Index row storage order
Rows in an index are stored in ascending or descending order, as defined with
CREATE INDEX, for the first column and subsequent columns of the index. For
sorting purposes, null values are considered greater than all other values.
If multiple index rows share the same value for the first column, SQL uses values in
the second column to order the rows, and so forth. If duplicate index rows occur in a
nonunique index, SQL stores duplicate index key values in ascending or descending
order, depending on the sequence specified for the columns of the primary key of
the underlying table.
•
Limits on number of indexes and partitions
There is a limit on the number of indexes that can exist for a table and on the
number of partitions that can exist for an index. See Limits
on page L-5 for more
information.
The type of partition array associated with an index affects these limits. The
partition array for an index is always the same type as that associated with the
underlying base table.
Examples—CREATE INDEX
•
The following example creates an index on the LAST_NAME and FIRST_NAME
columns of table EMPLOYEE:
CREATE INDEX \SYS1.$VOL1.PERSNL.EMPLOYE0 ON
\SYS1.$VOL1.PERSNL.EMPLOYEE (LAST_NAME, FIRST_NAME) CATALOG
\SYS1.$VOL1.PERSNL;