CREATE INDEX statement

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

Syntax

                                  (1)   
>>-CREATE--| Index-Type Options |------------------------------->

>--INDEX--+---------------+--index--ON--+-table---+------------->
          '-IF NOT EXISTS-'             '-synonym-'   

                       (2)                      
>--| Index-Key Specs |------| Index Options |------------------><

Index Options

|--+------------------------------------+----------------------->
   |                                (3) |   
   '-| USING Access-Method Clause |-----'   

>--+---------------------------+--+-------------------------+--->
   |                       (4) |  |                     (5) |   
   '-| FILLFACTOR Option |-----'  '-| Storage Options |-----'   

>--+---------------------+--+--------------------+-------------->
   |                 (6) |  |                (7) |   
   '-| Index Modes |-----'  '-HASH ON Clause-----'   

>--+------------+--+----------------+--------------------------->
   |        (8) |  |            (9) |   
   '-ONLINE-----'  '-COMPRESSED-----'   

>--+------------------------------+-----------------------------|
   |                         (10) |   
   '-| Extent Size Options |------'   

1. See Index-type options
2. See Index-key specification
3. See USING access-method clause
4. See FILLFACTOR Option
5. See Storage options
6. See Index modes
7. See HASH ON clause
8. See The ONLINE keyword of CREATE INDEX
9. See COMPRESSED option for indexes
10. See Extent Size Options

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.