CREATE INDEX

>>-CREATE------------------------------------------------------->
 
                            (1)                                        (2)
>--+-| Index-Type Options |-------| Index Scope |--| Index-Key Specs |-------+-------------------+-+-->
   |                                                                         '-| Index Options |-' |
   |  (3)                                                        (4)                               |
   '--------GK INDEX--index--ON--static--(--| GK SELECT Clause |-------)--+--------------+---------'
                                                                          '-USING BITMAP-'
 
>--+-----------------------------------+--+---------------+----><
   |  (3)                         (5)  |  |  (6)          |
   '--------| LOCK MODE Options |------'  '--------ONLINE-'
 
Index Scope:
 
|--INDEX--index--ON--+-table---+--------------------------------|
                     '-synonym-'
 
Index Options:
 
|--+--------------------------------------------+--------------->
   |  (6)                                  (7)  |
   '--------| USING Access-Method Clause |------'
 
>--+----------------------------+------------------------------->
   |                       (8)  |
   '-| FILLFACTOR Option |------'
 
>--+--------------------------+--------------------------------->
   |                     (9)  |
   +-| Storage Options |------+
   |  (3)                     |
   '--------USING BITMAP------'
 
>--+------------------------------+-----------------------------|
   |  (6)                   (10)  |
   '--------| Index Modes |-------'

Notes:

See Index-Type Options
See Index-Key Specification
Extended Parallel Server only
See SELECT Clause for Generalized-Key Index
See LOCK MODE Options (XPS)
Dynamic Server only
See USING Access-Method Clause (IDS)
See FILLFACTOR Option
See Storage Options
See Index Modes (IDS)

Element	Description	Restrictions	Syntax
index	Name declared here for a new index	Must be unique among names of indexes in the database	Database Object Name
static	Table on which a Generalized Key index is created (XPS)	Table must exist and be static; it cannot be a virtual table	Database Object Name
synonym, table	Name or synonym of a standard or temporary table to be indexed	Synonym and its table must exist in the current database	Database Object Name

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 (IDS).)

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.

In Dynamic Server, neither synonym nor table can refer to a virtual table.

If you are using Extended Parallel Server, use the USING BITMAP keywords to store the list of records in each key of the index as a compressed bitmap. The storage option is not compatible with a bitmap index because bitmap indexes must be fragmented in the same way as the table.

CREATE INDEX

Syntax

Usage