Clusters in Oracle

CLUSTERS:
------------
Oracle Index Cluster Tables

Unlike the hash cluster where the symbolic key is hashed to the data block address,
an index cluster uses an index to maintain row sequence.

A table cluster is a group of tables that share the same data blocks,
since they share common columns and are often used together. When you create cluster tables,
Oracle physically stores all rows for each table in the same data blocks

Index cluster tables can be either multi-table or single-table.
Lets take a look at each method.

SINGLE TABLE CLUSTER:
-------------------

SYNTAX
------
SQL> create  cluster clus_emp (ename  VARCHAR2(10))
size 1024 single table  hashkeys 11  ;

SQL> select * from user_clusters


SQL> create table emp
      cluster clus_emp(ENAME) as
     select * from SCOTT.emp;


Multi-Table Cluster
------------------
SYNTAX
------
SQL> create  cluster clus_emp (DEPTNO  NUMBER(2))
size 1024  hashkeys 142  ;


CREATE TABLE empcluster (
empno    NUMBER(10),
sal      NUMBER(10),
ename     VARCHAR2(10),
deptno number(2))
CLUSTER clus_emp (deptno);

CREATE TABLE deptcluster (
deptno          NUMBER(2),
dname     VARCHAR2(15),
loc          VARCHAR2(5))
CLUSTER clus_emp (deptno);


Cluster index
-------------
Create index on cluster is called cluster index.

CREATE INDEX indx_cluster ON CLUSTER clus_emp;

In above query "indx_cluster" is the index name and "clus_emp" is the cluster name.

SELECT index_name, index_type, tablespace_name FROM user_indexes;


DROP CLUSTER
------------

SQL> drop cluster clus_emp;

SQL> drop cluster clus_emp INCLUDING TABLES

Ur's

AmarAlam