- UNIQUE
- If ON table-name is specified, UNIQUE
prevents the table from containing two or more rows with the same
value of the index key. The uniqueness is enforced at the end of the
SQL statement that updates rows or inserts new rows.
The uniqueness
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.
If the index is on an XML column (the
index is an index over XML data), the uniqueness applies to values
with the specified pattern-expression for all rows of the table.
Uniqueness is enforced on each value after the value has been converted
to the specified sql-data-type. Because converting to the specified sql-data-type might
result in a loss of precision or range, or different values might
be hashed to the same key value, multiple values that appear to be
unique in the XML document might result in duplicate key errors.
The uniqueness of character strings depends on XQuery semantics where
trailing blanks are significant. Therefore, values that would be duplicates
in SQL but differ in trailing blanks are considered unique values
in an index over XML data.
When UNIQUE is used, null values are treated as any other values.
For example, if the key is a single column that may contain null values,
that column may contain no more than one null value.
If the
UNIQUE option is specified, and the table has a distribution key,
the columns in the index key must be a superset of the distribution
key. That is, the columns specified for a unique index key must include
all the columns of the distribution key (SQLSTATE 42997).
Primary or unique keys cannot be subsets of dimensions (SQLSTATE
429BE).
If ON nickname is specified,
UNIQUE should be specified only if the data for the index key contains
unique values for every row of the data source table. The uniqueness
will not be checked.
For an index over XML data,
UNIQUE can be specified only if the specified pattern-expression specifies
a single complete path and does not contain a descendant
or descendant-or-self axis, "//", an xml-wildcard, node(),
or processing-instruction() (SQLSTATE 429BS).
In a partitioned database environment, the following
rules apply to a table with one or more XML columns:
- A distributed table cannot have a unique index over XML data.
- A unique index over XML data is supported only on a table that
does not have a distribution key and that is on a single node multi-partition
database.
- If a unique index over XML data exists on a table, the table cannot
be altered to add a distribution key.
- INDEX index-name
- Names the index or index specification. The name, including the
implicit or explicit qualifier, must not identify an index or index
specification that is described in the catalog, or an existing index
on a declared temporary table (SQLSTATE 42704). The qualifier must
not be SYSIBM, SYSCAT, SYSFUN, or SYSSTAT (SQLSTATE 42939).
The
implicit or explicit qualifier for indexes on declared global temporary
tables must be SESSION (SQLSTATE 428EK).
- ON table-name or nickname
- The table-name identifies a table on
which an index is to be created. The table must be a base table (not
a view), a created temporary table, a declared temporary table, a materialized query table
that exists at the current server, or a declared temporary table.
The name of a declared temporary table must be qualified with SESSION.
The table-name must not identify a catalog
table (SQLSTATE 42832). If UNIQUE is specified and table-name is
a typed table, it must not be a subtable (SQLSTATE 429B3).
nickname is
the nickname on which an index specification is to be created. The nickname references
either a data source table whose index is described by the index
specification, or a data source view that is based on such a table.
The nickname must be listed in the catalog.
- column-name
- For an index, column-name identifies
a column that is to be part of the index key. For an index specification, column-name is
the name by which the federated server references a column of a data
source table.
Each column-name must
be an unqualified name that identifies a column of the table. Up
to 64 columns can be specified. If table-name is
a typed table, up to 63 columns can be specified. If table-name is
a subtable, at least one column-name must
be introduced in the subtable; that is, not inherited from a supertable
(SQLSTATE 428DS). No column-name can be
repeated (SQLSTATE 42711).
The sum of the stored lengths of
the specified columns must not be greater than the index key length
limit for the page size. For key length limits, see "SQL limits".
If table-name is a typed table, the index
key length limit is further reduced by 4 bytes. Note that this length
limit can be reduced even more by system overhead, which varies according
to the data type of the column and whether or not the column is nullable.
For more information on overhead affecting this limit, see "Byte
Counts" in "CREATE TABLE".
Note that this length can
be reduced by system overhead, which varies according to the data
type of the column and whether it is nullable. For more information
on overhead affecting this limit, see "Byte Counts" in "CREATE
TABLE".
No
LOB column or distinct type column based on a LOB can be used as part
of an index, even if the length attribute of the column is small enough
to fit within the index key length limit for the page size (SQLSTATE
54008). A structured type column can only be specified if the EXTEND
USING clause is also specified (SQLSTATE 42962). If the EXTEND USING
clause is specified, only one column can be specified, and the type
of the column must be a structured type or a distinct type that is
not based on a LOB (SQLSTATE 42997).
If an index has only
one column, and that column has the XML data type, and the GENERATE
KEY USING XMLPATTERN clause is also specified, the index is an index over XML data.
A column with the XML data type can be specified only if the GENERATE
KEY USING XMLPATTERN clause is also specified (SQLSTATE 42962). If
the GENERATE KEY USING XMLPATTERN clause is specified, only one column
can be specified, and the type of the column must be XML.
- ASC
- Specifies that index entries are to be kept in ascending order
of the column values; this is the default setting. ASC cannot be specified
for indexes that are defined with EXTEND USING (SQLSTATE 42601).
- DESC
- Specifies that index entries are to be kept in descending order
of the column values. DESC cannot be specified for indexes that are
defined with EXTEND USING, or if the index is an index over XML data (SQLSTATE
42601).
- PARTITIONED
- Indicates that a partitioned index should be created. The table-name must
identify a table defined with data partitions (SQLSTATE 42601).
If
the table is partitioned and neither PARTITIONED nor NOT PARTITIONED
is specified, the index is created as partitioned (with a few exceptions).
A nonpartitioned index is created instead of a partitioned index if
any of the following situations apply:
- You specify UNIQUE, and the index key does not include all the
table-partitioning key columns.
- You specify UNIQUE for an index over XML
data.
- You create a spatial index.
- You define the index over XML column paths.
A partitioned index with a definition that duplicates
the definition of a nonpartitioned index is not considered to be a
duplicate index. For more details, see the Rules section in this topic.
You will receive an error if you specify the
PARTITIONED keyword for the following indexes:
- An index on a nonpartitioned table (SQLSTATE 42601)
- A unique index where the index key does not include all the table-partitioning
key columns (SQLSTATE 42990)
- A spatial index (SQLSTATE 42997)
A partitioned index cannot be created on a partitioned
table that has detached dependent tables, for example, MQTs (SQLSTATE
55019).
A partitioned index cannot be created on a
partitioned table that has detached partitions.
The
table space placement for an index partition of the partitioned index
is determined by the following rules:
- If the table being indexed was created using the partition-tablespace-options
INDEX IN clause of the CREATE TABLE statement, the index partition
is created in the table space specified in that INDEX IN clause.
- If the CREATE TABLE statement for the table being indexed did
not specify the partition-tablespace-options INDEX IN clause, the
index partition partitioned index is created in the same table space
as the corresponding data partition that it indexes.
The IN clause of the CREATE INDEX statement is not supported
for partitioned indexes (SQLSTATE 42601). The tablespace-clauses INDEX
IN clause of the CREATE TABLE statement is ignored for partitioned
indexes.
- NOT PARTITIONED
- Indicates that a nonpartitioned index should be created that spans
all of the data partitions defined for the table. The table-name must
identify a table defined with data partitions (SQLSTATE 42601).
A
nonpartitioned index with a definition that duplicates the definition
of a partitioned index is not considered to be a duplicate index.
For more details, see the Rules section in this topic.
The table space
placement for a the nonpartitioned index is determined by the following
rules:
- If you specify the IN clause of the CREATE INDEX statement, the
nonpartitioned index is placed in the table space specified in that
IN clause.
- If you do not specify the IN clause of the CREATE INDEX statement,
the following rules determine the table space placement of the nonpartitioned
index:
- If the table being indexed was created using the tablespace-clauses
INDEX IN clause of the CREATE TABLE statement, the nonpartitioned
index is placed in the table space specified in that INDEX IN clause.
- If the table being indexed was created without using the tablespace-clauses
INDEX IN clause of the CREATE TABLE statement, the nonpartitioned
index is created in the table space of the first visible or attached
data partition of the table. The first visible or attached data partition
of the table is the first partition in the list of data partitions
that are sorted on the basis of range specifications. Also, the authorization
ID of the statement is not required to have the USE privilege on the
default table space.
- IN tablespace-name
- The
IN clause is supported only for nonpartitioned indexes on partitioned
tables. Specifically the clause is not supported for indexes on nonpartitioned
tables. Specifying the IN clause for partitioned indexes or for indexes
on nonpartitioned tables results in SQLSTATE 42601.
Specifies
the table space in which the index is to be created. This clause is not supported for indexes on a created
temporary table or a declared temporary table (SQLSTATE 42601). You
can specify this clause even if the INDEX IN clause was specified
when the table was created. This will override that clause.
The
table space specified by tablespace-name must
be in the same database partition group as the data table spaces for
the table and manage space in the same way as the other table spaces
of the partitioned table (SQLSTATE 42838); it must be a table space
on which the authorization ID of the statement holds the USE privilege.
If
the IN clause is not specified, the index is created in the table
space that was specified by the INDEX IN clause on the CREATE TABLE
statement. If no INDEX IN clause was specified, the table space of
the first visible or attached data partition of the table is used.
This is the first partition in the list of data partitions that are
sorted on the basis of range specifications. If the IN clause is not
specified, the authorization ID of the statement is not required to
have the USE privilege on the default table space.
- SPECIFICATION ONLY
- Indicates that this statement will be used to create an index
specification that applies to the data source table referenced by nickname.
SPECIFICATION ONLY must be specified if nickname is
specified (SQLSTATE 42601). It cannot be specified if table-name is
specified (SQLSTATE 42601).
If the index specification applies
to an index that is unique, DB2 does
not verify that the column values in the remote table are unique.
If the remote column values are not unique, queries against the nickname
that include the index column might return incorrect data or errors.
This clause cannot be used when creating an index
on a created temporary table or declared temporary table (SQLSTATE
42995).
- INCLUDE
- This keyword introduces a clause that specifies additional columns
to be appended to the set of index key columns. Any columns included
with this clause are not used to enforce uniqueness. These included
columns might improve the performance of some queries through index
only access. The columns must be distinct from the columns used to
enforce uniqueness (SQLSTATE 42711). UNIQUE must be specified when
INCLUDE is specified (SQLSTATE 42613). The limits for the number of
columns and sum of the length attributes apply to all of the columns
in the unique key and in the index.
This clause
cannot be used with created temporary tables or declared temporary
tables (SQLSTATE 42995).
- column-name
- Identifies a column that is included in the index but not part
of the unique index key. The same rules apply as defined for columns
of the unique index key. The keywords ASC or DESC may be specified
following the column-name but have no effect on the order.
INCLUDE cannot be specified for indexes that
are defined with EXTEND USING, if nickname is
specified, or if the index is an XML values index (SQLSTATE 42601).
- xml-index-specification
- Specifies how index keys are generated from XML documents that
are stored in an XML column. xml-index-specification cannot
be specified if there is more than one index column, or if the column
does not have the XML data type.
This clause only applies to
XML columns (SQLSTATE 429BS).
- GENERATE KEY USING XMLPATTERN xmlpattern-clause
- Specifies the parts of an XML document that are to be indexed.
XML pattern values are the
indexed values generated by the xmlpattern-clause.
List data type nodes are not supported in the index. If a node is
qualified by the xmlpattern-clause and an
XML schema exists that specifies that the node is a list data type,
then the list data type node cannot be indexed (SQLSTATE 23526 for
CREATE INDEX statements, or SQLSTATE 23525 for INSERT and UPDATE statements).
- xmlpattern-clause
- Contains a pattern expression that identifies the nodes that are
to be indexed. It consists of an optional namespace-declaration and
a required pattern-expression.
- namespace-declaration
- If the pattern expression contains qualified names, a namespace-declaration
must be specified to define namespace prefixes. A default namespace
can be defined for unqualified names.
- DECLARE NAMESPACE namespace-prefix=namespace-uri
- Maps namespace-prefix, which is
an NCName, to namespace-uri, which
is a string literal. The namespace-declaration can
contain multiple namespace-prefix-to-namespace-uri mappings.
The namespace-prefix must be unique within
the list of namespace-declaration (SQLSTATE
10503).
- DECLARE DEFAULT ELEMENT NAMESPACE namespace-uri
- Declares the default namespace URI for unqualified element names
or types. If no default namespace is declared, unqualified names of
elements and types are in no namespace. Only one default namespace
can be declared (SQLSTATE 10502).
- pattern-expression
- Specifies the nodes in an XML document that are indexed. The pattern-expression can
contain pattern-matching characters (*). It is similar to a path expression
in XQuery, but supports a subset of the XQuery language that is supported
by DB2.
- / (forward slash)
- Separates path expression steps.
- // (double forward slash)
- This is the abbreviated syntax for /descendant-or-self::node()/.
You cannot use // (double forward slash) if
you also specify UNIQUE.
- forward-axis
-
- child::
- Specifies children of the context node. This is the default,
if no other forward axis is specified.
- @
- Specifies attributes of the context node. This is the abbreviated
syntax for attribute::.
- attribute::
- Specifies attributes of the context node.
- descendant::
- Specifies the descendants of the context node. You cannot use descendant:: if
you also specify UNIQUE.
- self::
- Specifies just the context node itself.
- descendant-or-self::
- Specifies the context node and the descendants of the context
node. You cannot use descendant-or-self:: if you also specify UNIQUE.
- xmlname-test
- Specifies the node name for the step in the path using a qualified
XML name (xml-qname) or a wildcard (xml-wildcard).
- xml-ncname
- An XML name as defined by XML 1.0. It cannot include a colon character.
- xml-qname
- Specifies a qualified XML name (also known as a QName) that can
have two possible forms:
- xml-nsprefix:xml-ncname, where the xml-nsprefix is an xml-ncname
that identifies an in-scope namespace
- xml-ncname, which indicates that the default namespace should
be applied as the implicit xml-nsprefix
- xml-wildcard
- Specifies an xml-qname as a wildcard that can have three
possible forms:
- * (a single asterisk character) indicates any xml-qname
- xml-nsprefix:* indicates any xml-ncname within
the specified namespace
- *:xml-ncname indicates a specific
XML name in any in-scope namespace
You cannot use xml-wildcard if
you also specify UNIQUE.
- xmlkind-test
- Use these options to specify what types of nodes you pattern match.
The following options are available to you:
- node()
- Matches any node. You cannot use node() if
you also specify UNIQUE.
- text()
- Matches any text node.
- comment()
- Matches any comment node.
- processing-instruction()
- Matches any processing instruction node. You cannot use
processing-instruction() if you also specify UNIQUE.
- xmltype-clause
-
- AS data-type
- Specifies the data type to which indexed values are converted
before they are stored. Values are converted to the index XML data
type that corresponds to the specified index SQL data type.
Table 1. Corresponding index data types| Index XML data type |
Index SQL data type |
|---|
| xs:string |
VARCHAR(integer),
VARCHAR HASHED |
| xs:double |
DOUBLE |
| xs:date |
DATE |
| xs:dateTime |
TIMESTAMP |
For VARCHAR(integer) and
VARCHAR HASHED, the value is converted to an xs:string value using
the XQuery function fn:string. The length attribute of VARCHAR(integer)
is applied as a constraint to the resulting xs:string value. An index
SQL data type of VARCHAR HASHED applies a hash algorithm to the resulting
xs:string value to generate a hash code that is inserted into the
index.
For indexes using the data types DOUBLE, DATE,
and TIMESTAMP, the value is converted to the index XML data type using
the XQuery cast expression.
If the
index is unique, the uniqueness of the value is enforced after the
value is converted to the indexed type.
- data-type
- The following data type is supported:
- sql-data-type
- Supported SQL data types are:
- VARCHAR(integer)
- If this form of VARCHAR is specified, DB2 uses integer as a constraint. If document
nodes that are to be indexed have values that are longer than integer,
the documents are not inserted into the table if the index already
exists. If the index does not exist, the index is not created. integer is
a value between 1 and a page size-dependent maximum. Table 2 shows the maximum value for each
page size.
Table 2. Maximum length of document nodes by page size| Page size |
Maximum length of document node (bytes) |
|---|
| 4KB |
817 |
| 8KB |
1841 |
| 16KB |
3889 |
| 32KB |
7985 |
XQuery semantics are used for string comparisons, where
trailing blanks are significant. This differs from SQL semantics,
where trailing blanks are insignificant during comparisons.
- VARCHAR HASHED
- Specify VARCHAR HASHED to handle indexing of arbitrary length
character strings. The length of an indexed string has no limit. DB2 generates an eight-byte hash
code over the entire string. Indexes that use these hashed character
strings can be used only for equality lookups. XQuery semantics are
used for string equality comparisons, where trailing blanks are significant.
This differs from SQL semantics, where trailing blanks are insignificant
during comparisons. The hash on the string preserves XQuery semantics
for equality and not SQL semantics.
- DOUBLE
- Specifies that the data type DOUBLE is used for indexing numeric
values. Unbounded decimal types and 64 bit integers may lose precision
when they are stored as a DOUBLE value. The values for DOUBLE may
include the special numeric values NaN, INF, -INF, +0,
and -0, even though the SQL data type DOUBLE
itself does not support these values.
- DATE
- Specifies that the data type DATE is used for
indexing XML values. Note that the XML schema data type for xs:date
allows greater range of values than the DB2 pureXML® xs:date data type that
corresponds to the SQL data type. If an out-of-range value is encountered,
an error is returned.
- TIMESTAMP
- Specifies that the data type TIMESTAMP is used
for indexing XML values. Note that the XML schema data type for xs:dateTime
allows greater range of values and fractional seconds precision than
the DB2 pureXML xs:dateTime data
type that corresponds to the SQL data type. If an out-of range value
is encountered, an error is returned.
- IGNORE INVALID VALUES
- Specifies that XML pattern values that are invalid for the target
index XML data type are ignored and that the corresponding values
in the stored XML documents are not indexed by the CREATE INDEX statement.
By default, invalid values are ignored. During insert and update operations,
the invalid XML pattern values are not indexed, but XML documents
are still inserted into the table. No error or warning is raised,
because specifying these data types is not a constraint on the XML
pattern values (XQuery expressions that search for the specific XML
index data type will not consider these values).
The index can
ignore only invalid XML pattern values for the index XML data type.
Valid values must conform to the DB2 representation
of the value for the index XML data type, or an error is returned.
An XML pattern value associated with the index XML data type xs:string
is always valid. However, the additional length constraint of the
associated index SQL data type VARCHAR(integer)
data type can still raise an error, if the maximum length is exceeded.
If an error is returned, XML data is not inserted or updated in the
table if the index already exists (SQLSTATE 23525). If the index does
not exist, the index is not created (SQLSTATE 23526).
- REJECT INVALID VALUES
- Specifies that all XML pattern values must be valid for the index
XML data type. If any XML pattern value cannot be cast to the index
XML data type, an error is returned. XML data is not inserted or
updated in the table if the index already exists (SQLSTATE 23525).
If the index does not exist, the index is not created (SQLSTATE 23526).
- CLUSTER
- Specifies that the index is the clustering index of the table.
The cluster factor of a clustering index is maintained or improved
dynamically as data is inserted into the associated table, by attempting
to insert new rows physically close to the rows for which the key
values of this index are in the same range. Only one clustering index
may exist for a table so CLUSTER may not be specified if it was used
in the definition of any existing index on the table (SQLSTATE 55012).
A clustering index may not be created on a table that is defined
to use append mode (SQLSTATE 428D8).
CLUSTER is disallowed if nickname is
specified, or if the index is an index over XML data (SQLSTATE
42601). This clause cannot be used with created
temporary tables or declared temporary tables (SQLSTATE 42995) or
range-clustered tables (SQLSTATE 429BG).
- EXTEND USING index-extension-name
- Names the index-extension used to manage
this index. If this clause is specified, then there must be only
one column-name specified and that column
must be a structured type or a distinct type (SQLSTATE 42997). The
index-extension-name must name an index
extension described in the catalog (SQLSTATE 42704). For a distinct
type, the column must exactly match the type of the corresponding
source key parameter in the index extension. For a structured type
column, the type of the corresponding source key parameter must be
the same type or a supertype of the column type (SQLSTATE 428E0).
This clause cannot be used with created temporary
tables or declared temporary tables (SQLSTATE 42995).
- constant-expression
- Identifies values for any required arguments for the index extension.
Each expression must be a constant value with a data type that exactly
matches the defined data type of the corresponding index extension
parameters, including length or precision, and scale (SQLSTATE 428E0).
This clause must not exceed 32 768 bytes in length in the
database code page (SQLSTATE 22001).
- PCTFREE integer
- Specifies what percentage of each index page to leave as free
space when building the index. The first entry in a page is added
without restriction. When additional entries are placed in an index
page at least integer percent of free space
is left on each page. The value of integer can
range from 0 to 99. If a value greater than 10 is specified, only
10 percent free space will be left in non-leaf pages. The default
is 10.
PCTFREE is disallowed if nickname is
specified (SQLSTATE 42601). This clause cannot be used with created temporary tables or declared temporary
tables (SQLSTATE 42995).
- LEVEL2 PCTFREE integer
- Specifies what percentage of each index level 2 page to leave
as free space when building the index. The value of integer can
range from 0 to 99. If LEVEL2 PCTFREE is not set, a minimum of 10
or PCTFREE percent of free space is left on all non-leaf pages. If
LEVEL2 PCTFREE is set, integer percent of
free space is left on level 2 intermediate pages, and a minimum of
10 or integer percent of free space is left
on level 3 and higher intermediate pages.
LEVEL2 PCTFREE is disallowed
if nickname is specified (SQLSTATE 42601).
This clause cannot be used with created temporary
tables or declared temporary tables (SQLSTATE 42995).
- MINPCTUSED integer
- Indicates whether index leaf pages are merged online, and the
threshold for the minimum percentage of space used on an index leaf
page. If, after a key is removed from an index leaf page, the percentage
of space used on the page is at or below integer percent,
an attempt is made to merge the remaining keys on this page with those
of a neighboring page. If there is sufficient space on one of these
pages, the merge is performed and one of the pages is deleted. The
value of integer can be from 0 to 99. A
value of 50 or below is recommended for performance reasons. Specifying
this option will have an impact on update and delete performance. Merging is only done during update and delete operations
when an exclusive table lock is held. If an exclusive table lock
does not exist, keys are marked as pseudo deleted during update and
delete operations, and no merging is done. Consider using the CLEANUP
ONLY ALL option of REORG INDEXES to merge leaf pages instead of using
the MINPCTUSED option of CREATE INDEX.
MINPCTUSED is disallowed
if nickname is specified (SQLSTATE 42601).
This clause cannot be used with created temporary
tables or declared temporary tables (SQLSTATE 42995).
- DISALLOW REVERSE SCANS
- Specifies that an index only supports forward scans or scanning
of the index in the order that was defined at index creation time.
DISALLOW REVERSE SCANS cannot be specified together with nickname (SQLSTATE
42601).
- ALLOW REVERSE SCANS
- Specifies that an index can support both forward and reverse scans;
that is, scanning of the index in the order that was defined at index
creation time, and scanning in the opposite order.
ALLOW REVERSE
SCANS cannot be specified together with nickname (SQLSTATE
42601).
- PAGE SPLIT
- Specifies the page split behavior when values are inserted into
an index. The default is SYMMETRIC.
- SYMMETRIC
- Specifies that pages are to be split roughly in the middle. Use
this option in the following situations:
- When the insertion into an index is random
- When the insertion into an index does not follow the patterns
that are addressed by the PAGE SPLIT HIGH and PAGE SPLIT LOW options
- HIGH
- Specifies an index page split behavior that uses the space on
index pages efficiently when there are ever-increasing values in the
index. Increasing values in the index might occur when the following
conditions are met:
- There is an index with multiple key parts and there are multiple
index pages of values where all except the last key part has the same value.
- All insert operations into the table consist of a new value, which
has the same value as existing keys for all but the last key part.
- The last key part of the inserted value is larger than the values
of the existing keys.
For example, assume that an index has the following key values: (1,1),(1,2),(1,3), ... (1,n),
(2,1),(2,2),(2,3), ... (2,n),
...
(m,1),(m,2),(m,3), ... (m,n)
The next key to be inserted
would have the value (x,y) where 1 <=
x <= m and y > n.In such cases, use
the PAGE SPLIT HIGH clause so that page splits do not result in many
pages that are 50 percent empty.
- LOW
- Specifies an index page split behavior that uses the space on
index pages efficiently when there are ever-decreasing values in the
index. Decreasing values in the index might occur when the following
conditions are met:
- There is an index with multiple key parts and there are multiple
index pages of values where all except the last key part has the same value.
- All insert operations into the table consist of a new value, which
has the same value as existing keys for all but the last key part.
- The last key part of the inserted value is smaller than the values
of the existing keys.
In such cases, use the PAGE SPLIT LOW clause so that page splits
do not result in many pages that are 50 percent empty.
- COLLECT STATISTICS
- Specifies that basic index statistics are to be collected during
index creation.
- DETAILED
- Specifies that extended index statistics (CLUSTERFACTOR and PAGE_FETCH_PAIRS)
are also to be collected during index creation.
- SAMPLED
- Specifies that sampling can be used when compiling extended index
statistics.
- COMPRESS
- Specifies whether index compression is enabled. By default, index compression will be enabled if
data row compression is enabled
or if the
table is a declared global temporary table (DGTT) or created global temporary table (CGTT)
;
index compression will be disabled if data row compression is disabled. This option can be used to
override the default behavior. COMPRESS is disallowed if nickname is
specified (SQLSTATE 42601). - YES
- Specifies that index compression is enabled. Insert and update operations on the index will be
subject to compression.
- NO
- Specifies that index compression is disabled.
DB2 Version 9.7 for Linux, UNIX, and Windows
>>-CREATE--+--------+--INDEX--index-name------------------------> '-UNIQUE-' .-,---------------------. (1) V .-ASC--. | >--ON--+-table-name-----+--(----column-name--+------+-+--)------> | (2) | '-DESC-' '-nickname-------' >--+-----------------+--●--+---------------------+--●-----------> +-PARTITIONED-----+ '-IN--tablespace-name-' '-NOT PARTITIONED-' >--+--------------------+--●------------------------------------> '-SPECIFICATION ONLY-' >--+------------------------------------+--●--------------------> | .-,-----------. | | (3) V | | '-INCLUDE------(----column-name-+--)-' >--+-----------------------------------------------------------------------+--●--> | (4) | +-| xml-index-specification |-------------------------------------------+ +-CLUSTER---------------------------------------------------------------+ '-EXTEND USING--index-extension-name--+-------------------------------+-' | .-,-------------------. | | V | | '-(----constant-expression-+--)-' .-PCTFREE 10-------. >--+------------------+--●--+-------------------------+--●------> '-PCTFREE--integer-' '-LEVEL2 PCTFREE--integer-' .-ALLOW REVERSE SCANS----. >--+---------------------+--●--+------------------------+--●----> '-MINPCTUSED--integer-' '-DISALLOW REVERSE SCANS-' .-PAGE SPLIT SYMMETRIC-. >--+----------------------+--●----------------------------------> '-PAGE SPLIT--+-HIGH-+-' '-LOW--' >--+------------------------------------------------+--●--------> '-COLLECT--+-----------------------+--STATISTICS-' '-+---------+--DETAILED-' '-SAMPLED-' >--+-------------------+--------------------------------------->< '-COMPRESS--+-NO--+-' '-YES-'