---

Table Partitioning

---

Dividing the large table into different smaller part is known as the table partitioning and partitioned table can stored in the different table.

There are many advantages of Table Partitioning.

Increases the performance of servers and we can retrieve data from existing table easily.
Backup/Recovery operation is easy for DBA.

Note: We can divide the table into different part only with the during the creation of the table. Once table can create it is not possible to dividing the table in to different part. It means that unpartition the table cannot be partition.

There are various type of partitions technique supported by oracle.

1: Range Partition

2: Hash Partition

3: List Partition

4: Composite Partition

---

1: Range Partition: In this type of partition we can divide the table based on some range, generaly we can divide into numeric part.

Syntax

Create table table_name(colum1 datatype(size), colum2 datatype(size), -------- column datatype(size) ) partition by range(column_name) (partition partition_name values less than (value) ----- partition partition_name values less than (value));

Example:

Create table table1(emp_id number(5), emp_name varchar2(20), emp_sal number(6)) partition by range (sal)(partition p1 values less than (5000), partition p2 values less than (10000));

Syntax to retrieve the data from specific partition

Select * from table_name partition (partition_name);
Select * from table1 partition (p1);
Select * from table1 partition (p2);

---

2: Hash Partition:Hash partitioning is a partitioning method where a hash key is used to distribute rows consistently across the different partitions of table. This is usually used where ranges aren't appropriate.
The hash Partitioning is a best choice than range partition when.
You do not know beforehand how much data will map into a given range.
Sizes of range partition would differ quite substantially.

Example:

create table table1 ( emp_id number(4), emp_name varchar2(30),emp_sal number(6)) partition by hash(emp_id) ( partition p1 tablespace emp1, partition p2 tablespace emp2, partition p3 tablespace emp3);

---

3: List Partition: List partitioning is a partitioning method where we specify a list of separate values for the partitioning key in the description for each partition.

Example:

create table table1(emp_id number(3), emp_name varchar2(15), join_month varchar2(10)) partition by list(join_month) (partition p1 values(‘jan’,’fab’,’march’,’apr’), partition p1 values(‘may’,’jun’,’jul’,’aug’), partition p1 values(‘sep’,’oct’,’nov’,’dec’);

---