CREATE INDEX statement A CREATE INDEX statement creates an index on a table. Indexes can be on one or more columns in the table. CREATE INDEX statementDescending indexesIndexescreated in descending order Backing indexescreated automatically to enforce primary key, unique, and foreign key constraintsIndexesautomatic creation of for primary key, unique, and foreign key constraints Indexesspecifying page size for SyntaxCREATE [ UNIQUE ] INDEX indexName ON tableName ( simpleColumnName [ ASC | DESC ] [ , simpleColumnName [ ASC | DESC ] ]* )

The maximum number of columns for an index key in is 16.

An index name cannot exceed 128 characters.

A column must not be named more than once in a single CREATE INDEX statement. Different indexes can name the same column, however.

does not support indexing on columns with user-defined data types or with the data types LONG VARCHAR, BLOB, CLOB, or XML.

can use indexes to improve the performance of data manipulation statements (see ). In addition, UNIQUE indexes provide a form of data integrity checking.

Index names are unique within a schema. (Some database systems allow different tables in a single schema to have indexes of the same name, but does not.) Both index and table are assumed to be in the same schema if a schema name is specified for one of the names, but not the other. If schema names are specified for both index and table, an exception will be thrown if the schema names are not the same. If no schema name is specified for either table or index, the current schema is used.

By default, uses the ascending order of each column to create the index. Specifying ASC after the column name does not alter the default behavior. The DESC keyword after the column name causes to use descending order for the column to create the index. Using the descending order for a column can help improve the performance of queries that require the results in mixed sort order or descending order and for queries that select the minimum or maximum value of an indexed column.

Sorting and ordering of character data is controlled by the collation specified for a database when it is created, as well as the locale of the database. For details, see collation=collation attribute and territory=ll_CC attribute, as well as the sections "Creating a database with locale-based collation", "Creating a case-insensitive database", and "Character-based collation in " in the .

If a qualified index name is specified, the schema name cannot begin with SYS.
Indexes and constraints

Unique, primary key, and foreign key constraints generate indexes that enforce or "back" the constraint (and are thus sometimes called backing indexes). If a column or set of columns has a UNIQUE or PRIMARY KEY constraint on it, you can not create an index on those columns. has already created it for you with a system-generated name. System-generated names for indexes that back up constraints are easy to find by querying the system tables if you name your constraint. Adding a PRIMARY KEY or UNIQUE constraint when an existing UNIQUE index exists on the same set of columns will result in two physical indexes on the table for the same set of columns. One index is the original UNIQUE index and one is the backing index for the new constraint.

To find out the name of the index that backs a constraint called FLIGHTS_PK:

SELECT CONGLOMERATENAME FROM SYS.SYSCONGLOMERATES, SYS.SYSCONSTRAINTS WHERE SYS.SYSCONGLOMERATES.TABLEID = SYSCONSTRAINTS.TABLEID AND CONSTRAINTNAME = 'FLIGHTS_PK'
CREATE INDEX OrigIndex ON Flights(orig_airport); -- money is usually ordered from greatest to least, -- so create the index using the descending order CREATE INDEX PAY_DESC ON SAMP.EMPLOYEE (SALARY); -- use a larger page size for the index call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','8192'); CREATE INDEX IXSALE ON SAMP.SALES (SALES); call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize',NULL);
Page size and key sizeThe size of the key columns in an index must be equal to or smaller than half the page size. If the length of the key columns in an existing row in a table is larger than half the page size of the index, creating an index on those key columns for the table will fail. This error only occurs when creating an index if an existing row in the table fails the criteria. After an index is created, inserts may fail if the size of their associated key exceeds the criteria.
Statement dependency system

Prepared statements that involve SELECT, INSERT, UPDATE, UPDATE WHERE CURRENT, DELETE, and DELETE WHERE CURRENT on the table referenced by the CREATE INDEX statement are invalidated when the index is created. Open cursors on the table are not affected.