Creates an index for a table.

CREATE [UNIQUE | BITMAP] INDEX index-name
 ON [TABLE] table-name (column-name [ASC | DESC], ...)

CREATE INDEX creates a sorted index on the specified columns of the named table. The name of an index must be unique within a given table. What happens when you try to create an index with the same name as an existing index is described below.

To create an index, you must have the ALTER privilege for table access in your user definition.

What happens when you try to create an index that has the same name as an existing index for that table depends on a setting of the Caché Configuration Manager. Go to the Advanced tab and list the SQL options to display the current value of: Allow DDL CREATE INDEX for Existing Index. The default is “No.” By default, Caché rejects an attempt to create an index with the name of an existing index for that table and returns an SQLCODE -324 error. This is the recommended setting for this option.

However, if this option is set to “Yes,” 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.

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 an upper case collation (to make searches case-insensitive). In this case, the values “Smith” and “SMITH” are considered to be equal and not unique.

Using the BITMAP keyword, you can specify that this index will be a bitmap index. A bitmap index maintains a list of row ids corresponding to its indexed values using a bitmap (a compressed set of binary digits). The SQL Query Processor can take advantage of bitmap indices to perform restrictions using set operations; that is, it can use logical AND and OR operations to execute table restrictions (WHERE clauses). There is no significant difference in the performance of INSERT, UPDATE, or DELETE operations between using bitmap and regular indices. You can only define a bitmap index for tables that use default (%CacheStorage) structure and have system-assigned numeric row ids (i.e., do not use the IDKEY mechanism). If you use DDL (as opposed to using class definitions) to create a table, then it will meet these requirements and you can make use of bitmap indices.

The following embedded SQL example creates an index named "PersonIndex" on the Name and Age columns of the Sample.Person table. It later removes (drops) the index:

  &sql(CREATE INDEX PersonIndex
       ON TABLE Sample.Person
       (Name,Age))
  IF SQLCODE=-324 {
      WRITE !,"Index already exists" }
  ELSE { WRITE !,"Created an index" }
  /* use the index */
  &sql(DROP INDEX PersonIndex)
  WRITE !,"Deleted an index"

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

CREATE INDEX CityIndex ON Staff (City)

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

CREATE UNIQUE INDEX EmpIndex ON TABLE Staff (EmpName)

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

CREATE BITMAP INDEX SKUIndex ON TABLE Purchases (SKU)

DROP INDEX