You use DROP INDEX to delete an index. The index may have been created explicitly with a CREATE INDEX statement or implicitly by the definition of an integrity constraint (UNIQUE).
The INDEXES view of the INFORMATION_SCHEMA provides you with information on which indexes have been defined (see chapter "Information schemas").
If an explicitly defined index is also used by an integrity constraint, the index is not deleted but is renamed as an implicit index. The new index name starts with UI and is followed by a 16-digit number.
Indexes created implicitly by an integrity constraint (UNIQUE) are not deleted until the relevant integrity constraint is deleted.
The current authorization identifier must own the schema to which the index belongs.
DROP INDEX index [DEFERRED]
index
Name of the index to be deleted.
You can qualify the name of the index with a database and schema name.
DEFERRED
This clause initiates high-speed deletion in which only the contiguous part of the index is deleted. Any relocations which exist are retained.
The next time the user space is reorganized using the utility statement REORG SPACE all the existing tables and indexes are recovered in the user space. The relocations then also disappear.
Information on the storage structure of indexes is provided in the “ Core manual”.
An implicitly generated index (in the case of a UNIQUE integrity constraint) cannot be deleted explicitly. If necessary, the index must be generated explicitly with CREATE INDEX. Here the “Generate_Type” is merely changed from “implicit” to “explicit” in the metadata. The UNIQUE integration constraint can then be deleted. In this case the index is not deleted and can now be deleted using DROP INDEX ... DEFERRED.
The DEFERRED clause can only be specified in the case of explicit deletion. It cannot be specified when deletion takes place implicitly, e.g. using DROP SPACE CASCADE.
DEFERRED omitted:SESAM/SQL deletes the indexes. This can be time-consuming when indexes are very large and fragmented.
See also
CREATE INDEX