Universal Database (netDB2) Introductory Guide

Create Index

The CREATE INDEX statement is used to create an index on a DB2 table.

An index specification:
Metadata that indicates to the optimizer that a data source table has an index. An index is an ordered set of pointers to rows of a base table. Each index is based on the values of data in one or more table columns. An index is an object that is separate from the data in the table. When an index is created, the database manager builds this structure and maintains it automatically.

Indexes are used by the database manager to:

1. Improve performance. In most cases, access to data is faster than without an index. An index cannot be created for a view. However, an index created for a table on which a view is based may improve the performance of operations on the view.
2. Ensure uniqueness. A table with a unique index cannot have rows with identical keys.

Syntax:
Create [UNIQUE] index {INDEXNAME}
ON [TABLENAME / NICKNAME]  (column name [ASC / DESC])
specification only  include  (column name)
[cluster] [PCTFREE 10/ PCTFREE INTEGER] [MINPCTUSED integer]
[disallow reverse scans / allow reverse scans]  

Explanation:

UNIQUE

If ON table-name is specified, UNIQUE prevents the table from containing two or more rows with the same value of the index key. The uniqueness is enforced at the end of the SQL statement that updates rows or inserts new rows. The uniqueness 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. When UNIQUE is used, null values are treated as any other values. For example, if the key is a single column that may contain null values, that column may contain no more than one null value. If ON nickname is specified, UNIQUE should be specified only if the data for the index key contains unique values for every row of the data source table. The uniqueness will not be checked.

Names the index or index specification. The name, including the implicit or explicit qualifier, must not identify an index or index specification that already exists.

The table-name names a table on which an index is to be created. The table base table (not a view) or a summary table described in the catalog. nickname is the nickname on which an index specification is to be created. The nickname references either a data source table whose index is described by the index specification, or a data source view that is based on such a table.

For an index, column-name identifies a column that is to be part of the index key. For an index specification, column-name is the name by which the federated server references a column of a data source table. Each column-name must be an unqualified name that identifies a column of the table. 16 columns or less may be specified. If table-name is a typed table, 15 columns or less may be specified.imust be a base table (not a view) or a summary table.

Puts the index entries in ascending order by the column. This is the default.

Puts the index entries in descending order by the column.

Indicates that this statement will be used to create an index specification that applies to the data source table referenced by nickname.

This keyword introduces a clause that specifies additional columns to be appended to the set of index key columns. Any columns included with this clause are not used to enforce uniqueness.The limits for the number of columns and sum of the length attributes apply to all of the columns in the unique key and in the index.INCLUDE is disallowed if nickname is specified.

Specifies that the index is the clustering index of the table. The cluster factor of a clustering index is maintained or improved dynamically as data is inserted into the associated table, by attempting to insert new rows physically close to the rows for which the key values of this index are in the same range.

Specifies what percentage of each index page to leave as free space when building the index. The first entry in a page is added without restriction. When additional entries are placed in an index page at least integer percent of free space is left on each page. The value of integer can range from 0 to 99.

Indicates whether indexes are reorganized online and the threshold for the minimum percentage of space used on an index leaf page If after a key is deleted from an index leaf page, the percentage of space used on the pageis at or below integer percentage, an attempt is made to merge the remaining keys on this page with those of a neighbouring page. If there is sufficient space on one of these pages, the merge is performed and one of the pages is deleted. The value of integer can be from 0 to 99.

Specifies that an index only supports forward scans or scanning of the index in the order defined at INDEX CREATE time. This is the default. DISALLOW REVERSE SCANS is disallowed if nickname is specified.

Specifies that an index can support both forward and reverse scans; that is, in the order defined at INDEX CREATE time and in the opposite (or reverse) order. ALLOW REVERSE SCANS is disallowed if nickname is specified.

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:
The nickname EMPLOYEE references a data source table called CURRENT_EMP. After this nickname was created, an index was defined on CURRENT_EMP. The columns chosen for the index key were WORKDEBT and JOB. Create an index specification that describes this index. Through this specification, the optimizer will know that the index exists and what its key is. With this information, the optimizer can improve its strategy to access the table.

CREATE UNIQUE INDEX JOB_BY_DEPT ON EMPLOYEE (WORKDEPT, JOB) SPECIFICATION ONLY