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

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,

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.