- UNIQUE
- Prevents the table from containing two or more rows with the same value
of the index key. The constraint is enforced when rows of the table are updated
or new rows are inserted.
The constraint is also checked during the execution
of the CREATE INDEX statement. If the table already contains rows with duplicate
key values, the index is not created.
When UNIQUE is used, null values
are treated as any other values. For example, if the key is a single column
that can contain null values, that column can contain no more than one null
value.
- UNIQUE WHERE NOT NULL
- Prevents the table from containing two or more rows with the same nonnull
value of the index key. Multiple null values are allowed; otherwise, this
is identical to UNIQUE.
- ENCODED VECTOR
- Specifies that the resulting index will be an encoded vector index (EVI).
An encoded vector index cannot be used to ensure an ordering of rows. It is
used by the database manager to improve the performance of queries. For more
information, see the Database Performance and Query Optimization book.
- index-name
- Names the index. The name, including the implicit or explicit qualifier,
must not be the same as an index, table, view, alias, or file that already
exists at the current server.
If SQL names were specified, the index will
be created in the schema specified by the implicit or explicit qualifier.
If system names were specified, the index name will be created in the
schema that is specified by the qualifier. If not qualified, the index name
will be created in the same schema as the table over which the index is created.
If the index name is not a valid system name, DB2 UDB for iSeries will generate a
system name. For information on the rules for generating a name, see Rules for Table Name Generation.
- ON table-name
- Identifies the table on which the index is to be created. The table-name must identify a base table (not a view) that exists at the current server.
If the table is a partitioned table, an alias may be specified which identifies
a single partition. The created index will then only be created over the specified
partition.
- (column-name, ... )
- Identifies the list of columns that will be part of the index
key.
Each column-name must be an unqualified name that identifies
a column of the table. The same column may be specified more than once. A column-name must not identify a LOB or DATALINK column, or a distinct
type based on a LOB or DATALINK column. The number of columns must not exceed
120, and the sum of their byte lengths must not exceed 32766-n, where n is the number of columns specified
that allows nulls.
- ASC
- Specifies that the index entries are to be kept in ascending
order of the column values. ASC is the default.
- DESC
- Specifies that the index entries are to be kept in descending order
of the column values.
- WITH integer DISTINCT VALUES
- Specifies the estimated number of distinct key values. This clause may
be specified for any type of index.
For encoded vector indexes this is
used to determine the initial size of the codes assigned to each distinct
key value. The default value is 256.
For non-encoded vector indexes,
this is used as a hint to the optimizer.
- PARTITIONED
- Specifies that an index partition should be created for each data partition
defined for the table using the specified columns. The table-name must
identify a partitioned table. If the index is unique, the columns of the index
must be the same or a superset of the columns of the data partition key. PARTITIONED
is the default if the index is not unique and the table is partitioned.
- NOT PARTITIONED
- Specifies that a single index should be created that spans all of the
data partitions defined for the table. The table-name must identify
a partitioned table. NOT PARTITIONED is the default if the index is unique
and the table is partitioned. An index on a table that is not partitioned
is also by default not partitioned.
If an encoded vector index is specified,
NOT PARTITIONED is not allowed.
- PAGESIZE
- Specifies the logical page used for the index in kilobytes. Indexes
with larger logical page sizes are typically more efficient when scanned during
query processing. Indexes with smaller logical page sizes are typically more
efficient for simple index probes and individual key look ups.
The default value for PAGESIZE is determined by the length of the key and
with a minimum value of 64.
If an encoded vector index is specified,
PAGESIZE is not allowed.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.