SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
7-32
Creating an Index
Creating an Index
To create an index, use the CREATE INDEX statement. If the underlying table contains
data, the creation process automatically loads the index unless you specify NO
POPULATE.
When you define an alternate index, first consider the column-related guidelines
described under Defining Table Columns on page 7-20.
When you create an index, specify UNIQUE to ensure that the values (including the
null values) in the column or set of columns that make up the index field cannot contain
more than one occurrence of the same value or set of values. For indexes with multiple
columns, the values of the columns as a group determine uniqueness, not the values
of the individual columns. If you do not specify UNIQUE, duplicate values are allowed.
The columns specified for the index are not required to be declared NOT NULL. When
you create a nonunique index, column values can be duplicated. Index uniqueness is
achieved by attaching the clustering key value of the table. A nonunique index consists
of the columns specified in CREATE INDEX and the clustering key. For more
information about unique and nonunique indexes, see the SQL/MX Reference Manual.
In a unique index, key length is determined solely by the length of the columns you
specify. In a nonunique index, the length of your largest clustering key is determined by
the length of the index columns and the clustering key. Clustering key length must
therefore be considered in planning the size of nonunique indexes.
Also consider primary key definitions, as noted in Section 4, Understanding and
Planning SQL/MX Tables, and Creating and Using Keys on page 7-11. Determine if
the clustering key is the most appropriate based on actual use of the table.
Guidelines for Creating Indexes
•
If you are creating an index on a large table that is already populated, use the NO
POPULATE option and then run the POPULATE INDEX utility to load the index.
Because CREATE INDEX executes in a single TMF transaction, it could
experience TMF limitations (for example, transaction timeout) if a large amount of
data is to be moved. For information about the POPULATE INDEX utility, see the
SQL/MX Reference Manual.
•
CREATE INDEX locks out INSERT, DELETE, and UPDATE operations on the table
being indexed. If other processes have their table rows locked when the operation
begins, CREATE INDEX waits until its lock request is granted or a timeout occurs.
•
Define index columns with NOT NULL NOT DROPPABLE to save extra bytes of
storage space for each column.
•
Indexes you create before a table is loaded are loaded automatically as the table is
loaded.
•
Indexes are automatically loaded in parallel.