DROP INDEX

Function

Delete the index.

Precautions

Only the owner of the index or the user with INDEX privileges on the table where the index is located has the privilege to execute the DROP INDEX command, and the system administrator has this privilege by default.

For a global temporary table, when a session has initialized the global temporary table object (including the creation of the global temporary table and the first insertion of data into the global temporary table), other sessions cannot perform the DROP operation of the index on the table.

New You can specify the ALGORITHM option syntax.

Syntax

DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] 
    index_name [, ...] [ CASCADE | RESTRICT ] [ALGORITHM [=] {DEFAULT | INPLACE | COPY}];

Parameter Description

• CONCURRENTLY

  Deletes an index in an unlocked manner. Deleting an index generally blocks access to the table on which the index depends by other statements. Add this keyword to enable the deletion process without blocking.

  This option specifies the name of only one index and is not supported by the CASCADE option.

  The normal DROP INDEX command can be executed within a transaction, but DROP INDEX CONCURRENTLY cannot be executed within a transaction.

• IF EXISTS

  If the specified index does not exist, issue a NOTICE instead of throwing an error.

• index_name

  The name of the index to delete.

  Range of values: indexes that already exist.

• CASCADE | RESTRICT

  • CASCADE: Indicates that cascading deletion of objects dependent on this index is allowed.
  • RESTRICT (default): Indicates that objects that depend on this index exist, then the index cannot be deleted.

• ALGORITHM

  Specify the algorithm, options: DEFAULT, INPLACE, COPY. currently only do syntax compatibility, no actual function for the time being.

Examples

See Examples for CREATE INDEX .

Helpful Links

ALTER INDEX, CREATE INDEX