Since all table columns are treated in the same way, each can be used as a search criterion. This does not mean, however, that they are equally efficient.
If a column is preferred for making conditions for a search or an update, it is recommendable to create an index file for it. This file helps to find the table rows more quickly.
A single-column index on the column 'name' of the table 'customer' is to be created. Two syntactical variants are provided for this purpose. In the first case, an index named 'name_idx' is created; in the second case, the unnamed index is identified using the table name and column name.
CREATE INDEX name_idx on customer (name)
CREATE INDEX customer.name
An index can refer to more columns; then it is called a multiple index and must be named.
CREATE INDEX name_idx on customer (name, firstname)
To create an index which, like the key (see Section 'Accessing Single Rows'), ensures uniqueness, the keyword UNIQUE must be specified.
The definition of a UNIQUE index can be included in the table definition. Although uniqueness would be too restrictive for the name, the two examples above would look like this:
CREATE TABLE customer (cno FIXED (4) ...
title
name CHAR (8) UNIQUE,
firstname
)
CREATE TABLE customer (cno FIXED (4) ...
title
name
firstname ...
UNIQUE (name,firstname)
)
In the second case, the database generates a name of its own, INDEX01.