DROP INDEX (Delete index)

Function

DROP INDEX deletes an index.

Privileges

Owner of the index

The owner of an index can delete that index.

Users with the DBA privilege

A user with the DBA privilege can delete indexes owned by other users.

Format

DROP INDEX [authorization-identifier.] index-identifier [WITH PROGRAM]

Operands

authorization-identifier
Specifies the authorization identifier of the user who owns the index.
When this operand is omitted, the authorization identifier of the user who is executing the command is assumed.
index-identifier
Specifies the name of the index to be deleted.
WITH PROGRAM
Specifies that when the index is deleted, the SQL objects for active procedures and triggers that use the index are to be made inactivate.
When WITH PROGRAM is omitted, the index cannot be deleted if a procedure and triggers that uses the index is still active in an SQL object.

Common rules

  1. DROP INDEX cannot be executed from within a Java procedure if the deletion results in invalidation of the SQL object being executed.
  2. If the index to be deleted, the table from which the index is to be deleted, the index defined for the table, and columns of the abstract data type containing LOB columns and LOB attributes that are defined for the table are stored in an RDAREA to which the inner replica facility is applied, the index can be deleted, provided that certain conditions are met. For details about DROP INDEX execution conditions under a condition in which the inner replica facility is used, see the manual HiRDB Staticizer Option Version 7.

Notes

  1. The DROP INDEX statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. If an SQL object for which procedures or triggers are in effect is nullified by specifying WITH PROGRAM, any information associated with the nullified procedures or triggers in the SQL_ROUTINE_RESOURCES dictionary table is deleted.
  3. Before executing the SQL object associated with the procedures and triggers that were nullified by specifying WITH PROGRAM, you need to recreate the SQL object associated with the procedures and triggers by executing ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER.
  4. Regardless or whether or not WITH PROGRAM is specified, if there are procedures and triggers (exclusive of triggers that are defined for the table) associated with the table for which the index to be deleted is defined, any index information in the SQL object is nullified, in which case the triggers can no longer be executed. Because the procedure cannot be executed from another procedure or trigger, you need to recreate the SQL object.

Example

Delete the index (IDX1) defined for the product code (PCODE) column of a stock table (STOCK):

DROP INDEX IDX1