SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

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
“Managing Table Data” (page 88).
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,
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 “Understanding and Planning SQL/MX Tables”
(page 29), and “Creating and Using Keys” (page 81). 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 more information, 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.
• Indexes can be updated in parallel by the disk process while the table is being updated. To
take full advantage of parallel updating, you should create a table’s indexes on separate disk
volumes, with each disk volume configured for a separate processor. The performance effects
of parallel updates are discussed in “Enhancing SQL/MX Database Performance” (page 293).
• You should perform an UPDATE STATISTICS on the table before populating the index using,
at a minimum, the ON EVERY KEY option.
• Index creation can be a long operation, depending on the size of the table and the load on
the system. NonStop SQL/MX supports only default locking, which requires a shared table
lock on the underlying table. The shared table lock ensures that no users can modify rows
during the creation of the index. This lock can prohibit access to the table by other users.
• All SQL/MX indexes that are not created with the NO POPULATE option are audited.
Immediately after creating an audited index, make a TMF online dump of the index to prepare
for possible file recovery, which might be faster than rebuilding the index.
Creating Indexes for SQL/MX Tables 97