Valid in: QUEL, EQUEL

Creates an index on an existing table.

This statement has the following format:

The optional with_clause must consist of a comma-separated list of one of more of the following:

The index statement creates an index on an existing table. Indexes can make retrieval and updating more efficient. A key is constructed from base table columns in the order you specify. A maximum of 32 columns can be specified per index. You can create any number of indexes for a base table.

Locationname specifies the location of the index you are creating; the location must exist, and the database must have been extended to the location. If no locationname is specified, the index is created in the default database location. If multiple locationnames are specified, the index is physically partitioned across the areas.

Structure is specified using the with structure option. The default index structure is isam. To specify the default index structure, use the -n flag when you invoke QUEL. To modify the storage structure of indexes, use the modify statement.

If key=(column list) is specified, the columns in column list must be an ordered subset of the leading columns specified in the index definition. For example, an index defined on columns "a," "b," "c" and "d" can be keyed on "a", or "ab," or "abc" or "abcd." (The default is "abcd" if the key clause is omitted.)

Indexes cannot be directly updated. When a primary table is changed, its indexes are automatically updated by the system. To minimize the time it takes to update a table, limit the number of indexes.

If you modify or destroy a primary table, indexes on the primary table are destroyed. You can modify and destroy an index (an index is also a table).

For details about the with clause options, see MODIFY--Change Table or Index Properties.