Clusters in Oracle

Clusters are an optional method of storing table data.  A cluster is group of tables that share the same data blocks. 

They share common columns and are often used together.

Clusters are special configurations to use when two or more tables are stored in close physical-proximity to improve performance on SQL join statements using those tables.  To cluster tables one must own the tables he is going to cluster.

The cluster key is the column or group or columns that the clustered tables have in common.  The syntax for creating a cluster is:

create cluster <cluster_name>(

column datatype  [, column datatype] [,column datatype]….) [other options];

create cluster emp_dept (deptno_key number(12));

Cluster created.

create table dept33(deptno number(12) primary key, dname char(14), loc char(13))cluster emp_dept(deptno);

Table created.

create table emp3(

empno number(4) not null, ename char(10), job char(9), mgr number(4),hiredate date, sal number(7,2), comm number(7,2),  deptno number(12) references dept33)

cluster emp_dept(deptno);

Table created.

DROP CLUSTER <cluster_name>    INCLUDING TABLES

CASCADE CONSTRAINTS;