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
- Vertical fragmentation is a subset of attributes.
- Basically, vertical fragmentation splits tables by columns
Horizontal Fragmentation
- Horizontal Fragmentation is a subset of tuples (rows).
- 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.