SQL Reference

CREATE INDEX

The CREATE INDEX statement is used to create:

An index on a DB2 table
An index specification: metadata that indicates to the optimizer that a data source table has an index

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

SYSADM or DBADM authority.
One of:
- CONTROL privilege on the table
- INDEX privilege on the table
and one of:
- IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the index does not exist
- CREATEIN privilege on the schema, if the schema name of the index refers to an existing schema.

Syntax

>>-CREATE----+---------+--INDEX--index-name--------------------->
             '-UNIQUE--'
 
                                 .-,-----------------------.
                      (1)        V               .-ASC--.  |
>----ON--+-table-name------+--(-----column-name--+------+--+---)->
         |          (2)    |                     '-DESC-'
         '-nickname--------'
 
>-----+---------------------+----------------------------------->
      '-SPECIFICATION ONLY--'
 
>----*--+--------------------------------------------+---------->
        |                    .-,--------------.      |
        |         (3)        V                |      |
        '-INCLUDE---------(-----column-name---+---)--'
 
>----*--+----------------------------------------------------------------------------+---*->
        +-CLUSTER--------------------------------------------------------------------+
        '-EXTEND USING--index-extension-name--+------------------------------------+-'
                                              |    .-,----------------------.      |
                                              |    V                        |      |
                                              '-(-----constant-expression---+---)--'
 
     .-PCTFREE 10--------.
>----+-------------------+---*----+----------------------+--*--->
     '-PCTFREE--integer--'        '-MINPCTUSED--integer--'
 
     .-DISALLOW REVERSE SCANS--.
>----+-------------------------+---*---------------------------><
     '-ALLOW REVERSE SCANS-----'

Notes:

In a federated system, the table-name must identify a table in the federated database. It cannot identify a data source table.
If nickname is specified, the CREATE INDEX statement will create an index specification. INCLUDE, CLUSTER, PCTFREE, MINPCTUSED, DISALLOW REVERSE SCANS, and ALLOW REVERSE SCANS cannot be specified.
The INCLUDE clause may only be specified if UNIQUE is specified.

Description

UNIQUE

If ON table-name is specified, UNIQUE prevents the table from containing two or more rows with the same value of the index key. The uniqueness is enforced at the end of the SQL statement that updates rows or inserts new rows. For details refer to Appendix J, Interaction of Triggers and Constraints.

The uniqueness is also checked during the execution of the CREATE INDEX statement. If the table already contains rows with duplicate key values, the index is not created.

When UNIQUE is used, null values are treated as any other values. For example, if the key is a single column that may contain null values, that column may contain no more than one null value.

If the UNIQUE option is specified and the table has a partitioning key, the columns in the index key must be a superset of the partitioning key. That is, the columns specified for a unique index key must include all the columns of the partitioning key (SQLSTATE 42997).

If ON nickname is specified, UNIQUE should be specified only if the data for the index key contains unique values for every row of the data source table. The uniqueness will not be checked.

The table-name cannot be a declared temporary table (SQLSTATE 42995).

INDEX index-name

Names the index or index specification. The name, including the implicit or explicit qualifier, must not identify an index or index specification that is described in the catalog. The qualifier must not be SYSIBM, SYSCAT, SYSFUN, or SYSSTAT (SQLSTATE 42939)

ON table-name or nickname

The table-name names a table on which an index is to be created. The table must be a base table (not a view) or a summary table described in the catalog. It must not name a catalog table (SQLSTATE 42832), or a declared temporary table (SQLSTATE 42995). If UNIQUE is specified and table-name is a typed table, it must not be a subtable (SQLSTATE 429B3). If UNIQUE is specified, the table-name cannot be a summary table (SQLSTATE 42809).

nickname is the nickname on which an index specification is to be created. The nickname references either a data source table whose index is described by the index specification, or a data source view that is based on such a table. The nickname must be listed in the catalog.

column-name

For an index, column-name identifies a column that is to be part of the index key. For an index specification, column-name is the name by which the federated server references a column of a data source table.

Each column-name must be an unqualified name that identifies a column of the table. 16 columns or less may be specified. If table-name is a typed table, 15 columns or less may be specified. If table-name is a subtable, at least one column-name must be introduced in the subtable, that is, not inherited from a supertable (SQLSTATE 428DS). No column-name may be repeated (SQLSTATE 42711).

The sum of the stored lengths of the specified columns must not be greater than 1024. If table-name is a typed table, the index key length limit is further reduced by 4 bytes.

Note that this length can be reduced by system overhead which varies according to the data type of the column and whether it is nullable. See Byte Counts for more information on overhead affecting this limit.

The length of any individual column must not be greater than 255 bytes. No LOB column, DATALINK column, or distinct type column based on a LOB or DATALINK may be used as part of an index, even if the length attribute of the column is small enough to fit within the 255 byte limit (SQLSTATE 42962). A structured type column can only be specified if the EXTEND USING clause is also specified (SQLSTATE 42962). If the EXTEND USING clause is specified, only one column can be specified and the type of the column must be a structured type or a distinct type that is not based on a LOB, DATALINK, LONG VARCHAR, or LONG VARGRAPHIC (SQLSTATE 42997).

ASC: Specifies that index entries are to be kept in ascending order of the column values; this is the default setting. ASC cannot be specified for indexes that are defined with EXTEND USING (SQLSTATE 42601).
DESC: Specifies that index entries are to be kept in descending order of the column values. DESC cannot be specified for indexes that are defined with EXTEND USING (SQLSTATE 42601).

SPECIFICATION ONLY

Indicates that this statement will be used to create an index specification that applies to the data source table referenced by nickname. SPECIFICATION ONLY must be specified if nickname is specified (SQLSTATE 42601). It cannot be specified if table-name is specified (SQLSTATE 42601).

INCLUDE

This keyword introduces a clause that specifies additional columns to be appended to the set of index key columns. Any columns included with this clause are not used to enforce uniqueness. These included columns may improve the performance of some queries through index only access. The columns must be distinct from the columns used to enforce uniqueness (SQLSTATE 42711). The limits for the number of columns and sum of the length attributes apply to all of the columns in the unique key and in the index.

column-name: Identifies a column that is included in the index but not part of the unique index key. The same rules apply as defined for columns of the unique index key. The keywords ASC or DESC may be specified following the column-name but have no effect on the order.

INCLUDE cannot be specified for indexes that are defined with EXTEND USING, or if nickname is specified (SQLSTATE 42601).

CLUSTER

Specifies that the index is the clustering index of the table. The cluster factor of a clustering index is maintained or improved dynamically as data is inserted into the associated table, by attempting to insert new rows physically close to the rows for which the key values of this index are in the same range. Only one clustering index may exist for a table so CLUSTER may not be specified if it was used in the definition of any existing index on the table (SQLSTATE 55012). A clustering index may not be created on a table that is defined to use append mode (SQLSTATE 428D8).

CLUSTER is disallowed if nickname is specified (42601).

EXTEND USING index-extension-name

Names the index-extension used to manage this index. If this clause is specified, then there must be only one column-name specified and that column must be a structured type or a distinct type (SQLSTATE 42997). The index-extension-name must name an index extension described in the catalog (SQLSTATE 42704). For a distinct type, the column must exactly match the type of the corresponding source key parameter in the index extension. For a structured type column, the type of the corresponding source key parameter must be the same type or a supertype of the column type (SQLSTATE 428E0).

constant-expression: Identifies values for any required arguments for the index extension. Each expression must be a constant value with a data type that exactly matches the defined data type of the corresponding index extension parameters, including length or precision, and scale (SQLSTATE 428E0).

PCTFREE integer

Specifies what percentage of each index page to leave as free space when building the index. The first entry in a page is added without restriction. When additional entries are placed in an index page at least integer percent of free space is left on each page. The value of integer can range from 0 to 99. However, if a value greater than 10 is specified, only 10 percent free space will be left in non-leaf pages. The default is 10.

PCTFREE is disallowed if nickname is specified (SQLSTATE 42601).

MINPCTUSED integer

Indicates whether indexes are reorganized online and the threshold for the minimum percentage of space used on an index leaf page If after a key is deleted from an index leaf page, the percentage of space used on the page is at or below integer percentage, an attempt is made to merge the remaining keys on this page with those of a neighboring page. If there is sufficient space on one of these pages, the merge is performed and one of the pages is deleted. The value of integer can be from 0 to 99. However, a value of 50 or below is recommended for performance reasons.

MINPCTUSED is disallowed if nickname is specified (SQLSTATE 42601).

DISALLOW REVERSE SCANS

Specifies that an index only supports forward scans or scanning of the index in the order defined at INDEX CREATE time. This is the default.

DISALLOW REVERSE SCANS is disallowed if nickname is specified (SQLSTATE 42601).

ALLOW REVERSE SCANS

Specifies that an index can support both forward and reverse scans; that is, in the order defined at INDEX CREATE time and in the opposite (or reverse) order.

ALLOW REVERSE SCANS is disallowed if nickname is specified (SQLSTATE 42601).

Rules

The CREATE INDEX statement will fail (SQLSTATE 01550) if attempting to create an index that matches an existing index. Two index descriptions are considered duplicates if:
- the set of columns (both key and include columns) and their order in the index is the same as that of an existing index AND
- the ordering attributes are the same AND
- both the previously existing index and the one being created are non-unique OR the previously existing index is unique AND
- if both the previously existing index and the one being created are unique, the key columns of the index being created are the same or a superset of key columns of the previously existing index.

Notes

If the named table already contains data, CREATE INDEX creates the index entries for it. If the table does not yet contain data, CREATE INDEX creates a description of the index; the index entries are created when data is inserted into the table.
Once the index is created and data is loaded into the table, it is advisable to issue the RUNSTATS command. (See Command Reference for information about RUNSTATS.) The RUNSTATS command updates statistics collected on the database tables, columns, and indexes. These statistics are used to determine the optimal access path to the tables. By issuing the RUNSTATS command, the database manager can determine the characteristics of the new index.
Creating an index with a schema name that does not already exist will result in the implicit creation of that schema provided the authorization ID of the statement has IMPLICIT_SCHEMA authority. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.
The optimizer can recommend indexes prior to creating the actual index. Refer to SET CURRENT EXPLAIN MODE for more details.
If an index specification is being defined for a data source table that has an index, the name of the index specification does not have to match the name of the index.
The optimizer uses index specifications to improve access to the data source tables that the specifications apply to.
For more information about index specifications, see Index Specifications.

Examples

Example 1: Create an index named UNIQUE_NAM on the PROJECT table. The purpose of the index is to ensure that there are not two entries in the table with the same value for project name (PROJNAME). The index entries are to be in ascending order.

  CREATE UNIQUE INDEX UNIQUE_NAM
   ON PROJECT(PROJNAME)

Example 2: Create an index named JOB_BY_DPT on the EMPLOYEE table. Arrange the index entries in ascending order by job title (JOB) within each department (WORKDEPT).

  CREATE INDEX JOB_BY_DPT 
   ON EMPLOYEE (WORKDEPT, JOB)

Example 3: The nickname EMPLOYEE references a data source table called CURRENT_EMP. After this nickname was created, an index was defined on CURRENT_EMP. The columns chosen for the index key were WORKDEBT and JOB. Create an index specification that describes this index. Through this specification, the optimizer will know that the index exists and what its key is. With this information, the optimizer can improve its strategy to access the table.

   CREATE UNIQUE INDEX JOB_BY_DEPT
      ON EMPLOYEE (WORKDEPT, JOB)
      SPECIFICATION ONLY

Example 4: Create an extended index type named SPATIAL_INDEX on a structured type column location. The description in index extension GRID_EXTENSION is used to maintain SPATIAL_INDEX. The literal is given to GRID_EXTENSION to create the index grid size. For a definition of index extensions, please see CREATE INDEX EXTENSION.

     CREATE INDEX SPATIAL_INDEX ON CUSTOMER (LOCATION)
          EXTEND USING (GRID_EXTENSION (x'000100100010001000400010'))

[ Top of Page | Previous Page | Next Page ]