Clustering Tables   «Prev  Next»

Lesson 8Deleting a cluster
ObjectiveDroppin an Oracle Cluster.

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

Dropping Clustered 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.
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.