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

Read syntax diagramSkip visual syntax diagram
                                  (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 |------'   

Notes:
  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
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. 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.

The following statistics are generated automatically by the CREATE INDEX statement, with or without the ONLINE keyword:
  • 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.

Copyright© 2019 HCL Technologies Limited