HP 3000 Manuals HP 3000 Manuals
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
[REV BEG]
* 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.[REV END]
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)