Dropping an Oracle Cluster

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

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

This syntax is valid in current Oracle releases, including Oracle 23ai, just as it has been since much earlier versions.
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 it.
• 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 removes the emp_dept_cluster, drops all tables in that cluster (e.g. emp, dept), and drops all foreign key constraints pointing to those tables.

Before you drop a cluster in production:

• Confirm you have the right privileges — either you own the cluster, or you have the DROP ANY CLUSTER system privilege.
• Check what the cluster actually contains before deciding on INCLUDING TABLES. Querying DBA_CLUSTERS and DBA_TABLES (the same views introduced for sizing and monitoring earlier in this module) shows which tables belong to the cluster and how large they are, so you aren't guessing at the blast radius of the command:

  SELECT table_name, num_rows
  FROM dba_tables
  WHERE cluster_name = 'EMP_DEPT_CLUSTER';

• Consider whether CASCADE CONSTRAINTS will silently remove foreign keys that other parts of the application still rely on — dropping the constraint doesn't warn you about the tables that referenced it. If, for instance, an ORDERS table outside the cluster has a foreign key to DEPARTMENTS' primary key, CASCADE CONSTRAINTS removes that foreign key along with everything else, and nothing will stop new ORDERS rows from being inserted with a department_id that no longer refers to anything.

Dropping a cluster is rarely the first move; it's usually the conclusion of a decision made earlier. A few situations from this module where it comes into play:

• The clustering decision itself turned out to be wrong — the tables were not joined or looked up by the cluster key as often as expected, so the read benefit never materialized against the write overhead the module covered earlier.
• A hash cluster's HASHKEYS was set too low for how the data actually grew, and collisions became frequent enough that rebuilding with better sizing is easier via drop-and-recreate than trying to grow the existing structure in place.
• A schema redesign moves the same data to a different structure entirely — partitioning, for instance — and the cluster is simply retired once the new structure is in place.

In all three cases, the actual DROP CLUSTER command is the easy part; the planning that precedes it — checking what the cluster contains, deciding whether to keep the tables, and confirming nothing else depends on constraints that will disappear with CASCADE CONSTRAINTS — is where the real care belongs.

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

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 rather than simply deallocating a segment. This traces back to the same physical layout described earlier in this module: rows from different tables sharing a cluster key can live in the very same data block. Oracle cannot just discard the whole block, because it may still hold rows belonging to a table you did not ask to drop — so it has to walk through and remove only the rows that belong to the table being dropped, leaving everything else in that block untouched. On a large clustered table, that row-by-row pass can be slow and I/O-intensive.

To maximize efficiency when you intend to drop an entire cluster, use the DROP CLUSTER statement with the INCLUDING TABLES option instead. Because every table in the cluster is going away together, Oracle no longer needs to distinguish which rows belong to which table — it can reclaim the whole segment at once rather than deleting row by row. 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 same DROP CLUSTER statement:

DROP CLUSTER emp_dept_cluster;

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 — which is a slightly simpler story for a hash cluster specifically, since a hash cluster never had a separate cluster index in the first place. There is nothing extra to clean up on that front: dropping the hash cluster just releases the space it pre-allocated via SIZE and HASHKEYS, and dropping a single table from it still means Oracle deletes that table's rows one at a time, for the same shared-block reason described above.

When you drop a cluster, the cluster index, if one exists, is also dropped along with it. You can drop a table from a cluster that contains multiple tables by using the DROP TABLE command; the result of this action is 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 comes up most often when a cluster's original access pattern no longer holds — for example, the tables stopped being joined on the cluster key as often as expected, or write volume grew to the point where the clustering overhead discussed earlier in this module outweighs the read benefit it was chosen for.

Migrating a table out of a cluster, step by step:

-- 1. Unload: copy the clustered table's data out first
CREATE TABLE dept_backup AS SELECT * FROM department;

-- 2. Drop the cluster and its tables
DROP CLUSTER emp_dept_cluster INCLUDING TABLES;

-- 3. Recreate the table with no CLUSTER clause
CREATE TABLE department (
  department_id NUMBER PRIMARY KEY,
  department_name VARCHAR2(50)
);

-- 4. Reload the data
INSERT INTO department SELECT * FROM dept_backup;

-- 5. Clean up the temporary copy
DROP TABLE dept_backup;

Note that step 2 also removes every other table that was in the cluster — if the cluster holds more than one table, each one needs its own unload/reload pass through steps 1 and 4 before the cluster is dropped in step 2.

• Use DROP CLUSTER ... INCLUDING TABLES when your goal is to remove the entire cluster and its contents efficiently — for example, during application decommissioning, a schema redesign, or migrating the cluster to a different structure, such as partitioning.
• Use DROP TABLE only if you want to retain the cluster and the 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;

This drops the lot_cluster cluster, drops all tables that were created in it, and efficiently frees up the storage without the row-by-row deletion overhead described above.

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.

• DROP CLUSTER removes the cluster itself, and requires INCLUDING TABLES to also remove any tables it contains.
• CASCADE CONSTRAINTS is required if any foreign key elsewhere in the schema references a primary or unique key on a table in the cluster.
• Dropping an entire cluster with INCLUDING TABLES is far more efficient than dropping its tables one at a time, since Oracle can reclaim the whole segment at once instead of deleting rows individually.
• Dropping a cluster also drops its cluster index, if it had one — a hash cluster never has one to begin with.
• There is no in-place way to uncluster a table; moving it out of a cluster requires unloading its data, dropping the cluster, recreating the table without a CLUSTER clause, and reloading the data.