Using CREATE INDEX

Use the CREATE INDEX statement to create an index on one or more columns in a table and, optionally, to cluster the physical table in the order of the index. This section describes some of the options available when you create indexes. For more information about the CREATE INDEX statement, see the IBM Informix: Guide to SQL Syntax.

Suppose you create table customer:

CREATE TABLE customer
(
   cust_num   SERIAL(101) UNIQUE
   fname      CHAR(15),
   lname      CHAR(15),
   company    CHAR(20),
   address1   CHAR(20),
   address2   CHAR(20),
   city       CHAR(15),
   state      CHAR(2),
   zipcode    CHAR(5),
   phone      CHAR(18) 
);

The following statement shows how to create an index on the lname column of the customer table:

CREATE INDEX lname_index ON customer (lname);

Composite Indexes

You can create an index that includes multiple columns. For example, you might create the following index:

CREATE INDEX c_temp2 ON customer (cust_num, zipcode);

Bidirectional Traversal of Indexes

The ASC and DESC keywords specify the order in which the database server maintains the index. When you create an index on a column and omit the keywords or specify the ASC keyword, the database server stores the key values in ascending order. If you specify the DESC keyword, the database server stores the key values in descending order.

Ascending order means that the key values are stored in order from the smallest key to the largest key. For example, if you create an ascending index on the lname column of the customer table, last names are stored in the index in the following order: Albertson, Beatty, Currie.

Descending order means that the key values are stored in order from the largest key to the smallest key. For example, if you create a descending index on the lname column of the customer table, last names are stored in the index in the following order: Currie, Beatty, Albertson.

The bidirectional traversal capability of the database server lets you create just one index on a column and use that index for queries that specify sorting of results in either ascending or descending order of the sort column.