SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-149
Considerations—CREATE INDEX
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.)

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.

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. For information, see Limits on
page L-6.
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.

These considerations apply to index partition formats:

The default partition format is based on the partition array value of the
underlying table. If the partition array of the underlying table is STANDARD or
EXTENDED, the default partition format is 1. If the partition array of the
underlying table is FORMAT2ENABLED, the default partition format is 2. For
relative and entry-sequenced tables, the partition format is always 1 because
such tables cannot have a FORMAT2ENABLED partition array.

An index inherits its partition array value from the underlying table. Because
there is no command to explicitly set the partition array value for an index, a
table must be Format 2-enabled before any of its index partitions can be
Format 2.

When you create a table with a FORMAT2ENABLED partition array, the table
will have an object version of at least version 350. Therefore, all partitions of