CREATE INDEX

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 USAGE privilege on the schema 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 USAGE privilege on the schema 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.

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 and the FOR SYSTEM NAME clause is not used, 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. expression must contain a column reference. 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
• TRY_CAST specification
• 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 strongly typed distinct type
• Any function that is not deterministic
• The following built-in scalar functions:

  ATAN2	DLURLSERVER	JSON_QUERY	REPLACE
  BSON_TO_JSON	DLVALUE	JSON_TO_BSON	ROUND_TIMESTAMP
  CARDINALITY	ENCRYPT_AES	JSON_VALUE	RPAD
  CONTAINS	ENCRYPT_AES256	LOCATE_IN_STRING	SCORE
  CURDATE	ENCRYPT_RC2	LPAD	SOUNDEX
  CURTIME	ENCRYPT_TDES	MAX_CARDINALITY	TABLE_NAME
  DATAPARTITIONNAME	GENERATE_UNIQUE	MONTHNAME	TABLE_SCHEMA
  DATAPARTITIONNUM	GETHINT	MONTHS_BETWEEN	TIMESTAMP_FORMAT
  DAYNAME	HASH	NEXT_DAY	TIMESTAMPDIFF
  DBPARTITIONNAME	HASH_MD5	NOW	TIMESTAMPDIFF_BIG
  DECRYPT_BINARY	HASH_SHA1	OVERLAY	TRUNC_TIMESTAMP
  DECRYPT_BIT	HASH_SHA256	RAISE_ERROR	VARCHAR_FORMAT
  DECRYPT_CHAR	HASH_SHA512	RAND	VERIFY_GROUP_FOR_USER
  DECRYPT_DB	IDENTITY_VAL_LOCAL	REGEXP_COUNT	WEEK_ISO
  DIFFERENCE	INSERT	REGEXP_INSTR	WRAP
  DLURLCOMPLETE 1	INTERPRET	REGEXP_REPLACE	XMLPARSE
  DLURLPATH	JSON_ARRAY	REGEXP_SUBSTR	XMLVALIDATE
  DLURLPATHONLY	JSON_OBJECT	REPEAT	XSLTRANSFORM
  DLURLSCHEME
  1 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. 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.

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.

RENAME

Specifies the columns from table-name that will be included in the format for the index. The order of the columns in the index format will be the same as the order of the columns in the list. The columns in the index can also be renamed.

table-system-column-name

Identifies the system column name of a column in table-name.

index-system-column-name

Provides the system column name for the corresponding column in the index.

index-column-name

Provides the column name for the corresponding column in the index.

When this clause is used, the following rules apply:

• Each key-expression must contain only a column-name. It can be either the column name or the system column name of a column in table-name. The key-expression cannot include an AS clause to rename the column.
• If a list of table-system-column-names is specified, it must contain the same number of names as are provided in the index column name list.
• If a list of table-system-column-names is not specified, every index-system-column-name must be the system column name for a column in table-name.
• Each index-column-name and index-system-column-name must be unique and unqualified.
• Every column in key-expression must be included in the RENAME clause.

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.