Informix Guide to SQL: Syntax SQL Statements

Use the CREATE INDEX statement to create an index for one or more columns in a table, to specify whether or not it allows only unique values, to cluster the physical table in the order of the index, and to designate where the index should be stored.

A secondary access method (sometimes referred to as an index access method) is a set of database server functions that build, access, and manipulate an index structure such as a B-tree, R-tree, or an index structure that a DataBlade module provides. Typically, a secondary access method speeds up the retrieval of data.

When you issue the CREATE INDEX statement, the table is locked in exclusive mode. If another process is using the table, the database server cannot execute the CREATE INDEX statement and returns an error.

If you are using Enterprise Decision Server, use the USING BITMAP keywords to store the list of records in each key of the index as a compressed bitmap. The storage option is not compatible with a bitmap index because bitmap indexes must be fragmented in the same way as the table.

The index-type options let you specify the characteristics of the index.

Use the UNIQUE or DISTINCT keywords to require that the column or set of columns on which the index is based accepts only unique data.

The following example creates a unique index:

CREATE  UNIQUE  INDEX  c_num_ix  ON  customer  (customer_num) 

A unique index prevents duplicates in the customer_num column. A column with a unique index can have, at most, one null value. The DISTINCT keyword is a synonym for the keyword UNIQUE, so the following statement accomplishes the same task:

CREATE  DISTINCT  INDEX  c_num_ix  ON  customer  (customer_num) 

The index in either example is maintained in ascending order, which is the default order.

If you do not specify the UNIQUE or DISTINCT keywords in a CREATE INDEX statement, the database server allows duplicate values in the indexed column.

You can also prevent duplicates in a column or set of columns by creating a unique constraint with the CREATE TABLE or ALTER TABLE statement. For more information on creating unique constraints, see the CREATE TABLE or ALTER TABLE statements.

The database server creates internal B-tree indexes for primary-key, unique, and referential constraints. If a primary-key, unique, or referential constraint is added after the table is created, any user-created indexes on the constrained columns are used, if appropriate. An appropriate index is one that indexes the same columns that are used in the primary-key, referential or unique constraint. If an appropriate user-created index is not available, the database server creates a nonfragmented internal index on the constrained column or columns.

Use the CLUSTER option to reorder the physical table in the order that the index designates. The CREATE CLUSTER INDEX statement fails if a CLUSTER index already exists.

CREATE  CLUSTER  INDEX  c_clust_ix  ON  customer  (zipcode) 

This statement creates an index on the customer table that physically orders the table by zip code.

If the CLUSTER option is specified in addition to fragments on an index, the data is clustered only within the context of the fragment and not globally across the entire table.

If you are using Enterprise Decision Server, you cannot use the CLUSTER option on STANDARD tables. In addition, you cannot use the CLUSTER option and storage options in the same CREATE INDEX statement (see Storage Options). When you create a clustered index the constrid of any unique or referential constraints on the associated table changes. The constrid is stored in the sysconstraints system catalog table.

Some secondary access methods (such as R-tree) do not support clustering. Before you specify CLUSTER for your index, be sure that it uses an access method that supports clustering.

Use the Index-Key Specification portion of the CREATE INDEX statement to specify the key value for thes A, B, and C each have col1 as a primary key. In the following example, B is joined on key to A and C is joined on key to B. C is transitively joined on key to A.

CREATE GK INDEX gki 
(SELECT A.col1, A.col2 FROM A, B, C
  WHERE A.col1 = B.col1 AND B.col1 = C.col1)

The WHERE clause for a GK index has the following limitations:

• The clause cannot include USER, TODAY, CURRENT, DBINFO built-in functions or any functions that refer to time or a time interval.
• The clause cannot refer to any SPL routine.
• The clause cannot have any subqueries.
• The clause cannot use any aggregate function.
• The clause cannot have any IN, LIKE, or MATCH clauses.

Related statements: ALTER INDEX, CREATE OPCLASS, CREATE TABLE, DROP INDEX, and SET Database Object Mode

For a discussion of the structure of indexes, see your Administrator's Reference.

For a discussion on the different types of indexes and information about performance issues with indexes, see your Performance Guide.

For a discussion of the GLS aspects of the CREATE INDEX statement, see the Informix Guide to GLS Functionality.

For information about operator classes, refer to the CREATE OPCLASS statement and Extending Informix Dynamic Server 2000.

For information about the indexes provided by DataBlade modules, refer to your DataBlade module user's guide.