Skip to main content

CREATE INDEX (SQL)

Creates an index for a table.

Synopsis

CREATE index-type INDEX index-name
  ON [TABLE] table-name (field-name, ...)
  [AS index-class-name [ (parameter-name = parameter_value, ... ) ] ]
  [WITH DATA  (datafield-name, ...)]
  [ [ IMMEDIATE | DEFER ] [BUILD] ]

Arguments

index-type

An optional argument that specifies the type of index to be created. The following are the options for the index type:

  • UNIQUE: A constraint that ensures there will not be two rows in the table with identical values in all the fields in the index. You cannot specify this keyword for a bitmap or bitslice index.

    The UNIQUE keyword can be followed by (or replaced by) the CLUSTERED or NONCLUSTERED keywords. These keywords are no-ops; they are provided for compatibility with other vendors.

  • BITMAP: Indicates that a bitmap index should be created. A bitmap index enables rapid queries on fields with a small number of distinct values.

  • BITMAPEXTENT: Indicates that a bitmapextent index should be created. At most one bitmapextent index can be created for a table. No field-name is specified with BITMAPEXTENT.

  • BITSLICE: Indicates that a bitslice index should be created. A bitslice index enables very fast evaluation of certain expressions, such as sums and range conditions. This is a specialized index type, which should only be used to solve very specific problems.

  • COLUMNAR: Indicates that a columnar index should be created. A columnar index enables very fast queries, especially ones involving filtering and aggregation operations, on columns whose underlying data is stored across rows. Columnar indexes are an experimental feature for 2022.2.

index-name

The index being defined. The name is an identifier.

table-name

The name of an existing table for which the index is being defined. You cannot create an index for a view. A table-name can be qualified (schema.table), or unqualified (table). An unqualified table name takes the default schema name.

field-name

One or more field names that serve as the basis for the index. Field names must be enclosed in parentheses. Multiple field names are separated by commas.

Each field name can be followed by an ASC or DESC keyword. These keywords are no-ops; they are provided for compatibility with other vendors.

AS index-class-name

An optional argument specifying a class that defines an index, optionally followed by parentheses enclosing one or more comma-separated pairs of parameter names and associated values.

WITH DATA (datafield-name)

An optional argument that specifies one or more field names to be defined as Data properties for the index. Field names must be enclosed in parentheses. Multiple field names are separated by commas. You cannot specify a WITH DATA clause when specifying a BITMAP or BITSLICE index.

IMMEDIATE BUILD

An optional argument that specifies to build the index as soon as you create it. Indexes build immediately by default, so this clause can be omitted. The BUILD keyword is optional.

DEFER BUILD

An optional argument that specifies to disable building the index upon creation. This option also marks the index as not selectable, making it unavailable for use in queries. To later use the index, you must build it using BUILD INDEX and then make it selectable by using the SetMapSelectability()Opens in a new tab method; you can view whether a map is selectable or not in the Management Portal by navigating to System Explorer > SQL > Catalog Details and selecting the Maps/Indices button. The BUILD keyword is optional.

See additional compatibility syntax below.

Description

CREATE INDEX creates a sorted index on the specified field (or fields) of the named table. InterSystems IRIS uses indexes to improve performance of query operations. InterSystems IRIS automatically maintains indexes during INSERT, UPDATE, and DELETE operations, and this index maintenance may negatively affect performance of these data modification operations.

You can create an index using the CREATE INDEX command or by adding an index definition to a class definition, as described in Defining and Building Indexes. You can delete an index by using the DROP INDEX command.

For information about properties on which you can and cannot create indexes, see Properties That Can Be Indexed.

CREATE INDEX can be used to create any of the following types of index:

  • A regular index (Type=index): Specify either CREATE INDEX (for non-unique values) or CREATE UNIQUE INDEX (for unique values).

  • A bitmap index (Type=bitmap): Specify CREATE BITMAP INDEX.

  • A bitslice index (Type=bitslice): Specify CREATE BITSLICE INDEX.

  • A columnar index (Type=columnar): Specify CREATE COLUMNAR INDEX.

You can also define an index using the %Dictionary.IndexDefinitionOpens in a new tab class.

You can use CREATE INDEX to add an index to a sharded table.

For information about indexes at the class level, see %Library.FunctionalIndex.

Privileges and Locking

The CREATE INDEX command is a privileged operation. The user must have %ALTER_TABLE administrative privilege to execute CREATE INDEX. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have %ALTER_TABLE privileges. You can use the GRANT command to assign %ALTER_TABLE privileges to a user or role, if you hold appropriate granting privileges. Administrative privileges are namespace-specific. For further details, refer to Privileges.

The user must have %ALTER privilege on the specified table. If the user is the Owner (creator) of the table, the user is automatically granted %ALTER privilege for that table. Otherwise, the user must be granted %ALTER privilege for the table. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have required %ALTER privilege needed to change the table definition for 'Schema.TableName'. You can determine if the current user has %ALTER privilege by invoking the %CHECKPRIV command. You can use the GRANT command to assign %ALTER privilege to a specified table. For further details, refer to Privileges in Using InterSystems SQL.

  • CREATE INDEX cannot be used on a table projected from a persistent class, unless the table class definition includes [DdlAllowed]. Otherwise, the operation fails with an SQLCODE -300 error with the %msg DDL not enabled for class 'Schema.tablename'.

  • CREATE INDEX cannot be used on a table projected from a deployed persistent class. This operation fails with an SQLCODE -400 error with the %msg Unable to execute DDL that modifies a deployed class: 'classname'.

The CREATE INDEX statement acquires a table-level lock on table-name. This prevents other processes from modifying the table’s data. This lock is automatically released at the conclusion of the CREATE INDEX operation. CREATE INDEX maintains a lock on the corresponding class definition until the completion of the create index operation, including the population of the index data.

To create an index, the table cannot be locked by another process in either EXCLUSIVE MODE or SHARE MODE. Attempting a CREATE INDEX operation on a locked table results in an SQLCODE -110 error, with a %msg such as the following: Unable to acquire exclusive table lock for table 'Sample.MyTest'.

Options Supported for Compatibility Only

InterSystems SQL accepts the following CREATE INDEX options for parsing purposes only, to aid in the conversion of existing SQL code to InterSystems SQL. These options do not provide any actual functionality.

CLUSTERED | NONCLUSTERED owner.catalog. ASC | DESC

The following is an example showing the placement of these no-op keywords:

CREATE UNIQUE CLUSTERED INDEX index-name   ON TABLE owner.catalog.schema.table    (field1 ASC, field2 DESC)

Index Name

The name of an index must be unique within a given table. Index names follow identifier conventions, subject to the restrictions below. By default, index names are simple identifiers; an index name can be a delimited identifier. An index name should not exceed 128 characters. Index names are not case-sensitive.

InterSystems IRIS uses the name you supply (which it refers to as the “SqlName”) to generate a corresponding index property name in the class and the global. This index property name contains only alphanumeric characters (letters and numbers) and is a maximum of 96 characters in length. To generate an index property name, InterSystems IRIS first strips punctuation characters from the SqlName you supply, and then generates a unique identifier of 96 (or less) characters to create a unique index property name.

  • An index name can be the same as a field, table, or view name, but such name duplication is not advised.

  • An index property name (after punctuation stripping) must be unique. If you specify a duplicate SQL index name, the system generates an SQLCODE -324 error. If you specify an SQL index name that differs only in punctuation characters from an existing SQL index name, InterSystems IRIS substitutes a capital letter (beginning with “A”) for the final character to create a unique index property name. Therefore it is possible (though not advisable) to create SQL index names that differ only in their punctuation characters.

  • An index property name must begin with a letter. Therefore, either the first character of the index name or the first character after initial punctuation characters are stripped must be a letter. A valid letter is a character that passes the $ZNAME test. If the first character of the SQL index name is a punctuation character (% or _) and the second character is a number, InterSystems IRIS appends a lowercase “n” as the first character of the stripped index property name.

  • An index name may be much longer than 31 characters, but index names that differ in their first 31 alphanumeric characters are much easier to work with.

The Management Portal SQL interface Catalog Details displays the SQL index name (SQL Map Name) and the corresponding index property name (Index Name) for each index.

What happens when you try to create an index with the same name as an existing index is described below.

Existing Index

By default, InterSystems IRIS rejects an attempt to create an index that has the same name as an existing index for that table and issues an SQLCODE -324 error. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays a Allow DDL CREATE INDEX for existing index setting. The deault is 0, which is the recommended setting for this option. If this option is set to 1, InterSystems IRIS deletes the existing index from the class definition and then recreates it by performing the CREATE INDEX. It deletes the named index from the table specified in CREATE INDEX. This option permits the delete/recreate of a UNIQUE constraint index (which cannot be done using a DROP INDEX command). To delete/recreate a primary key index, refer to the ALTER TABLE command.

From the Management Portal, System Administration, Configuration, SQL and Object Settings, SQL you can set this option (and other similar create, alter, and drop options) system-wide by selecting the Ignore redundant DDL statements check box.

However, even if this option is set to allow the recreating of an existing index, you cannot recreate a Primary Key IDKEY index if the table contains data. Attempting to do so generates an SQLCODE -324 error.

Table Name

You must specify the name of an existing table.

  • If table-name is a nonexistent table, CREATE INDEX fails with an SQLCODE -30 error, and sets %msg to Table 'SQLUSER.MYTABLE' does not exist.

  • If table-name is a view, CREATE INDEX fails with an SQLCODE -30 error, and sets %msg to Attempt to CREATE INDEX 'My_Index' on view SQLUSER.MYVIEW failed. Indices only supported for tables, not views..

Creating an index modifies the table’s definition; if you do not have permission to change the table definition, CREATE INDEX fails with an SQLCODE -300 error, and sets %msg to DDL not enabled for class 'schema.tablename'.

Field Names

You must specify at least one field name to index on. Specify a field name or a comma-separated list of field names enclosed in parentheses. Duplicate field names are permitted and preserved in the index definition. Specifying more than one field may improve performance of GROUP BY operations, for example, group by state and then by city within each state. Generally, you should avoid indexing on a field or fields that have large amounts of duplicate data. For example, in a database of people, indexing on a Name field would be appropriate because most names are unique. Indexing on a State field would (in most cases) not be appropriate because of the large number of duplicate data values. The fields you specify must either be defined in the table or in the superclass of the table’s persistent class. (all classes must, of course, have been compiled.) Specifying a nonexistent field generates an SQLCODE -31 error.

In addition to ordinary data fields, you can use CREATE INDEX to create an index:

You cannot create an index on a stream value field.

You cannot create an index with multiple IDKEY fields if one of the IDKEY fields (properties) is SQL Computed. This limitation does not apply to a single field IDKEY index. Because multiple IDKEY fields in an index are delimited using the “||” (double vertical bar) characters, you cannot include this character string in IDKEY field data.

Field in an Embedded Object (%SerialObject)

To index a field in an embedded object, you create an index in the table (%Persistent class) referencing that embedded object. In CREATE INDEX the field-name specifies the name of the referencing field in the table (%Persistent object) joined by an underbar to the field name in the embedded object (%SerialObject), as shown in the following example:

CREATE INDEX StateIdx ON TABLE Sample.Person (Home_State)

Here Home is a field in Sample.Person that references the embedded object Sample.Address, which contains the State field.

Only those embedded object records associated with the persistent class referencing property are indexed. You cannot index a %SerialObject property directly.

For further details on defining embedded objects (also known as serial objects) refer to Embedded Object (%SerialObject); for further details on indexing a property (field) defined in an embedded object, refer to Indexing an Embedded Object (%SerialObject) Property.

Index Class Name

This optional syntax allow users to specify a class and parameters for a functional index using SQL.

An SQL example is:

CREATE INDEX HistIdx ON TABLE Sample.Person (MedicalHistory) AS %iFind.Index.Basic (LANGUAGE='en', LOWER=1)

For further details, refer to Indexing Sources for SQL Search.

WITH DATA Clause

Specifying this clause may allow a query to be resolved by only reading the index, which greatly reduces the amount of disk I/O, improving performance.

You should specify the same field in the field-name and the WITH DATA datafield-name if field-name uses string collation; this allows retrieval of the uncollated value without having to go to the Master Map. If the value in field-name does not use string collation there is no advantage to specifying this field in the WITH DATA datafield-name.

You can specify fields in WITH DATA datafield-name that are not indexed. This allows more queries to be satisfied from the index without going to the Master Map. The tradeoff is how many indexes you want to maintain; and that adding data to an index makes it quite a bit larger, which will slow down operations that don't need the data.

You can specify fields in WITH DATA datafield-name that are defined in the superclass for the table’s persistent class.

The UNIQUE Keyword

Using the UNIQUE keyword, you can specify that each record in the index has a unique value. More specifically, this ensures that no two records within the index (and hence in the table that contains the index) can have the same collated value. By default, most indexes use uppercase string collation (to make searches not case-sensitive). In this case, the values “Smith” and “SMITH” are considered to be equal and not unique. CREATE INDEX cannot specify non-default index string collation. You can specify a different string collation for individual indexes by defining the index in the class definition.

You can change the namespace default collation to make fields/properties case-sensitive by default. Changing this option requires recompiling all classes and rebuilding all indexes in the namespace. Go to the Management Portal, select the Classes option, select the namespace for your stored queries and use the Compile option to recompile the corresponding classes. Then rebuild all indexes. They will be case-sensitive.

Caution:

Do not rebuild indexes while the table’s data is being accessed by other users. Doing so may result in inaccurate query results.

The BITMAP Keyword

Using the BITMAP keyword, you can specify that this index will be a bitmap index. A bitmap index consists of one or more bit strings in which the bit position represents the row id, and each bit value represents the presence (1) or absence (0) of a specific value for the field in that row (or the value for the combined field-name fields). InterSystems SQL maintains these positional bits (as compressed bit strings) when inserting, updating, or deleting data; there is no significant difference in the performance of INSERT, UPDATE, or DELETE operations between using a bitmap index and a regular index. A bitmap index is highly efficient for many types of query operations. They have the following characteristics:

  • You can only define bitmap indexes in tables (classes) that either use system-assigned RowID with positive integer values, or use a primary key IDKEY to define custom ID values when the IDKEY is based on a single property with type %Integer and MINVAL > 0, or type %Numeric with SCALE = 0 and MINVAL > 0.

    You can use the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method SET status=$SYSTEM.SQL.Util.SetOption("BitmapFriendlyCheck",1,.oldval) to set a system-wide configuration parameter to check at compile time for this restriction, determining whether a defined bitmap index is allowed in a %Storage.SQL class. This check only applies to classes that use %Storage.SQL. The default is 0. You can use $SYSTEM.SQL.Util.GetOption("BitmapFriendlyCheck")Opens in a new tab to determine the current configuration of this option.

    You can only define a bitmap index for tables that use default (%Storage.Persistent) structure. Tables with compound keys, such as a child table, cannot use a bitmap index. If you use DDL (as opposed to using class definitions) to create a table, it meets this requirement and you can make use of bitmap indexes.

  • A bitmap index should only be used when the number of possible distinct field values is limited and relatively small. For example, a bitmap index is a good choice for a field for gender, or nationality, or timezone. A bitmap should not be used on a field with the UNIQUE constraint. A bitmap should not be used if a field can have more than 10,000 distinct values, or if multiple indexed fields can have more than 10,000 distinct values.

  • Bitmap indexes are very efficient when used in combination with logical AND and OR operations in a WHERE clause. If two or more fields are commonly queried in combination, it may be advantageous to define bitmap indexes for those fields.

For more details, see Bitmap Indices.

For more details, see Bitmap Indexes.

The BITMAPEXTENT Keyword

A bitmap extent index is a bitmap index for the table itself. InterSystems SQL uses this index to improve performance of COUNT(*), which returns the number of records (rows) in the table. A table can have, at most, one bitmap extent index. Attempting to create more than one bitmap extent index results in an SQLCODE -400 error with the %msg ERROR #5445: Multiple Extent indexes defined: DDLBEIndex.

All tables defined using CREATE TABLE automatically define a bitmap extent index. This automatically generated index is assigned the Index Name DDLBEIndex and the SQL MapName %%DDLBEIndex. A table defined as a class may have a bitmap extent index defined with an Index Name and SQL MapName of $ClassName.

You can use CREATE BITMAPEXTENT INDEX to add a bitmap extent index to a table, or to rename an automatically-generated bitmap extent index. The index-name you specify should be the class name corresponding to the table-name of the table. This becomes the SQL MapName for the index. No field-name or WITH DATA clause can be specified.

The following example creates a bitmap extent index with Index Name DDLBEIndex and the SQL MapName Patient. If Sample.Patient already had a %%DDLBEIndex bitmap extent index, this example renames that index to SQL MapName Patient:

CREATE BITMAPEXTENT INDEX Patient ON TABLE Sample.Patient

For more details, see Bitmap Extent Index.

The BITSLICE Keyword

Using the BITSLICE keyword, you can specify that this index will be a bitslice index. A bitslice index is used exclusively for numeric data which is used in calculations. A bitslice index represents each numeric data value as a binary bit string. Rather than indexing a numeric data value using a boolean flag (as in a bitmap index), a bitslice index creates a bit string for each numeric value, a separate bit string for each record. This is a highly specialized type of index that should only be used for fast aggregate calculations. For example, the following would be a candidate for a bitslice index:

SELECT SUM(Salary) FROM Sample.Employee

You can create a bitslice index for a string data field, but the bitslice index will represent these data values as canonical numbers. In other words, any non-numeric string, such as “abc” will be indexed as 0. This type of bitslice index could be used to rapidly count records that have a value for a string field and not count those that are NULL.

A bitslice index should not be used in a WHERE clause, because they are not used by the SQL query optimizer.

Populating and maintaining a bitslice index using INSERT, UPDATE, or DELETE operations is significantly slower than using a bitmap index or a regular index. Using several bitslice indexes, and/or using a bitslice index on a field that is frequently updated may have a significant performance cost.

A bitslice index can only be used for records that have system-assigned row Ids with positive integer values. A bitslice index can only be used on a single field-name. You cannot specify a WITH DATA clause.

For more details, see Bitslice Indexes.

The COLUMNAR Keyword

Using the COLUMNAR keyword, you can specify that this index will be a columnar index. A columnar index is used for a column that is frequently queried but whose table has an underlying row storage structure. By default, each row of a table is stored as a $LIST in a separate global subscript. For more details, see Columnar Indexes and Choose an SQL Table Storage Layout.

Rebuilding an Index

Creating an index using the CREATE INDEX statement automatically builds the index. However, there are cases when you may wish to explicitly rebuild an index.

You can build/re-build indexes as follows:

  • Using the BUILD INDEX SQL command.

  • Using the Management Portal to rebuild all of the indexes for a specified class (table).

  • Using the %BuildIndices() method.

To rebuild all indexes for an inactive table, execute the following:

  SET status = ##class(myschema.mytable).%BuildIndices()

By default, this command purges the indexes prior to rebuilding them. You can override this purge default and use the %PurgeIndices() method to explicitly purge specified indexes. If you call %BuildIndices() for a range of ID values, InterSystems IRIS does not purge indexes by default.

You can also purge/rebuild specified indexes:

  SET status = ##class(myschema.mytable).%BuildIndices($ListBuild("NameIDX","SpouseIDX"))

You may want to purge/rebuild an index if the index is corrupt or to change the case sensitivity of the index, as described above. To recompress a bitmap index, use the %SYS.Maint.BitmapOpens in a new tab methods, rather than purge/rebuild.

For more details, see Building Indexes.

Examples

The following example creates a table named Fred, and then creates an index named "FredIndex" (by stripping out the punctuation from the supplied name “Fred_Index”) on the Lastword and Firstword fields of the Fred table.

CREATE TABLE Fred (
  TESTNUM     INT NOT NULL,
  FIRSTWORD   CHAR (30) NOT NULL,
  LASTWORD    CHAR (30) NOT NULL,
  CONSTRAINT FredPK PRIMARY KEY (TESTNUM))

CREATE INDEX Fred_Index
ON TABLE Fred (LASTWORD,FIRSTWORD)

The following example creates an index, named “CityIndex” on the City field of the Staff table:

CREATE INDEX CityIndex ON Staff (City)

The following example creates an index, named “EmpIndex” on the EmpName field of the Staff table. The UNIQUE constraint is used to avoid having rows with identical values in the fields:

CREATE UNIQUE INDEX EmpIndex ON TABLE Staff (EmpName)

The following example creates a bitmap index, named “SKUIndex” on the SKU field of the Purchases table. The BITMAP keyword indicates that this is a bitmap index:

CREATE BITMAP INDEX SKUIndex ON TABLE Purchases (SKU)

See Also

FeedbackOpens in a new tab