CREATE INDEX

The CREATE INDEX statement creates an index on a table at the current server.

Invocation

This statement can be embedded in an application program or issued interactively. 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:

The privilege to create in the schema. For more information, see Privileges necessary to create in a schema.
Database administrator authority

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

The following system authorities:
- *USE to the Create Logical File (CRTLF) command
- *CHANGE to the data dictionary if the library into which the index is created is an SQL schema with a data dictionary
Database administrator authority

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

For the referenced table:
- The INDEX privilege on the table
- The system authority *EXECUTE on the library containing the table
Database administrator authority

If SQL names are specified and a user profile exists that has the same name as the library into which the table is created, and that name is different from the authorization ID of the statement, then the privileges held by the authorization ID of the statement must include at least one of the following:

The system authority *ADD to the user profile with that name
Database administrator authority

If a distinct type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:

For each distinct type identified in the statement:
- The USAGE privilege on the distinct type, and
- The system authority *EXECUTE on the library containing the distinct type
Database administrator authority

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.

Syntax

key-expression

index-options

media-preference

memory-preference

Notes:

¹ The index-options may be specified in any order.

Description

UNIQUE

Prevents the table from containing two or more rows with the same value of the index key. When UNIQUE is used, all null values for a column are considered equal. For example, if the key is a single column that can contain null values, that column can contain only one null value. The constraint is enforced when rows of the table are updated or new rows are inserted.

The constraint 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.

UNIQUE WHERE NOT NULL

Prevents the table from containing two or more rows with the same value of the index key, where all null values for a column are not considered equal. Multiple null values in a column are allowed. Otherwise, this is identical to UNIQUE.

ENCODED VECTOR

Specifies that the resulting index will be an encoded vector index (EVI).

An encoded vector index cannot be used to ensure an ordering of rows. It is used by the database manager to improve the performance of queries. For more information, see the Database Performance and Query Optimization topic collection.

index-name

Names the index. The name, including the implicit or explicit qualifier, must not be the same as an index, table, view, alias, or file that already exists at the current server.

If SQL names were specified, the index will be created in the schema specified by the implicit or explicit qualifier.

If system names were specified, the index name will be created in the schema that is specified by the qualifier. If not qualified, the index name will be created in the same schema as the table over which the index is created.

If the index name is not a valid system name Start of change and the FOR SYSTEM NAME clause is not used End of change , Db2® for i will generate a system name. For information about the rules for generating a name, see Rules for Table Name Generation.

FOR SYSTEM NAME system-object-identifier

Identifies the system-object-identifier of the index. system-object-identifier must not be the same as a table, view, alias, or index that already exists at the current server. The system-object-identifier must be an unqualified system identifier.

When system-object-identifier is specified, index-name must not be a valid system object name.

ON table-name

Identifies the table on which the index is to be created. The table-name must identify a base table (not a view) that exists at the current server.

If the table is a partitioned table, an alias may be specified which identifies a single partition. The created index will then only be created over the specified partition.

key-expression

Identifies a column or expression that will be part of the index key.

The number of keys defined for the index must not exceed 120, and the sum of their byte lengths must not exceed 32766-n, where n is the number of keys specified that allow nulls.

expression

If expression contains only a column-name, it must be an unqualified name that identifies a column of the table. Start of change

expression must contain a column reference. End of change

The same column-name cannot be specified more than once if:

a WHERE clause, INCLUDE clause, or RCDFMT clause is specified,
an expression is defined as part of an index key, or
a column is renamed using the AS clause.

If the expression is not a column name, the expression must not reference a column that contains a field procedure.

A column-name must not identify a LOB, XML, or DATALINK column, or a distinct type based on a LOB, XML, or DATALINK column. If the expression is not a column name, any intermediate result expression and the final result expression must not be a DATALINK, LOB, or XML data type. It must not contain any of the following:

Subqueries
Aggregate functions
Variables
Global variables
Parameter markers
Special registers
Sequence references
OLAP specifications
ROW CHANGE expressions
REGEXP_LIKE predicate
User-defined functions other than functions that were implicitly generated with the creation of a distinct type
Any function that is not deterministic

The following built-in scalar functions:

ATAN2	DLURLPATHONLY	LPAD	RPAD
CARDINALITY	DLURLSCHEME	MAX_CARDINALITY	SCORE
CONTAINS	DLURLSERVER	MONTHNAME	SOUNDEX
CURDATE	DLVALUE	MONTHS_BETWEEN	TABLE_NAME
CURTIME	ENCRYPT_AES	NEXT_DAY	TABLE_SCHEMA
DATAPARTITIONNAME	ENCRYPT_RC2	NOW	TIMESTAMP_FORMAT
DATAPARTITIONNUM	ENCRYPT_TDES	OVERLAY	TIMESTAMPDIFF
DAYNAME	GENERATE_UNIQUE	RAISE_ERROR	TRUNC_TIMESTAMP
DBPARTITIONNAME	GETHINT	RAND	VARCHAR_FORMAT
DECRYPT_BINARY	IDENTITY_VAL_LOCAL	REGEXP_COUNT	VERIFY_GROUP_FOR_USER
DECRYPT_BIT	INSERT	REGEXP_INSTR	WEEK_ISO
DECRYPT_CHAR	JSON_ARRAY	REGEXP_REPLACE	WRAP
DECRYPT_DB	JSON_OBJECT	REGEXP_SUBSTR	XMLPARSE
DIFFERENCE	JSON_QUERY	REPEAT	XMLVALIDATE
DLURLCOMPLETE ¹	JSON_VALUE	REPLACE	XSLTRANSFORM
DLURLPATH	LOCATE_IN_STRING	ROUND_TIMESTAMP
¹ For DataLinks with an attribute of FILE LINK CONTROL and READ PERMISSION DB.

column-name

Names a column of the index. Do not use the same name for more than one column of the index or for a system-column-name of the index.

If the expression is not a column name and is not named, a name will be generated for the index key column. The name will be SQLIXxxxxx, where xxxxx is a number that makes the column name unique for the index.

FOR COLUMN system-column-name

Provides an IBM® i name for the column. Do not use the same name for more than one column of the index or for a column-name of the index.

If the system-column-name is not specified, and the column-name is not a valid system-column-name, a system column name is generated. The name will be SQLIXxxxxx, where xxxxx is a number that makes the column name unique for the index.

ASC

Specifies that the index entries are to be kept in ascending order of the column values. ASC is the default.

DESC

Specifies that the index entries are to be kept in descending order of the column values.

Ordering is performed in accordance with the comparison rules described in Assignments and comparisons. The null value is higher than all other values.

WHERE search-condition

Specifies the condition to apply for a row to be included in the index. The search-condition cannot contain a predicate with a subquery. It must not contain any of the items listed as restrictions for key-expression.

WITH integer DISTINCT VALUES

Specifies the estimated number of distinct key values. This clause may be specified for any type of index.

For encoded vector indexes this is used to determine the initial size of the codes assigned to each distinct key value. Start of change Only 1, 2, and 4 byte codes are used. If the INCLUDE clause is not specified, the default value is 255 (a 1-byte code). Otherwise, a 4-byte code is used. During the create or rebuild of the index if the number of distinct values exceeds the maximum supported by the size of the code, the size of the code is increased. End of change

For non-encoded vector indexes, this clause is ignored.

PARTITIONED

Specifies that an index partition should be created for each data partition defined for the table using the specified columns. The table-name must identify a partitioned table. If the index is unique, the columns of the index must be the same or a superset of the columns of the data partition key. PARTITIONED is the default if the index is not unique and the table is partitioned.

NOT PARTITIONED

Specifies that a single index should be created that spans all of the data partitions defined for the table. The table-name must identify a partitioned table. NOT PARTITIONED is the default if the index is unique and the table is partitioned. An index on a table that is not partitioned is also by default not partitioned.

If an encoded vector index is specified, NOT PARTITIONED is not allowed.

PAGESIZE

Specifies the logical page used for the index in kilobytes. Indexes with larger logical page sizes are typically more efficient when scanned during query processing. Indexes with smaller logical page sizes are typically more efficient for simple index probes and individual key look ups.

The default value for PAGESIZE is determined by the length of the key and has a minimum value of 64.

If an encoded vector index is specified, PAGESIZE is not allowed.

INCLUDE

Specifies aggregate function expressions to be included in the index. These aggregates make it possible for the index to be used directly to return aggregate results for a query. INCLUDE is only allowed for an encoded vector index.

aggregate-function-name ( expression ): The aggregate function name must be one of the built-in functions AVG, COUNT, COUNT_BIG, SUM, STDDEV, STDDEV_SAMP, VARIANCE, or VARIANCE_SAMP. The DISTINCT keyword must not be specified. The expression argument of the aggregate function must not contain any of the items listed as restrictions for key-expression.

RCDFMT format-name

An unqualified name that designates the IBM i record format name of the index. A format-name is a system identifier.

If the INCLUDE keyword is specified, RCDFMT is not allowed.

ADD ALL COLUMNS: Specifies that all non-hidden columns of table-name will be added to the format for the index. All the columns will be defined in the same order as they appear in the format of table-name and will precede any expressions defined as index keys.
ADD KEYS ONLY: Specifies that only the columns specified as index key columns will be added to the format for the index. Other columns from table-name will not be added.
ADD column-name: Specifies that the listed columns will be added to the format for the index. The index key columns will be first, followed by the added columns.

media-preference

Specifies the preferred storage media for the index.

UNIT ANY: No storage media is preferred. Storage for the index will be allocated from any available storage media.
UNIT SSD: Solid state disk storage media is preferred. Storage for the index may be allocated from solid state disk storage media, if available.

memory-preference

KEEP IN MEMORY

Specifies whether the data for the index should be brought into a main storage pool when the data is used for a query.

NO: The data will not be brought into a main storage pool.
YES: The data will be brought into a main storage pool.

Notes

Effects of the statement: CREATE INDEX creates a description of the index. If the named table already contains data, CREATE INDEX creates the index entries for it. If the table does not yet contain data, the index entries are created when data is inserted into the table.

Collating sequence: Any index created over columns containing SBCS or mixed data is created with the collating sequence in effect at the time the statement is executed. For collating sequences other than *HEX, the key for SBCS data or mixed data is the weighted value of the key based on the collating sequence.

Index attributes: An index is created as a keyed logical file. When an index is created, the file wait time and record wait time attributes are set to the default that is specified on the WAITFILE and WAITRCD keywords of the Create Logical File (CRTLF) command.

Start of change The date and time format used for date and time result columns is ISO. End of change

An index created over a distributed table is created on all of the servers across which the table is distributed. For more information about distributed tables, see DB2® Multisystem.

Index ownership: If SQL names were specified:

If a user profile with the same name as the schema into which the index is created exists, the owner of the index is that user profile.
Otherwise, the owner of the index is the user profile or group user profile of the thread executing the statement.

If system names were specified, the owner of the index is the user profile or group user profile of the Start of change thread End of change executing the statement.

Index authority: If SQL names are used, indexes are created with the system authority of *EXCLUDE on *PUBLIC. If system names are used, indexes are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the schema.

If the owner of the index is a member of a group profile (GRPPRF keyword) and group authority is specified (GRPAUT keyword), that group profile will also have authority to the index.

Record format sharing: An index that defines a key column as an expression or that specifies the RCDFMT, WHERE, INCLUDE, or AS clauses does not share the format of table-name. Otherwise, the index will share the format of table-name and its format-name will be the same as the system-object-name of the index.

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: Create an index named DEPT_TYPE on the DEPARTMENT table. Arrange the index entries in ascending order by type of department, which is determined by the second and third characters of the department number (DEPTNO).

  CREATE INDEX DEPT_TYPE 
   ON DEPARTMENT (SUBSTR(DEPTNO,2,2))