Create Index

Valid in: SQL, ESQL

The Create Index statement creates an index on an existing table.

Syntax

The Create Index statement has the following format:

[EXEC SQL] CREATE [UNIQUE] INDEX [schema.]index_name
              ON [schema.]table_name
              (column_name {, column_name})[UNIQUE]
              [WITH with-clause]

To build a set of indexes on the same table in parallel:

[EXEC SQL] CREATE [UNIQUE] INDEX [schema.]index_name
              ON [schema.] table_name
              (column_name [ASC|DESC]{, column_name..})[UNIQUE]
              [WITH with-clause]){, ([schema.]index_name }

or

[EXEC SQL] CREATE [UNIQUE] INDEX ([schema.] index_name

              ON table_name

              (column_name [ASC|DESC]{, column_name..})

              [UNIQUE]) {,([schema.]index_name…}

              [WITH with-clause]

Note: When using parallel index syntax, concurrent access is not allowed on readonly tables.

• index_name

  Defines the name of the index. This must be a valid object name.

• table_name

  Specifies the table on which the index is to be created.

• column_name

  Specifies a list of columns from the table to be included in the index. If the key option is used, the columns specified as keys must head this list and must appear in the same order in which they are specified in the key option. If the structure is rtree, only one column can be named.

• STRUCTURE = BTREE | ISAM | HASH | RTREE

  Specifies the storage structure of the index. Defaults to isam if the parameter is not included. If the structure is rtree, unique cannot be specified.

  Default: isam

• WITH with-clause

  Specifies a comma-separated list of any of the following items:

  • KEY = (columnlist)

    Specifies the columns on which the index is keyed. If this parameter is not included, the index is keyed on the columns in the index definition. If the structure is rtree, only one column can be named.

  • FILLFACTOR = n

    Specifies the percentage of each primary data page that can be filled with rows.

    Limits: 1 to 100 and must be expressed as an integer literal or integer variable.

    Default: Default values differ for each storage structure.

  • MINPAGES = n

    Defines the minimum number of primary pages a hash or compressed hash index table must have. The value can be expressed as an integer literal or integer variable.

    Default: 16 for a hash table; 1 for a compressed hash table.

  • MAXPAGES = n

    Defines the maximum number of primary pages that a hash or compressed hash index can have. The value can be expressed as an integer literal or integer variable.

    Default: No default

  • LEAFFILL = n

    Defines the percentage full each leaf index page is when the index is created. This option can be used when creating an index with a btree or compressed btree structure.

    Limits: 1 to 100 and must be an integer literal or integer variable.

  • NONLEAFFILL = n

    Specifies the percentage full each nonleaf index page is when the index is created. This option can be used when creating an index with a btree or compressed btree structure.

    Limits: 1 to 100, and must be an integer literal or integer variable.

  • LOCATION = (location_name {, location_name})

    Specifies the areas on which the index is created. Location_name must be a string literal or string variable.

    Default: The default area for the database

  • ALLOCATION = n

    Specifies the number of pages initially allocated for the index.

    Limits: An integer between 4 and 8,388,607

    Default: 4

  • EXTEND = n

    Specifies the number of pages by which the index is extended when more space is required.

    Limits: An integer between 1and 8,388,607

    Default: 16

  • COMPRESSION [= ([[NO]KEY] [,[NO|HI]DATA])] | NOCOMPRESSION

    Specifies whether the index key and data are to be compressed. If the structure is RTREE, compression cannot be specified.

    Default: NOCOMPRESSION

  • [NO]PERSISTENCE

    Specifies whether the modify statement recreates the index when its base table is modified.

    Default: nopersistence (indexes are not recreated).

  • UNIQUE_SCOPE = STATEMENT | ROW

    For unique indexes only. Specifies whether rows are checked for uniqueness one-by-one as they are inserted or after the update is complete. If the structure is rtree, unique_scope cannot be specified.

    Default: unique_scope = row

  • RANGE = ((min_x, min_y), (max_x, max_y))

    For RTREE indexes only. Specifies the minimum and maximum values of the index column.

    Limits: The values must have the same data type as the index column, either integer4 or float8. The RANGE parameter must be specified if the structure is RTREE.

  • PAGE_SIZE = n

    Specifies page size.

  • PRIORITY = cache_priority

    Allows tables to be assigned fixed priorities

    Limits: An integer between 0 and 8

Note: If II_DECIMAL is set to comma, be sure that when SQL syntax requires a comma (such as a list of table columns or SQL functions with several parameters), that the comma is followed by a space. For example:

select col1, ifnull(col2, 0), left(col4, 22) from t1:

Description

The index contains the columns specified. Any number of indexes can be created for a table, but each index can contain no more than 32 columns. The contents of indexes are sorted in ascending order by default.

Indexes can improve query processing. To obtain the greatest benefit, create indexes that contain all of the columns that are generally queried. The index must be keyed on a subset of those columns.

By default, the index is keyed on the columns in the column list, in the order they are specified. If the key option is specified, the index is keyed on the columns specified in the key list, in the order specified. For example, if you issue the statement:

create index nameidx on employee
        (last, first, phone);

you create an index called, nameidx, on the employee table that is keyed on the columns last, first, and phone in that order.

However, if you issue the statement:

create index nameidx on employee
(last, first, phone)
       with key = (last, first);

the index is keyed only on the two columns, last and first.

The columns specified in the key column list must be a subset of the columns in the main column list. A long varchar column cannot be specified as part of a key.