3.11.1 Specification format and rules for the DROP INDEX statement

The DROP INDEX statement deletes an index (a B-tree index, text index or range index).

Organization of this subsection

(1) Specification format

DROP-INDEX-statement ::= DROP INDEX index-name

index-name: Specifies the index name of the index to be deleted. For rules on specifying an index name, see (3) Index name specification format in 6.1.5 Qualifying a name.

To execute the DROP INDEX statement, the CONNECT privilege and schema definition privilege are required.

You can only delete an index owned by the current user (the HADB user whose authorization identifier is currently connected to the HADB server). You cannot delete an index owned by another HADB user.
You can delete an index that is defined for a table even if that table has data stored in it. When you delete the index, the data stored in the table will not be deleted.
You cannot delete an index defined on the base tables of a dictionary table or system table.
When you delete an index, it also deletes the cost information for the index.
You cannot use the DROP INDEX statement to delete a B-tree index corresponding to a primary key. To delete the index, use the DROP TABLE statement to delete both the index and table.
You cannot use the DROP INDEX statement to delete the range index that is defined for the archive range column. To delete the index, use the DROP TABLE statement to delete both the index and table.

Example 1

Delete the B-tree index (SHOP_CODE_IDX) defined for the shops table (SHOPSLIST).

DROP INDEX "SHOP_CODE_IDX"

Example 2

Delete the text index (ADDRESS_IDX) defined for the employee table (EMPLOYEE).

DROP INDEX "ADDRESS_IDX"

Example 3

Delete the range index (SHOP_CODE_RIDX) defined for the shops table (SHOPSLIST).

DROP INDEX "SHOP_CODE_RIDX"