Understanding Fragmentation In Distributed Databases

This article explains about fragmentation types in distributed databases.

Let's start the article by defining distributed database - A distributed database is a database in which storage devices are not all attached to a common processor. It may be stored in multiple computers, located in the same physical location; or may be dispersed over a network of interconnected computers. (source wiki) One of the strongest fault tolerance techniques of a distributed database is data replication, which is a process of storing separate copies of the database or tables at two or more sites.

Understanding Vertical and Horizontal fragmentation

Vertical fragmentation

1. Vertical fragmentation is a subset of attributes.
2. Basically, vertical fragmentation splits tables by columns

Horizontal Fragmentation

1. Horizontal Fragmentation is a subset of tuples (rows).
2. Horizontal Fragmentation splits tables by rows. 

Example

Let's say I have one global table (e.g. Customer Table)

Vertical fragmentation would be like this: (Here, we are storing 2 columns at one fragment and 3 columns at another fragment, however, id is important at both sites because it's a primary key)

Horizontal fragment looks like this: (Here, we are diving fragment based on some condition such that all data with gender male will reside at one fragment and others at different fragment).

Understanding with an example

am using SQL Plus to perform these operations. I have two machines m1 and m2; on m1 I am creating global table whereas on m2 I am storing fragment values.

Vertical Fragmentaion

There is one global table and the secondary table is given as

This is for tblCust

Global

create table tblCust_glo  
(  
   Cid varchar2(10) primary key,  
   Cname varchar2(10) not null,  
   Ctype varchar2(10) not null,  
   Cmob integer not null  
);   

Secondary

create table tblCust_1  
(  
   Cid varchar2(10) primary key,  
   Cname varchar2(10) not null,  
   Ctype varchar2(10) not null  
);  

   Creating link from one node to another

CREATE DATABASE LINK

Create database link linker  
connect to scott identified by  
tiger using ‘IT_78’;  

Here IT_78 is net service name of my machine.

You can create it by visiting this link.

Trigger

create or replace trigger trigCust_glo  
after insert on tblCust_glo  
for each row  
begin  
insert into tblCust_1@linker  
   values(:new.Cid,:new.Cname,:new.Ctype);  
end;  
/  

This is for tblVehical.

Global

create table tblVehicle_glo  
(  
   Vid varchar2(10) primary key,  
   Vclass varchar2(10) not null,  
   Vrgis varchar2(10) not null,  
   Vodo integer not null,  
   Vmeter integer not null,  
   Vstatus varchar2(10) not null  
);  

Secondary

create table tblVehicle_1  
(  
   Vid varchar2(10) primary key,  
   Vclass varchar2(10) not null,  
   Vmeter integer not null,  
   Vstatus varchar2(10) not null  
);  

--Trigger:-  
create or replace trigger trigVeh_glo  
after insert on tblVehicle_glo  
for each row  
begin  
insert into tblVehicle_1@linker  
   values(:new.Vid,:new.Vclass,:new.Vmeter,:new.Vstatus);  
end;  
/  

Horizontal Fragmentation

Create table customer_horizontal as  
(select * from tblCust_glo@linker Where Ctype='Premium')

Summary

So some of the advantages of using fragment are parallelism, balanced storage, disjointedness and efficiency. It also increases the reliability and availability.