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);
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);
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.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]