 |
» |
|
|
|
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.
The default sort space is in the current logon group and account.
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)
|
|
