ALLBASE/SQL Reference Manual (36216-90216)

334 Chapter10
SQL Statements A - D
CREATE INDEX
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