SQL Clusters

In this article, we are going to cover the SQL cluster and will also cover some advantages and disadvantages of SQL clusters. Let’s discuss one by one.

Clusters in SQL are used to store data that is from different tables in the same physical data blocks. They are used if records from those tables are frequently queried together. By storing same data blocks, the number of database block reads needed to full fill such queries decreases which improves performance.

• Each cluster stores tables data and maintains a clustered index to sort data.
• Columns within the cluster index are called clustered keys. These determine the physical placement of rows within the cluster.
• Cluster key is usually a foreign key of one table that references the primary key of another table in cluster.

The first cluster is created. Then cluster index is created on cluster key columns. After cluster key index is created, data can be entered into tables stored in clusters. As rows are inserted database stores cluster key and its associated rows in each of the cluster’s blocks.

Syntax :

CREATE CLUSTER <Cluster Name> 
(<Column> <Data Type> 
[, <Column> <Data Type> ] . . . . . )
[<Other Options >]

Example –

create table branch_master 
( 
"branch_code" varchar(10) PRIMARY KEY, 
"branch_name" varchar(15)
);

Now, Consider the above created table and let’s create the cluster for the same.
Creating a Cluster :

create cluster branch_info
( 
"branch_code" varchar(10)
);

Advantages of clusters :

1. Disk I/O is reduced.
2. Access time improves for joins of clustered tables.
3. As all rows in clustered tables use the same columns as common primary key, this yields storage benefit.

Disadvantages of clusters :

1. Reduces performance of INSERT statements as compared to storing the table separately with its own index.
2. Columns that are often updated are not good candidates for cluster key.

My Personal Notes arrow_drop_up

Save