Drop Cluster - Oracle Hash Cluster

A cluster is a database object and like other database objects, you can delete is from the database. The SQL syntax provided:

DROP CLUSTER cluster_name
[INCLUDING TABLES]
[CASCADE CONSTRAINTS];

✅ Yes, this syntax is valid and will work in Oracle Database 23c, just as it did in earlier versions (e.g., Oracle 11g through 21c). Here's how the options function:

• `DROP CLUSTER cluster_name;` Drops the cluster only if it contains no tables.
• `INCLUDING TABLES` Drops the cluster and all tables that are part of the cluster.
• `CASCADE CONSTRAINTS` Drops all referential integrity constraints that refer to primary and unique keys on the tables within the cluster.

Example:

DROP CLUSTER emp_dept_cluster INCLUDING TABLES CASCADE CONSTRAINTS;

This would:

• Remove the `emp_dept_cluster`,
• Drop all tables in that cluster (e.g., `emp`, `dept`),
• Drop all foreign key constraints pointing to those tables.

Final Notes:

• Ensure you have proper privileges (`DROP` on the cluster and affected tables).
• Use with caution in production environments, especially with `INCLUDING TABLES`.

To drop a cluster, your schema must contain the cluster or you must have the DROP ANY CLUSTER system privilege. You do not have to have additional privileges to drop a cluster that contains tables, even if the clustered tables are not owned by the owner of the cluster.
Clustered tables can be dropped individually without affecting

1. the table's cluster,
2. other clustered tables, or
3. the cluster index.

A clustered table is dropped just as a non-clustered table is dropped using the DROP TABLE statement.
Note: When you drop a single table from a cluster, Oracle deletes each row of the table individually. To maximize efficiency when you intend to drop an entire cluster, drop the cluster including all tables by using the DROP CLUSTER statement with the INCLUDING TABLES option. Drop an individual table from a cluster (using the DROP TABLE statement) only if you want the rest of the cluster to remain.

You can drop a hash cluster using the DROP CLUSTER statement:

DROP CLUSTER emp_dept;

A table in a hash cluster is dropped using the DROP TABLE statement. The implications of dropping hash clusters and tables in hash clusters are the same as those for dropping index clusters

When you drop a cluster, the cluster index, if one exists, is also dropped. You can drop a table from a cluster that contains multiple tables by using the DROP TABLE command. However, the result of this action will be that Oracle individually deletes each row of the table. There is no way to uncluster a table, since the cluster actually controls the physical placement of the table on the disk. If you want to change a clustered table to an unclustered table, you must first unload the data from the table, drop the cluster, create the table again without a CLUSTER clause, and reload the data back into the table.

This insight is crucial when managing "clustered table" in Oracle, especially for performance and efficiency.
✅ Here's the breakdown:
🔹 Dropping a Single Table from a Cluster

DROP TABLE table_name;

• Oracle deletes each row individually from the cluster.
• This is because rows from multiple tables share the same data blocks, and Oracle must ensure only the correct rows are removed.
• Performance impact: This can be slow and resource-intensive on large clustered tables.

🔹 Dropping the Entire Cluster

DROP CLUSTER cluster_name INCLUDING TABLES;

• Oracle can reclaim the entire segment more efficiently.
• All rows from all tables in the cluster are dropped together, with minimal overhead.
• 💡 This is highly efficient and avoids the per-row deletion overhead.

📌 Recommendation:

• ✔️ Use `DROP CLUSTER ... INCLUDING TABLES` when your goal is to remove the entire cluster and its contents.
• ❗ Use `DROP TABLE` only if you want to retain the cluster and other tables within it.

If you wanted to drop the existing lot_cluster cluster, and the cluster contained tables, you would use the following SQL command:

DROP CLUSTER lot_cluster INCLUDING TABLES;

The next lesson concludes the module on Oracle table clustering which will briefly review the topics covered in this module. You also can take a quiz to help identify topics that you might want to review in more detail.

This SQL statement is correct for "dropping an existing cluster" and all its associated tables in Oracle, including Oracle 23c:

DROP CLUSTER lot_cluster INCLUDING TABLES;

🟢 What this does:

• Drops the cluster `lot_cluster`
• Drops all tables that were created in that cluster
• Efficiently frees up storage, avoiding row-by-row deletion

✅ When to Use:
Use this command when you're done with the entire cluster and its tables — for instance, during:

• Application decommissioning
• Schema redesign
• Cluster migration to a different structure (e.g., partitioning)