In this article, we will see what is cluster in sql and what purpose cluster will be created.
A cluster in sql is a databasee 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
Usally 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 the 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 joing those table 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 seperate memory and segment will be allocated to the sales table.
What we must tell oracle? We must tell do not create seperate segment for this table instead store this data in cluster TC1.
Now our modified query will be
Create table Sales10 ( SLID Number(2),
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 organising 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 fastly.
So this will improve the performance of join operation.
Since it is already organized based on SLID wise there will be no need of comparison like which customer SLID is matching with which sales SLID.
No comparison will be reduced no of IO operations. Hence it improves the perormance.
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 we know that these 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;
Select ROWID, cust_num, cust_name, SLID from customer;
Now here if you observe for the item 10 sale SLID and ROWID is matching with the customer 10 ROWID.
Silerly, item 20 sale SLID and ROWID is matching with the custome 20 ROIWID.
These ROWIDs 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?
Thats 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 go 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.
Here you can see the related article What is Unique key and Primary in SQL?