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. 

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

I 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,

  1. create table tblCust_glo  
  2. (  
  3.    Cid varchar2(10) primary key,  
  4.    Cname varchar2(10) not null,  
  5.    Ctype varchar2(10) not null,  
  6.    Cmob integer not null  
  7. );   
  1. create table tblCust_1  
  2. (  
  3.    Cid varchar2(10) primary key,  
  4.    Cname varchar2(10) not null,  
  5.    Ctype varchar2(10) not null  
  6. );  
Creating link from one node to another

  1. Create database link linker  
  2. connect to scott identified by  
  3. tiger using ‘IT_78’;  
--Here IT_78 is net service name of my machine.
--You can create it by visiting this link.

  1. create or replace trigger trigCust_glo  
  2. after insert on tblCust_glo  
  3. for each row  
  4. begin  
  5. insert into tblCust_1@linker  
  6.    values(:new.Cid,:new.Cname,:new.Ctype);  
  7. end;  
  8. /  
This is for tblVehical.

  1. create table tblVehicle_glo  
  2. (  
  3.    Vid varchar2(10) primary key,  
  4.    Vclass varchar2(10) not null,  
  5.    Vrgis varchar2(10) not null,  
  6.    Vodo integer not null,  
  7.    Vmeter integer not null,  
  8.    Vstatus varchar2(10) not null  
  9. );  
  1. create table tblVehicle_1  
  2. (  
  3.    Vid varchar2(10) primary key,  
  4.    Vclass varchar2(10) not null,  
  5.    Vmeter integer not null,  
  6.    Vstatus varchar2(10) not null  
  7. );  
  1. --Trigger:-  
  2. create or replace trigger trigVeh_glo  
  3. after insert on tblVehicle_glo  
  4. for each row  
  5. begin  
  6. insert into tblVehicle_1@linker  
  7.    values(:new.Vid,:new.Vclass,:new.Vmeter,:new.Vstatus);  
  8. end;  
  9. /  
Horizontal Fragmentation
  1. Create table customer_horizontal as  
  2. (select * from tblCust_glo@linker Where Ctype='Premium')  

So some of the advantages of using fragment are parallelism, balanced storage, disjointedness and efficiency.

It also increases the reliability and availability.