How a DROP Functions
The system pursues the following process to drop a secondary index:
1 Sets an EXCLUSIVE lock on the base table that defines the index.
2 Frees the disk space that was used by the index subtable.
3 Removes information about the index from the table header.
If statistics have been collected on the index, sets the value for the IndexNumber column in DBC.StatsTbl to 0, but does not drop the statistics that have been collected on the index column.
You can drop a named index by specifying either its name (see “DROP INDEX” on page 802) or its definition, but not both (see “DROP index_definition Syntax” in SQL Data Definition Language).