Skip to main content
Previous sectionNext section

DROP INDEX

Removes an index.

Synopsis

DROP INDEX index-name [ON [TABLE] table-name]

DROP INDEX table-name.index-name

Arguments

Argument Description
index-name The name of the index to be deleted. index-name is the SQL version of the name, which can include underscores and other punctuation. It is listed in the table’s Management Portal SQL Catalog Details as the SQL Map Name.
ON table-name
or
ON TABLE table-name
Optional — The name of the table associated with the index. You can specify the table-name using either syntax: The first syntax uses the ON clause; the TABLE keyword is optional. The second syntax uses the qualified name syntax schema-name.table-name.index-name. A table-name can be qualified (schema.table), or unqualified (table). An unqualified table name takes the default schema name. If you omit the table-name entirely, Caché deletes the first index found that matches index-name, as described below.

Description

A DROP INDEX statement deletes an index from a table definition. You can use DROP INDEX to delete a standard index, bitmap index, or bitslice index. You can use DROP INDEX to delete a unique constraint or a primary key constraint by deleting the corresponding Unique index. You cannot use DROP INDEX to delete a Bitmap Extent index or a Master Map (Data/Master) IDKEY index.

You may wish to delete an index for any of the following reasons:

  • You intend to perform large numbers of INSERT, UPDATE, or DELETE operations on a table. Rather than accepting the performance overhead of having each of these operations write to the index, you can use the %NOINDEX option for the operation. Or, in certain cases, it may be preferable to delete the index, perform the bulk changes to the database, and then recreate the index and populate it.

  • An index exists for a field or combination of fields that are not used for query operations. In this case, the performance overhead of maintaining the index may not be worthwhile.

  • An index exists for a field or combination of fields that now contain large amounts of duplicate data. In this case, the minimal gain to query performance may not be worthwhile.

You cannot drop an IDKEY index when there is data in the table. Attempting to do so generates an SQLCODE -325 error.

Privileges and Locking

The DROP INDEX command is a privileged operation. The user must have %ALTER_TABLE administrative privilege to execute DROP 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.

DROP 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 DROP 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 DROP INDEX operation.

Index Name

When specify an index-name to create an index, the system generates a corresponding class index name by stripping out any punctuation characters; it retains the index-name you specified in the class as the SqlName value for the index (the SQL map name). When you specify an index-name to DROP INDEX, you specify the name including the punctuation, which is listed in the table’s Management Portal SQL Catalog Details as the SQL Map Name. For example, you specify the generated SQL Map Name for a Unique constraint (MYTABLE_UNIQUE2) not the Index Name (MYTABLEUNIQUE2). This index-name is not case-sensitive.

Table Name

You can specify the table associated with the index using either DROP INDEX syntax form:

  • index-name ON TABLE syntax: specifying the table name is optional. If omitted, Caché searches all of the classes in the namespace for the corresponding index.

  • table-name.index-name syntax: specifying the table name is required.

In either syntax, the table name can be unqualified (table), or qualified (schema.table). If the schema name is omitted, the default schema name is used.

If DROP INDEX does not specify a table name, Caché searches through all indices for an index SqlName matching index-name, or an index name matching index-name for indices where an SqlName is not specified for the index. If Caché finds no matching indices in any class, an SQLCODE -333 error is generated, indicating no such index exists. If Caché finds more than one matching index, DROP INDEX cannot determine which index to drop; it issues an SQLCODE -334 error: “Index name is ambiguous. Index found in multiple tables.” Index names in Caché are not unique per namespace.

Nonexistent Index

If you try to delete a nonexistent index, DROP INDEX issues an SQLCODE -333 error, by default. However, this default can be overridden system-wide by setting a configuration option as follows:

  • The $SYSTEM.SQL.SetDDLNo333() method call. To determine the current setting, call $SYSTEM.SQL.CurrentSettings(), which displays a Allow DDL DROP of non-existent index setting.

  • Go to the Management Portal, select System, Configuration, General SQL Settings. View the current setting of Allow DDL DROP of Non-existent Index.

The default is “No” (0). By default, Caché issues an SQLCODE -333 error. This is the recommended setting for this option. Set this option to “Yes” (1) if you want a DROP INDEX for a nonexistent index to perform no operation and issue no error message. For further details, refer to SQL configuration settings described in Caché Advanced Configuration Settings Reference.

Table Name

If you specify the optional table-name, it must correspond to an existing table.

  • If the specified table-name does not exist, Caché issues an SQLCODE -30 error and sets %msg to Table 'SQLUser.tname' does not exist.

  • If the specified table-name exists but does not have an index named index-name, Caché issues an SQLCODE -333 error and sets %msg to Attempt to DROP INDEX 'MyIndex' on table SQLUSER.TNAME failed - index not found.

  • If the specified table-name is a view, Caché issues an SQLCODE -333 error and sets %msg to Attempt to DROP INDEX 'EmpSalaryIndex' on view SQLUSER.VNAME failed. Indices only supported for tables, not views.

Examples

The first example creates a table named Employee, which is used in all of the examples in this section.

The following embedded SQL example creates an index named "EmpSalaryIndex" and later removes it. Note that here DROP INDEX does not specify the table associated with the index; it assumes that "EmpSalaryIndex" is a unique index name in this namespace.

  &sql(CREATE TABLE Employee (
  EMPNUM     INT NOT NULL,
  NAMELAST   CHAR(30) NOT NULL,
  NAMEFIRST  CHAR(30) NOT NULL,
  STARTDATE  TIMESTAMP,
  SALARY     MONEY,
  ACCRUEDVACATION   INT,
  ACCRUEDSICKLEAVE  INT,
  CONSTRAINT EMPLOYEEPK PRIMARY KEY (EMPNUM))
  )
  WRITE !,"SQLCODE=",SQLCODE," Created a table"
  &sql(CREATE INDEX EmpSalaryIndex
       ON TABLE Employee
       (Namelast,Salary))
  WRITE !,"SQLCODE=",SQLCODE," Created an index"
  /* use the index */
  NEW SQLCODE,%msg
  &sql(DROP INDEX EmpSalaryIndex)
  WRITE !,"SQLCODE=",SQLCODE," Deleted an index"
  WRITE !,"message",%msg
Copy code to clipboard

The following embedded SQL example specifies the table associated with the index to be dropped using an ON TABLE clause:

  &sql(CREATE INDEX EmpVacaIndex
       ON TABLE Employee
       (NameLast,AccruedVacation))
  WRITE !,"SQLCODE=",SQLCODE," Created an index"
  /* use the index */
  &sql(DROP INDEX EmpVacaIndex ON TABLE Employee)
  WRITE !,"SQLCODE=",SQLCODE," Deleted an index"
Copy code to clipboard

The following embedded SQL example specifies the table associated with the index to be dropped using qualified name syntax:

  &sql(CREATE INDEX EmpSickIndex
       ON TABLE Employee
       (NameLast,AccruedSickLeave))
  WRITE !,"SQLCODE=",SQLCODE," Created an index"
  /* use the index */
  &sql(DROP INDEX Employee.EmpSickIndex)
  WRITE !,"SQLCODE=",SQLCODE," Deleted an index"
Copy code to clipboard

The following command attempts to drop a nonexistent index. It generates an SQLCODE -333 error:

DROP INDEX PeopleIndex ON TABLE Employee
Copy code to clipboard

See Also