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.

In Oracle, the `DROP SCHEMA` statement, as it exists in some other database systems, does not have a direct equivalent command. However, the concept of dropping a schema can be implemented by dropping all objects within a user and then dropping the user itself, as schemas in Oracle are associated with users.
To effectively remove a schema in Oracle, you typically use the following command:

DROP USER schema_name CASCADE;

Here's a breakdown of this command:

• DROP USER: This command is used to drop a user in Oracle, and since each user corresponds to a schema, this effectively removes the schema as well.
• schema_name: Replace this with the actual schema (user) name that you wish to drop.
• CASCADE: This option is required to drop all objects (tables, views, indexes, sequences, etc.) owned by the user (schema). Without CASCADE, Oracle will raise an error if the user owns any objects.

Important Considerations

• Dependencies: Dropping a user with CASCADE removes all objects owned by that user permanently. This includes tables, indexes, views, sequences, and any other database objects. Be cautious as this action is irreversible.
• Privileges: To execute this command, you need the DROP USER system privilege, which is typically available to users with DBA privileges.
• Impact: Removing a schema can impact other database objects that may depend on it, such as views or procedures in other schemas. It’s essential to check for dependencies before performing this action.

Example
To drop a schema named `test_schema`, use:

DROP USER test_schema CASCADE;

This command will delete the user `test_schema` and all associated objects within that schema.

1. Basic Functionality: A DROP INDEX removes an index or domain index.
2. Partitioned Indexes: Dropping a partitioned index (global, range, hash, composite) correctly removes all its partitions and subpartitions.
3. Domain Indexes: Oracle invokes the associated routine for domain indexes.
4. Statistics: If statistics are linked to a domain index, they are handled as you described.
5. Prerequisites: You need the necessary privileges (ownership or DROP ANY INDEX).
6. Schema: You can specify the schema or omit it to default to your own.
7. Space Reclamation: Dropping an index releases its allocated space back to the tablespace.
8. IN_PROGRESS Restriction: You cannot drop a domain index marked IN_PROGRESS (without FORCE).
9. FORCE Clause: FORCE is specific to domain indexes and overrides the IN_PROGRESS restriction and certain errors from the indextype routine.

Points to Clarify:

• Object Invalidation: While dropping an index can impact dependent objects, it doesn't necessarily invalidate them all. Oracle might be able to recompile them automatically. However, it's good practice to check and recompile dependent objects after dropping an index to ensure optimal performance.
• FORCE Clause: You mentioned that FORCE "drops the domain index even if the indextype routine invocation returns an error." It's more accurate to say it bypasses certain errors, not all. Severe errors can still prevent the drop.

This statement drops an index named ord_customer_ix_demo.

DROP INDEX ord_customer_ix_demo;

The next lesson wraps up this module.