Mimer SQL Reference Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


CREATE INDEX


Creates a secondary index on one or more columns of a table.

Usage

Embedded/Interactive/ODBC/JDBC.

Description

A secondary index is created on the column(s) in the table as specified. The index is stored in the data dictionary under the given name. The secondary index is used internally by the optimizer to improve the efficiency of a search.

If index-name is specified in its unqualified form, the index will be created in the schema which has the same name as the current ident.

If index-name is specified in its fully qualified form (i.e. schema-name.index-name) the index will be created in the named schema (in this case, the current ident must be the creator of the specified schema).

If UNIQUE is specified each index value (i.e. the value of all index columns together) is only allowed once. In this context two null values are considered equal. It is recommended that UNIQUE is used in the create table statement rather than in the create index statement.

ASC and DESC indicate the sort order of the column within the index. If neither is specified, then ASC is implicit. This makes an index appropriate for queries with a matching ORDER BY specification.

Restrictions

Two indexes with the same name cannot belong to the same schema.

An index must belong to the same schema as the table on which it is created.

Secondary indexes may only be created on base tables, not on views.

UNIQUE indexes may only be created on tables in databanks defined with the LOG or TRANS transaction option.

Notes

Each column name must identify an existing column of the table. The same column may not be identified more than once.

Mimer SQL can make use of an index in both the forward and backward direction. It is therefore immaterial whether ASC or DESC is specified if all the index columns have the same sorting direction.

Secondary indexes are automatically maintained and are invisible to the user. The index is used automatically when it provides better efficiency.

Any column may be specified as a secondary index.

Note: Table columns that are in the primary key, a unique key or used in a foreign key reference are automatically indexed (in the order in which they are defined in the key). Therefore, explicitly creating an index on these columns will not improve performance.
Example

Consider a table with columns A, B and C of which A and B form the primary key, in that order. An index is automatically created for the column combination A,B. Therefore, there is no advantage in explicitly creating an index on column A or on the column combination A,B. Secondary indexes may, however, be advantageous on column B alone or on combinations such as B,A or A,C.

Standard Compliance

Standard
Compliance
Comments
X/Open-95
YES
Fully compliant.
SQL92
MIMER EXTENSION
The CREATE INDEX statement is a Mimer SQL extension.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Reference Manual TOC PREV NEXT INDEX