DROP INDEX

The DROP INDEX statement deletes the specified index. The index can be global (TimesTen Scaleout or local (TimesTen Scaleoutor TimesTen Classic.

Required Privilege

No privilege is required for the index owner. DROP ANY INDEX is required for an index owned by another user.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout. Use the DROP INDEX statement to drop both global and local indexes.

SQL Syntax

DROP INDEX [Owner.]IndexName [FROM [Owner.]TableName]

Parameters

Parameter Description

[Owner.]IndexName

Name of the index to be dropped. You can include the name of the owner of the table for the index. For TimesTen Scaleout, the index can be global or local.

[Owner.]TableName

Name of the table upon which the index was created.

Description

  • If you attempt to drop a "busy" index—an index that is in use or that enforces a foreign key—an error results. To drop a foreign key and the index associated with it, use the ALTER TABLE statement.

  • If an index is created on a UNIQUE column constraint, it can only be dropped by dropping the constraint. Use the ALTER TABLE DROP UNIQUE statement for this purpose. Also, see CREATE TABLE for more information about the UNIQUE column constraint.

  • If a DROP INDEX operation is or was active in an uncommitted transaction, other transactions that are performing DML operations, that do not access that index, are blocked.

  • If an index is dropped, any prepared statement that uses the index is automatically prepared again the next time the statement is executed.

  • If no table name is specified, the index name must be unique for the specified or implicit owner.

  • If no index owner is specified and a table is specified, the default owner is the table owner.

  • If a table is specified and no owner is specified for it, the default table owner is the current user.

  • The table and index owners must be the same.

  • An index on a temporary table cannot be dropped by a connection if some other connection has an instance of the table that is not empty.

  • If the index is replicated across an active standby pair and if DDL_REPLICATION_LEVEL is 2 or greater, use the DROP INDEX statement to drop the index from the active standby pair in the replication scheme. See Making DDL Changes in an Active Standby Pair in the Oracle TimesTen In-Memory Database Replication Guide for more information.

Examples

Drop index partsorderedindex which is defined on table orderitems using one of the following: 

DROP INDEX partsorderedindex
   FROM purchasing.orderitems;

Or:

DROP INDEX purchasing.partsorderedindex;

See also

CREATE INDEX