| Lesson 9 | Dropping an index |
| Objective | Drop an index. |
Dropping an Oracle Index
You can remove an index by using the DROP command.
Syntax
The syntax to drop an index is extremely simple. It contains only two parts:
DROP INDEX: |
The keywords required at the start of this SQL command |
index_name: |
The unique index name. The index_name may also require the prefix of the schema name for the index if the target index is not in your current schema.
|
Once you drop an index, all the blocks associated with the index are returned to the tablespace the index was in.
For B*-tree and bitmapped indexes, dropping an index has no effect on the overall data in the database, because all of the information in the index is duplicated in the data tables themselves.
However, dropping an index could have an effect on the performance of some SQL operations.
DROP INDEX
Use the DROP INDEX statement to remove an index or domain index from the database. When you drop an index, Oracle Database invalidates all objects that depend on the underlying table, including views, packages, package bodies, functions, and procedures. When you drop a global partitioned index, a range-partitioned index, or a hash-partitioned index, all the index partitions are also dropped. If you drop a composite-partitioned index, all the index partitions and subpartitions are also dropped.
In addition, when you drop a domain index:
- Oracle Database invokes the appropriate routine.
- If any statistics are associated with the domain index, then Oracle Database disassociates the statistics types with the FORCE clause and removes the user-defined statistics collected with the statistics type.
Prerequisites: The index must be in your own schema or you must have the DROP ANY INDEX system privilege.
Semantics
schema: Specify the schema containing the index. If you omit schema, then Oracle Database assumes the index is in your own schema.
index
Specify the name of the index to be dropped. When the index is dropped, all data blocks allocated to the index are returned to the tablespace that contained the index.
Restriction on Dropping Indexes: You cannot drop a domain index if the index or any of its index partitions is marked IN_PROGRESS.
FORCE
FORCE applies only to domain indexes. This clause drops the domain index even if the indextype routine invocation returns an error or the index is marked IN PROGRESS.
Without FORCE, you cannot drop a domain index if its indextype routine invocation returns an error or the index is marked IN PROGRESS
Example of Dropping an Index:
This statement drops an index named ord_customer_ix_demo.
DROP INDEX ord_customer_ix_demo;
The next lesson wraps up this module.