SQL Reference

CREATE INDEX

The CREATE INDEX statement creates an index on a table.

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.

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--'
 
                        .-,-----------------------.
                        V               .-ASC--.  |
>----ON--table-name--(-----column-name--+------+--+---)--------->
                                        '-DESC-'
 
>----*--+--------------------------------------------+---------->
        |                    .-,--------------.      |
        |         (1)        V                |      |
        '-INCLUDE---------(-----column-name---+---)--'
 
                          .-PCTFREE 10--------.
>----*--+---------+---*---+-------------------+---*------------><
        '-CLUSTER-'       '-PCTFREE--integer--'

Notes:

The INCLUDE clause may only be specified if UNIQUE is specified.

Description

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 I. 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).

INDEX index-name

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

ON table-name

Names the table on which the index is to be created. The table-name must name a base table (not a view) or a summary table described in the catalog. It must not name a catalog table (SQLSTATE 42832). 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).

column-name

Identifies a column that is to be part of the index key. 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 (not inherited from a supertable) (SQLSTATE 428DS). No column-name may be repeated (SQLSTATE 42711).

The sum of the length attributes of the specified columns must not be greater than 255. If table-name is a typed table, the sum of the length attributes of the specified columns must not be greater than 251. Note that this figure 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. No LONG VARCHAR, LONG VARGRAPHIC, LOB or DATALINK column 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)).

ASC: Puts the index entries in ascending order by the column. This is the default.
DESC: Puts the index entries in descending order by the column.

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.

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

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.

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.
A unique index must exist on the underlying table of a replicated summary table where the index key columns are included in the select list of the query that defines the replicated summary table. One such index is selected when the replicated summary table is created as a system required index. This index cannot be dropped unless the replicated summary table is dropped. (SQLSTATE 42917).

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.

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)

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]