CREATE INDEX

The CREATE INDEX statement creates an index on one or more columns of a table and assigns a name to the new index.

Scope

ISQL or Application Programs

SQL Syntax

  CREATE [UNIQUE][CLUSTERING]INDEX [Owner.]Indexname ON
  [Owner.]TableName ({ColumnName[ASC 
                                 DESC]}[,...])

Parameters

UNIQUE

prohibits duplicates in the index. If UNIQUE is specified, each possible combination of index key column values can occur in only one row of the table. If UNIQUE is omitted, duplicate values are allowed. Because all null values are equivalent, a unique index allows only one row with a null value in an indexed column. When you create a unique index, all existing rows must have unique values in the indexed column(s).

CLUSTERING

can increase the efficiency of sequential processing.

If CLUSTERING is specified, rows added to the table after the index is created are placed physically near other rows with similar key values whenever space is available in the page. If CLUSTERING is omitted, the key values in a newly inserted row do not necessarily have any relationship with the row's physical placement in the database.

No more than one index for a table can have the CLUSTERING attribute.

If the table was declared to use a HASH structure, no clustering indexes may be defined upon it. See the CREATE TABLE statement for information on HASH structures.

[Owner.]IndexName

is the name to be assigned to the new index. A table cannot have two indexes with the same name. If the owner is specified, it must be the same as the owner of the table. The default owner name is the owner name of the table it is being defined on. The usual default owner rules do not apply here.

[Owner.]TableName

designates the table for which an index is to be created.

ColumnName

is the name of a column to be used as an index key. You can specify up to 16 columns in order from major index key to minor index key. The data type of the column cannot be a LONG data type.

ASC | DESC

specifies the order of the index to be either ascending or descending, respectively. The default is ascending. Specifying DESC does not create a descending index. It is the same index as ascending. Therefore, SELECT statements that require data to be retrieved in descending order must specify ORDER BY columnID DESC.

Description

If the table does not contain any rows, the CREATE INDEX statement enters the definition of the index in the system catalog and allocates a root page for it. If the table has rows, the CREATE INDEX statement enters the definition in the system catalog and builds an index on the existing data.
If the UNIQUE option is specified and the table already contains rows having duplicate values in the index key columns, the CREATE INDEX statement is rejected.
The CLUSTERING option does not affect the physical placement of rows that are already in the table when the CREATE INDEX statement is issued.
The new index is maintained automatically by ALLBASE/SQL until the index is deleted by a DROP INDEX statement or until the table it is associated with is dropped.
The following equation determines the maximum key size for a B-tree or hash index:
(NumberOfIndexColumns + 2)*2 + SumKeyLengths + 8 <= 1024
If the index contains only one column, the maximum length that column can be is 1010 bytes. At compile time, SumKeyLengths is computed assuming columns of NULL and VARCHAR columns contain no data. At run time, the actual data lengths are assumed.
At most 16 columns are allowed in a user-defined index.
Indexes cannot be created for views, including the system views and pseudotables.
Index entries are sorted in ascending order. Null compares higher than other values for sorting.
An index is automatically stored in the same DBEFileSet as its table.
The CREATE INDEX statement can invalidate stored sections. Refer to the ALLBASE/SQL Database Administration Guide for additional information on section validation.
The CREATE INDEX statement allocates file space for sorting under any available TempSpace location, or in the default sort space. After the index has been created, this file space is deallocated.
Indexes created with the CREATE INDEX statement are not associated with referential or unique constraints in any manner, and are not used to support any constraints. So a unique index created with the CREATE INDEX statement cannot be referenced as a primary key in a referential constraint.

Authorization

You can issue this statement if you have INDEX or OWNER authority for the table or if you have DBA authority.

Example

This unique index ensures that all part numbers are unique.

   CREATE UNIQUE INDEX PurchDB.PartNumIndex
                    ON PurchDB.Parts (PartNumber)

This clustering index causes rows for order items associated with one order to be stored physically close together.

   CREATE CLUSTERING INDEX OrderItemIndex
                        ON PurchDB.OrderItems (OrderNumber)