Aysha Femin A
S2 CSE
04
EXPERIMENT NO:04
Aim: Implement any one partitioning technique in parallel database.
List partitioning:
List partitioning in a parallel database involves dividing a table into partitions based on
a specified list of values. Each partition contains a subset of the data based on the
partitioning criteria. The main goal of list partitioning is to distribute data across
multiple nodes in the parallel database,allowing for parallel processing and improved
performance
List partitioning allows to openly organize the rows, which are divided into partitions
by spelling out a roll of distinct standards for the partitioning key in an account for
every division. Using this scheme of partitioning, even dissimilar and shuffled
information tables can be managed in a comfortable approach. In order to avoid errors
during the partition of rows in the giant database, the addition of the probable terms
into the table formed by the list partitioning method can be avoided by using the default
partition process.
Example:
Note: PostgreSQL is used for the implementation of partition technique.
Let the table name be “sales_data” with following columns: ”date”, ”region”, ”product”
and “revenue”.We can partition the table based on the “region” column using list
partition.
Queries:
CREATE TABLE sales_data_parent (
date DATE,
region TEXT,
product TEXT,
revenue NUMERIC
) PARTITION BY LIST (region);
CREATE TABLE sales_data_part1 PARTITION OF sales_data_parent
FOR VALUES IN ('North', 'South');
CREATE TABLE sales_data_part2 PARTITION OF sales_data_parent
FOR VALUES IN ('East');
CREATE TABLE sales_data_part3 PARTITION OF sales_data_parent
FOR VALUES IN ('West');
INSERT INTO sales_data_part1 (date, region, product, revenue);
select * from sales_data_parent;
insert into sales_data_parent VALUES('2006-02-15','North','Pen',3000);
select * from sales_data_part1;
insert into sales_data_parent VALUES('2006-02-15','West','Pen',3000);
select * from sales_data_part3;
