2.7 CREATE INDEX

---

Description

Creates an index on the specified table using the specified columns of the table. An index improves the performance of SQL operations whose predicates are based on the indexed column. However, an index slows performance of INSERT, DELETE and UPDATE operations.

Syntax

CREATE [ UNIQUE ] INDEX index_name

ON table_name

( {column_name [ASC | DESC]} [, ...])

[ PCTFREE number]

[ STORAGE_ATTRIBUTES `attributes' ]

[ TYPE `ix_type' ];

Arguments

UNIQUE
A UNIQUE index will not allow the table to contain any rows with duplicate column values for the set of columns specified for that index.

index_name
The name of the index has to be unique within the local database.

table_name
The name of the table on which the index is being built.

column_name [, ...]
The columns on which searches and retrievals will be ordered. These columns are called the index key. When more than one column is specified in the CREATE INDEX statement a concatenated index is created.

ASC | DESC
The index can be ordered as either ascending (ASC) or descending (DESC) on each column of the concatenated index. The default is ASC.

PCTFREE number
Specifies the desired percentage of free space for a index. The PCTFREE clause indicates to the storage system how much of the space allocated to an index should be left free to accommodate growth. However, the actual behavior of the PCTFREE clause depends entirely on the underlying storage system. The SQL engine passes the PCTFREE value to the storage system, which may ignore it or interpret it. If the CREATE statement does not include a PCTFREE clause, the default is 20. See the documentation for your storage system for details.

STORAGE_ATTRIBUTES 'attributes'
A quoted string that specifies index attributes that are specific to a particular storage system. The SQL engine passes this string to the storage system, and its effects are defined by the storage manager. See the documentation for your storage system for details.

TYPE 'ix_type'
A single-character that specifies the type of index. The valid values for the TYPE argument and their meanings are specific to the underlying storage system. See the documentation for your storage system for details.

Example

CREATE UNIQUE INDEX custindex ON customer (cust_no) ;

Authorization

The user executing this statement must have any of the following privileges:

DBA privilege
Ownership of the index.
INDEX privilege on the table.

SQL Compliance	ODBC Core SQL grammar. Extensions: PCTFREE, STORAGE_ATTRIBUTES, and TYPE
Environment	Embedded SQL, interactive SQL, ODBC applications
Related Statements	CREATE TABLE, DROP INDEX, ALTER INDEX