|
|
The CREATE INDEX statement creates an index on one or more columns
of a table and assigns a name to the new index.
ISQL or Application Programs
CREATE [UNIQUE] [CLUSTERING] INDEX [Owner.]Indexname ON
[Owner.]TableName ({ColumnName[ASC
DESC]}[,...])
- 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.
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.
You can issue this statement if you have INDEX or OWNER authority for the table
or if you have DBA authority.
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)
|