CREATE INDEX

Description

Creates an index on the specified table using the specified columns of the table. An index improves the performance of c-treeACE 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]} [, … ])

       [ STORAGE_ATTRIBUTES 'attributes' ];

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.

STORAGE_ATTRIBUTES 'attributes'

A quoted string that specifies index attributes that are specific to c-treeACE SQL indexes. c-treeACE SQL interprets the following attribute strings:

• ''PARTITION' - Creates the index as the partition index to the table enabling multiple table partitions. This option can be specified after a table has been created, and the table will be rebuilt according to the partition rule in effect.

To combine STORAGE_ATTRIBUTE options, separate them with a semicolon (;).

Example

CREATE UNIQUE INDEX postdate_idx ON customer (invoice_date) STORAGE_ATTRIBUTES 'partition' ;

Authorization

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

• DBA privilege.
• Ownership of the index.
• INDEX privilege on the table.
• EXCLUSIVE file access is required for any this operation.

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