In this article, we will see what is cluster in SQL and what purpose cluster will be created.

A cluster in SQL is a database object that stores data related to two are more tables in a single disk space.

Single disk space means single memory location that is called blocks.

Segments and Memory Locations in SQL Cluster

Usually whenever you create a table oracle allocates a segment for that table.

For example assume I have created a customer table.

So when I create a customer table a segment will be created for the customer table. In the segment, it stores the customer data.

If I create another table sales a segment will be created for the sales table and it stores sales data.

So whenever you create a table a separate segment will created and stores the data.

If the data stored in different segments and different memory locations sometimes the problem is when I want to join the two tables it degrades the performance.

Why sometimes it degrades the performance? When we submit a join query?

• It requires number of IO operations.

• It has to switch from one memory location to other memory location.

Due to this it degrades the performance.

Let us say for example we are using some of the tables frequently and we are joining those tables to get the data. It is better to cluster those tables.

In other words, If there are two tables customer and sales and these two tables are frequently being used and frequently involved in the join operations better to cluster these two tables.

If these two tables stored in the single memory location it reduces number of IO operations and improves performance.

How to cluster the tables?

• First Create Cluster
• Second Create Index on Cluster
• Third Create Tables

Create Cluster in SQL

Create cluster TC1( C Number(2));

If you see the cluster creation it is some what similar to table creation.

Consider I have executed the above query and cluster created with one column.

Create Index on Cluster

Create Index CI1 on cluster TC1;

Consider we have executed the above query and created Index CI1 on Cluster TC1.

Create Tables Using Cluster

Create table Sales10 ( OrdNum Number(2),
Item Varchar2(20));

If we simply executive this query, a separate memory and segment will be allocated to the sales table.

What we must tell oracle? We must tell do not to create a separate segment for this table instead store this data in cluster TC1.

Now our modified query will be

Create table Sales10 ( SLID Number(2),
Item Varchar2(20))
Cluster TC1(SLID);

It means this table data stored in the cluster TC1 and organized based on the SLID.

Now I will create one more table called Customer.

Create table Customer10 ( Cust_Num Number(4), Cust_Name Varchar2(20), SLID Number(2))
Cluster TC1 (SLID);

This Customer table also stored in Cluster TC1 and organized based on SLID wise.

Both the tables stored in cluster TC1 and organized based on SLID wise.

Now if we insert some data into the tables it will be stored in cluster TC1 and will organized based on SLID wise.

Why we are organizing based on SLID wise?

We want to join these two tables based on SLID.

If the data is already organized based on SLID wise join operation will performed fast.

So this will improve the performance of join operation.

Since it is already organized based on SLID wise there will be no need for comparison like which customer SLID is matching with which sales SLID.

No comparison will be reduced no of IO operations. Hence it improves the performance.

Insert data into the clustered tables

So now let us insert some data into these tables.

Let us insert data into sales table.

Insert into Sales10 values (10, 'Mobile')            Insert into Sales10 values (20, 'Laptop')

Let us insert data into customer table.

Insert into Customer10 values(1, 'Y', 10)
Insert into Customer10 values (2, 'Z', 20)

Here we have inserted two records into sales table and two records into the customer table.

Means these two tables data stored in cluster TC1 organized based on SLID wise.

How do we know that two tables data stored in the same location?

If we want to know that we can verify.

Let us write a query for that

Select ROWID, SLID, Item from sales;

ROWID	SLID	Item
ANACHDYTSCJGnCCTA	10	Mobile
BNKGYEFCKPMNnFHFB	20	Laptop

Select ROWID, cust_num, cust_name, SLID from customer;

ROWID	cust_num	cust_name	SLID
ANACHDYTSCJGnCCTA	1	Y	10
BNKGYEFCKPMNnFHFB	2	Z	20

Now here if you observe for the item 10 sale SLID and ROW ID is matching with the customer 10 ROW ID.

Similarly, item 20 sale SLID and ROW ID is matching with the customer 20 ROW ID.

These ROW IDs are same which means these records are stored in the same memory location and same block.

Now if we submit a below query.

Select s.*, c.* from sales10 s, customer10 c
Where s.SLID=c.SLID

When you submit the join query it has to get the data from the cluster.

These two tables data stored in the cluster TC1 so it has to get the data from cluster.

Cluster SQL Data Access Methods

Here there are two access methods.

• One is table access
• Another one is index access.

So oracle access the data either through tables or Index.

So now here the data stored in cluster TC1.

How to access the data?

That’s why I created one index CI at the beginning.

Creating index on cluster TC1 means creating index on cluster ID.

What is the cluster ID here?

The cluster ID is SLID. So both the tables are stored in cluster TC1 and organized based on SLID so SLID is cluster ID.

When you submit the query to oracle it go to the index and that index is pointing to the cluster.

So when you submit this query to oracle it goes to the Index CI and that index is pointing to the cluster TC1 and get the data.

So these how data will be accessed when you submit the join query to oracle.