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
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
- 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 LOCATE_IN_STRING RPAD BSON_TO_JSON DLURLSCHEME LPAD SCORE CARDINALITY DLURLSERVER MAX_CARDINALITY SOUNDEX CONTAINS DLVALUE MONTHNAME TABLE_NAME CURDATE ENCRYPT_AES MONTHS_BETWEEN TABLE_SCHEMA CURTIME ENCRYPT_RC2 NEXT_DAY TIMESTAMP_FORMAT DATAPARTITIONNAME ENCRYPT_TDES NOW TIMESTAMPDIFF DATAPARTITIONNUM GENERATE_UNIQUE OVERLAY TRUNC_TIMESTAMP DAYNAME GETHINT RAISE_ERROR VARCHAR_FORMAT DBPARTITIONNAME IDENTITY_VAL_LOCAL RAND VERIFY_GROUP_FOR_USER DECRYPT_BINARY INSERT REGEXP_COUNT WEEK_ISO DECRYPT_BIT INTERPRET REGEXP_INSTR WRAP DECRYPT_CHAR JSON_ARRAY REGEXP_REPLACE XMLPARSE DECRYPT_DB JSON_OBJECT REGEXP_SUBSTR XMLVALIDATE DIFFERENCE JSON_QUERY REPEAT XSLTRANSFORM DLURLCOMPLETE 1 JSON_TO_BSON REPLACE DLURLPATH JSON_VALUE ROUND_TIMESTAMP 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.
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.
The date and time format used for date and time result columns is ISO.
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 thread 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))