SQL Server Cluster

Introduction

• Cluster is a database object which contains groups of tables together and also shares same data block.
• In all databases cluster database object is used to improves performance of the Joins that’s why cluster database object is created by database Admisnistrator only.
• Cluster tables must have a ccommon column name, this common column is also called as cluster key.

• Generally, clusters is created at time of table creation.
• In all databases whenever we are submitting inner Join or outer Join then database servers internally checks from clause tables are available in cluster or not? If those tables are available on cluster then Database servers very fastly retrieve data from the cluster table.

Creating Clusters in Oracle

Step 1:- Creating a cluster
Step 2:- Create an index on cluster.
Step 3:- Create cluster table.

1.  Creating a cluster

Create a cluster based on a common column name, this common column name is also called as key.

Syntax –

Create cluster clustername (common column name data type (size))

2.  Create an index on cluster:-

Syntax – 

Create index indexname on cluster clustername;

Frequently Asked SQL Server Interview Questions

3. Create Cluster Table

Syntax –

Create table tablename (common colname datatype (size), col1 datatype (size),…..)
Cluster clustername (common col name);
SQL > create cluster emp_dept (deptno number (10));
SQL > create index abc on cluster emp_dept;
SQL > create table emp1 (empro number (10), ename varchar 2 (10) sal number (10), deptno number (10)), cluster emp_dept (deptno); 
SQL > create table dept1 (deptno number (10), dname varchar 2(10), loc varchar2 (10)) cluster emp_dept (deptno);
SQL > desc emp1;
SQL > desc dept1:

Note:-

• In oracle cluster tables having same row id.
• we cannot drop cluster if cluster having tables, to overcome this problem oracle 8.0 introduced including tables clause along with drop cluster clustername which is used to drop cluster with tables.

Syntax – 

Drop cluster clustername including tables;

Ex:- SQL > drop cluster emp_dept:

Error: cluster not empty

SQL > drop cluster emp_dept including tables;

O/P:- Cluster dropped.

In oracle all information of cluster stored under user_clusters data dictionary

List of Related Microsoft Certification Courses: