Index Storage Structure

By default, indexes are created with an isam storage structure. There are two methods to override this default:

• To specify the default index storage structure for indexes created during the session, use the -n flag when issuing the command that opens the session (sql, isql, or connect). For more information about this flag, see the System Administrator Guide.
• To override the session default when creating an index, specify the desired storage structure using the structure option when issuing the create index statement.

To specify whether the index is to be compressed, use the with [no]compression clause. By default, indexes are not compressed. If with compression is specified, the structure clause must be specified. An rtree index cannot be compressed. To change the storage structure of an index, use the modify statement. For details about table storage structures, see Modify.

Unique Indexes

To prevent the index from accepting duplicate values in key fields, specify the unique option. If the base table on which the index is being created has duplicate values for the key fields of the index, the create index statement fails. Similarly, if an insert or update is attempted that violates the uniqueness constraint of an index created on the table, the insert or update fails. This is true for an update statement that updates multiple rows: the update statement fails when it attempts to write a row that violates the uniqueness constraint.

Effect of the Unique_Scope Option on Updates

The unique_scope option can affect the outcome of an update. For example, suppose you create an index on the employee numbers in an employee table, and the table contains employee numbers in sequence from 1 to 1000. If you issue an update statement that increments all employee numbers by 1, uniqueness is checked according to the unique_scope option as follows:

• UNIQUE_SCOPE = ROW - Employee number 1 is incremented to 2. The row is checked for uniqueness-of course, employee number 2 already exists. Result: the update fails.
• UNIQUE_SCOPE = STATEMENT - Employees 1 through 1000 are incremented before uniqueness is checked. All employee numbers remain unique. Result: the update succeeds.

Index Location

Location_name refers to the areas where the new index is created. The location_names must be defined on the system, and the database must have been extended to the corresponding areas. If no location_name is specified, the index is created in the default database area. If multiple location_names are specified, the index is physically partitioned across the locations. For more information about creating locations and extending databases, see the Database Administrator Guide.

Parallel Index Building

Use parallel index to more efficiently create indexes in parallel. Each of these indexes can also be marked as persistent, which means that if the underlying base structure of the table is reorganized (or modified), the indexes are recreated automatically.

Note: Unique cannot be specified before both the index keyword and with an individual index specification. If unique is used before index, all the indexes being created are unique indexes. See the Examples included later in this section.