Clustering Tables   «Prev  Next»
Lesson 8Deleting a cluster
ObjectiveDrop a cluster.

Deleting or Dropping Hash Cluster

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

DROP CLUSTER

To drop a cluster, you use the syntax illustrated in the following display:
DROP CLUSTER cluster_name
[INCLUDING TABLES]
[CASCADE CONSTRAINTS];
  1. The name of an existing cluster.
  2. These keywords are not required if the cluster does not contain any tables. If the cluster does contain tables and these keywords are not included, an error is returned.
  3. If the cluster contains columns that are the objects of FOREIGN KEY constraints, you must include these keywords to drop these constraints, or an error will be returned.

Drop Oracle Cluster
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.

Oracle Cluster Example

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.