CREATE INDEX statement
Use the CREATE INDEX statement to create an index for one or more columns in a table, or on values returned by a UDR that uses column values as arguments.
This statement is an extension to the ANSI/ISO standard for SQL.
Syntax
Index Options
[ <USING Access-Method Clause> [] ]
[ <FILLFACTOR Option> [] ] [ <Storage Options> [] ]
[ <Index Modes> [] ] [ HASH ON Clause[] ]
[ ONLINE[] ]
[ COMPRESSED [] ]
[ <Extent Size Options>[] ]
| Element | Description | Restrictions | Syntax |
|---|---|---|---|
| index | The name declared here for a new index. | The name must be unique among names of indexes in the database. | Identifier |
| synonym, table | The name or synonym of a standard or temporary table to be indexed | The synonym and its table must exist in the current database. Note: BTS index on
temp table is not supported. |
Identifier |
Usage
When you issue the CREATE INDEX statement, the table is locked in exclusive mode. If another process is using the table, CREATE INDEX returns an error. (For an exception, however, see The ONLINE keyword of CREATE INDEX.)
If the index is on a column that stores encrypted data, the database server cannot use the index.
If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if an index of the specified name is already defined on the specified table in the current database.
Indexes use the collation that was in effect when CREATE INDEX executed.
A secondary-access method (sometimes referred to as an index-access method) is a set of database server functions that build, access, and manipulate an index structure such as a B-tree, R-tree, or an index structure that a DataBlade® module provides, in order to speed up the retrieval of data.
Neither synonym nor table can refer to a virtual table or to a table object that the CREATE EXTERNAL TABLE statement defined.
You cannot directly base a functional index on a built-in function, but you can create an SPL wrapper that calls and returns a value from a built-in function. The arguments to a user-defined function that defines a functional index cannot be the values from a column of a collection data type.
- Index-level statistics, equivalent to the statistics gathered in the UPDATE STATISTICS operation in LOW mode for B-tree indexes.
- Column-distribution statistics, equivalent to the distribution generated in the UPDATE STATISTICS operation in HIGH mode, for a non-opaque leading indexed column of an ordinary B-tree index.