Skip to main content

CREATE INDEX

Creates an index for a table.

Synopsis

CREATE [UNIQUE | BITMAP | BITMAPEXTENT | BITSLICE ] INDEX index-name
        ON [TABLE] table-name
       (field-name, ...)
       [WITH DATA  (datafield-name, ...)]

Arguments

Argument Description
UNIQUE

Optional — 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 Optional — Indicates that a bitmap index should be created. A bitmap index enables rapid queries on fields with a small number of distinct values.
BITMAPEXTENT Optional — 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 Optional — 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.
index-name The index being defined. The name is an identifier. For further details, see the “Identifiers” chapter of Using Caché SQL.
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.

WITH DATA (datafield-name) Optional — 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.

See additional compatibility syntax below.

Description

CREATE INDEX creates a sorted index on the specified field (or fields) of the named table. Caché uses indices to improve performance of query operations. Caché automatically maintains indices 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 the Defining and Building Indices chapter of Caché SQL Optimization Guide. You can delete an index by using the DROP INDEX command.

CREATE INDEX can be used to create any of the following three 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.

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

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 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 in Using Caché SQL.

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 Caché SQL.

CREATE INDEX cannot be used on a table created by defining 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'>

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

Caché SQL accepts the following CREATE INDEX options for parsing purposes only, to aid in the conversion of existing SQL code to Caché 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.

Caché 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, Caché 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, Caché 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, Caché 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, Caché 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. This behavior is configurable, as follows:

The default is “No” (0). By default, Caché rejects an attempt to create an index with the name of an existing index for that table and issues an SQLCODE -324 error. This is the recommended setting for this option.

If this option is set to “Yes” (1), Caché 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.

However, even if this option is set to allow the recreating of an existing index, you cannot recreate an 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 exist in the table. 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.

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 indices 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.

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 indices 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 indices 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 an rebuilding all indices 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 indices. They will be case-sensitive.

Caution:

Do not rebuild indices 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). Caché 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 indices in tables (classes) that either use system-assigned ID with positive integer values, or use an 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.SetBitmapFriendlyCheck()Opens in a new tab method to set a systemwide configuration parameter to check at compile time for this restriction. You can use $SYSTEM.SQL.GetBitmapFriendlyCheck()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 (%CacheStorageOpens in a new tab) 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 indices.

  • 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 indices 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 indices for those fields.

For more details, refer to the “Bitmap Indices” section of the Defining and Building Indices chapter of Caché SQL Optimization Guide.

The BITMAPEXTENT Keyword

A bitmap extent index is a bitmap index for the table itself. Caché 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 indices defined: DDLBEIndex.

At Caché 2015.2 and subsequent, 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:

  ZNSPACE "Samples"
  &sql(CREATE BITMAPEXTENT INDEX Patient ON TABLE Sample.Patient)
  WRITE !,"SQL code: ",SQLCODE

For more details, refer to the “Bitmap Extent Index” section of the Defining and Building Indices chapter of Caché SQL Optimization Guide.

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 indices, 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, refer to the “Bitslice Indices” section of the Defining and Building Indices chapter of Caché SQL Optimization Guide.

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.

Caution:

You must take additional steps when rebuilding an index if the table’s data is being accessed by other users. Failing to do so may result in inaccurate query results. For more details, refer to Building Indices on an Active System.

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

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

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

You can also purge/rebuild specified indices:

  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.

You can also use the Management Portal to rebuild all of the indices for a specified class (table).

For more details, refer to Building Indices in the “Defining and Building Indices” chapter of Caché SQL Optimization Guide.

Examples

The following embedded SQL 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.

   &sql(CREATE TABLE Fred (
  TESTNUM     INT NOT NULL,
  FIRSTWORD   CHAR (30) NOT NULL,
  LASTWORD    CHAR (30) NOT NULL,
  CONSTRAINT FredPK PRIMARY KEY (TESTNUM))
  )
  IF SQLCODE=0 { WRITE !,"Table created" }
  ELSEIF SQLCODE=-201 { WRITE !,"Table already exists" }
  ELSE { WRITE !,"SQL table create error code is: ",SQLCODE
         QUIT }
  &sql(CREATE INDEX Fred_Index
       ON TABLE Fred
       (LASTWORD,FIRSTWORD))
  IF SQLCODE=-324 {
      WRITE !,"Index already exists" 
      QUIT }
  ELSEIF SQLCODE=0 { WRITE !,"Index created" }
  ELSE { WRITE !,"SQL index create error code is: ",SQLCODE 
         QUIT }

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