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.
- Administrative 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
- Administrative 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
- Administrative 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
- Administrative 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
- Administrative authority
For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.
Syntax
>>-CREATE--+----------------------------+-----------------------> +-UNIQUE--+----------------+-+ | '-WHERE NOT NULL-' | '-ENCODED VECTOR-------------' >--INDEX--index-name--+-------------------------------------------+--> '-FOR SYSTEM NAME--system-object-identifier-' .-,------------------------. V .-ASC--. | >--ON--table-name--(----key-expression--+------+-+--)-----------> '-DESC-' >--+-------------------------+--index-options------------------>< '-WHERE--search-condition-' key-expression |--expression--+--------------------------------------------------------------+--| | .-AS-. | '-+----+--column-name--+-------------------------------------+-' | .-COLUMN-. | '-FOR--+--------+--system-column-name-' index-options |--+-------------------------------------+----------------------> | .-DISTINCT-. | '-WITH--integer--+----------+--VALUES-' >--+-----------------+------------------------------------------> +-NOT PARTITIONED-+ '-PARTITIONED-----' >--+---------------------------------------------------------------+--> | .-,------------------------------------------. | | V | | '-INCLUDE--(----aggregate-function-name --(--expression--)-+--)-' .-PAGESIZE--64------. >--+-------------------+----------------------------------------> '-PAGESIZE--+-8---+-' +-16--+ +-32--+ +-128-+ +-256-+ '-512-' >--+---------------------------------------------------+--------> | .-ADD ALL COLUMNS----------. | '-RCDFMT--format-name--+--------------------------+-' +-ADD KEYS ONLY------------+ | .-,---------------. | | V | | '-ADD------column-name---+-' (1) >--+------------------+-----------------------------------------| '-media-preference-' media-preference .-UNIT ANY-. |--+-UNIT SSD-+-------------------------------------------------|
- 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
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.
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
- Complex expressions that contain LOBs (such as concatenation)
- Special registers
- Sequence references
- OLAP specifications
REGEXP_LIKE predicate
- ROW CHANGE expressions
- 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 DLURLCOMPLETE 1 LPAD
REPLACE
CARDINALITY
DLURLPATH MAX_CARDINALITY ROUND_TIMESTAMP
CONTAINS
DLURLPATHONLY MONTHNAME RPAD
CURDATE DLURLSCHEME MONTHS_BETWEEN SCORE CURTIME DLURLSERVER NEXT_DAY SOUNDEX DATAPARTITIONNAME DLVALUE NOW TIMESTAMP_FORMAT DATAPARTITIONNUM ENCRYPT_AES OVERLAY TIMESTAMPDIFF DAYNAME ENCRYPT_RC2 RAISE_ERROR
TRUNC_TIMESTAMP DBPARTITIONNAME ENCRYPT_TDES RAND
VARCHAR_FORMAT
DECRYPT_BINARY GENERATE_UNIQUE REGEXP_COUNT
WEEK_ISO
DECRYPT_BIT GETHINT REGEXP_INSTR
XMLPARSE
DECRYPT_CHAR IDENTITY_VAL_LOCAL REGEXP_REPLACE XMLVALIDATE DECRYPT_DB INSERT REGEXP_SUBSTR XSLTRANSFORM DIFFERENCE LOCATE_IN_STRING
REPEAT 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. The default value is 256.
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 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 a record format name is not specified:
- If no key columns are expressions and no key columns have been explicitly named using the AS clause, the index will share the format of table-name.
- Otherwise, the index will not share the format of table-name and the format-name is the same as the system-object-name of the index.
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.

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.
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 job executing the statement.
If system names were specified, the owner of the index is the user profile or group user profile of the job 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.
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))