CREATE INDEX statement

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

(1)

1 ?  %USING Access-Method Clause3 ?  %FILLFACTOR Option4?  %Storage Options5 ?  %Index Modes6?  HASH ON Clause7?  ONLINE8?  COMPRESSED9 ?  %Extent
Size Options10

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.