Sunday, 4 June 2017

Cluster Table in Oracle SQL

Cluster Table in Oracle, Cluster Index

 
Cluster:-Cluster is a method by which we can store data of two or more tables the basis of cluster key

Cluster Key :-Cluster key is a column/columns by which the tables are usually joined.
Example:-
1) Create a cluster :-
SQL>create cluster emp_dept (deptno number(2));

Cluster Index:-Index created on cluster is called cluster index.
Example:-
SQL>create index cls_idx on cluster emp_dept;

2) Creating tables in the cluster:-
SQL>create table dept12 (deptno number(2),
                    name varchar2(20),
                    loc varchar2(20))
                    cluster emp_dept (deptno);

SQL>create table emp12 (empno number(5),
            name varchar2(20),
            sal number(10,2),
            deptno number(2))
            cluster emp_dept (deptno) ;

Drop a Cluster:-   a) If there is no table attached with cluster then we can drop cluster by below way     
SQL>drop cluster emp_dept;

b) If Tables attached with cluster then we can drop cluster with following way

SQL>drop cluster emp_dept including tables; --if tables attached with cluster

Data Dictionary for Cluster :-Below is the Data Dictionary used for getting the information of cluster
SQL>select * from user_clusters;



No comments:

Post a Comment

Cloning database with RMAN

Below are the steps to clone production database(primedb) to UAT database(clonedb)   STEP1 :- Check the both servers are able to connect ...