ALLBASE/SQL Reference Manual (36216-90216)

Chapter 10 335
SQL Statements A - D
CREATE INDEX
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.