ORACLE CLUSTERS - EXAMPLE

In order to create clustered table you must first create a cluster.

SQL> create cluster emp_clust(deptno number);

Cluster created.

Now we will create two tables dept and emp that are part of cluster emp_clust( clustered by deptno column)


SQL> CREATE TABLE dept (
2 deptno NUMBER primary key,
3 dname varchar(10),loc varchar(20))
4 cluster emp_clust(deptno);

Table created.

SQL> CREATE TABLE emp (
2 empno NUMBER(5) PRIMARY KEY,
3 ename VARCHAR2(15) NOT NULL,
4 deptno NUMBER REFERENCES dept)
5 CLUSTER emp_clust(deptno);

Table created.

.
SQL> CREATE INDEX emp_dept_index
2 ON CLUSTER emp_clust;

Index created.

Dictionary Tables to query information regarding clusters:

user_clusters,

user_clu_columns;


SQL> select * from tab;

INSERTING VALUES

SQL> insert into dept values(10,'a','b');

1 row created.

SQL> insert into dept values(20,'a','b');

1 row created.

SQL> insert into dept values(30,'a','b');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into emp values(100,'king',10);

1 row created.

SQL> insert into emp values(101,'king',10);

1 row created.

SQL> insert into emp values(102,'miller',20);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

SQL> select * from dept;