Clusters

Tables that are frequently accessed together may be physically stored together. Clustering is a method of storing tables that are closely related and are often joined together, into the same area on the disk.

A cluster is created to hold the tables. A cluster contains the values of a table according to the column that we define. When ever we insert values into the table the same values are automatically stored in the database object name cluster.

Cluster can be created by using create cluster command. To create a cluster use the following syntax

Syntax :

Create cluster <cluster name> (columns specifications,------)

To create any cluster

1. First create a cluster with the column names that you want
2. Next create an index on that cluster. Otherwise the cluster will not work properly
3. Next create a table with the cluster specification and list of columns
4. Now insert some values into that table, if you see the cluster the same values will be available from the cluster also

Note :

1. We can not insert or update or delete any row from the cluster
2. We can not even drop the cluster until the source table is present
3. To drop the cluster, first we have to drop the source table and then we can drop that particular cluster

Creation of cluster for a table

First create the cluster,

create cluster sampleclust (sno number(3), sname varchar2(10));

Next create the index,

create index idx_sampleclust on cluster sampleclust;

Attaching the cluster to the table,

create table sample2 (sno number(3),sname varchar2(10)) cluster

sampleclust(sno,sname)

Example :

1. To display details of the Table

select * from sample2;

SNO SNAME

--------- ----------

100 Nithya

101 Saloni

102 Aruna

1. To display details of the Table

select * from cluster sampleclust;

SNO SNAME

--------- ----------

100 Nithya

101 Saloni

102 Aruna

1. Even if you delete any row in the table, that will not affected to the cluster
   1. delete from sample2 where sno=100;
   2. select * from sample2;

SNO SNAME

--------- ----------

101 Saloni

102 Aruna

3. select * from cluster sampleclust;

SNO SNAME

--------- ----------

100 Nithya

101 Saloni

102 Aruna

4. delete from sampleclust where sno=102

ERROR at line 1:

ORA-00942: table or view does not exist

Note : We can not delete any data from the cluster

Drop cluster

This command is used to drop any cluster

Syntax : drop Cluster <cluster name>

To drop any cluster

Drop table sample2;

Drop cluster sampleclust;