SQL WORLD

Categories: SQL Performance TuningSQL Tutorials

Table partition | How to do Table Partitioning in Oracle with Example

Table partition :

There are so many aspects which are important in improving the performance of SQL. Partition allows tables, indexes and index organized tables to be subdivided into smaller pieces. Table partition is used to reduce the cost and improving performance of the application. There are some partition mechanisms using which one can divide a table into smaller pieces. Partitions can be used in so many application where we need to improve the performance. Each partition has its own name and it has own memory storage.

partition allows table,index or index organized tables to be subdivided in to smaller pieces and each piece of table,index or index organized table is called as Partition.

What is mean by Table Partition :

Following are Advantages of Partition:

1.Increase Performance

2.Increases availability

3.Enable storage cost optimization

4.Enables Simpler management

“We can not partition a table with Long and long raw datatype…”

When to partition the table?

1.Table should be greater than 2 GB

2.Tables which contains historical data in which new data will be added in to newest partition. The real life example of this is historical table which contains updatable data for one year other data is read only.

3.When contents of the table needs to be distributed in different storage devices.

4.When table performance is weak and we need to improve performance of application.

Each row in partitioned table is unambiguously assigned to single partition table. The Partitioning key is comprised of one or more columns that determine the partition where each row will be stored.

Types Of Table partitioning:

There are following types of Table partition:

1.Range Partition

2.List Partition

3.Hash Partition

1.Range Partition:

When in the specified table the data is based on the specific date range and it is properly divided in some range then user should go for the partitioned named as ‘Range Partition’.This partition type is most common type of Table partitioning which is been useful for Data warehouse to store the historical data in given date range.The partitioning is done in such way that the expression values lies within the specific range.This kind of Table partition is used when there is a particular date range available.

Syntax:

Create table Tablename

(Column_name1 datatype(size)….

Column_name-n datatype(size))

Partition by range(Column needs to be partitioned)

(Partition partition_name1 values less than(value1)….

Partition partition_name-n values less than(maxvalue));

Real Life Example of Range Partition:

Step 1: Creation of partitioned table

Create table Employee(emp_no number(2),

Salary number(2))

partition by range(Salary)

(partition p1 values less than(10000),

partition p2 values less than(20000),

partition p3 values less than(30000),

partition p4 values less than(maxvalue));

Here We put the partition on salary of the employee.

Step 2: Insert the values in table.

Insert into Employee

values(‘Amit’, 40000);

Value inserted in partition p4 which is maximum value.

Insert into Employee

values(‘Rama’, 11000);

Value inserted in partition p2.

Insert into Employee

values(‘Shiva’, 25000);

Value inserted in partition p3.

Queries for Range Partition:

1.Selecting records from partitioned tables.

Select * from Employee;

Select * from Employee partition(p1);

2.Adding new Table partition:

Alter table Employee

add partition p5 values less than(50000);

3.Drop Table partition:

Alter table Employee

drop partition p1;

4.Rename Table partition:

Alter table Employee

Rename partition p1 to p6;

5.Truncate partition:

Alter table Employee

Truncate partition p1;

6.Split partition:

Alter table Employee

Split partition p1 at (5000)

into (partition p10,partition p11);

7.Moving partition:

Alter table Employee

move partition p1 to tablespace ABCD;

2.List Partitioning:

List partition enables you to explicitly control how the partition of tables needs to be don by specifing list of distinct values as partition key in description of each partition.When there is a set of distinct values in the table which is properly divided then user should go with list partition.By listing the distinct values user should do the partition.Simple example is the table storing the country data in which state is distinct column.So You can partition the table using list of state values.

Syntax:

Create table Tablename

(Column_name1 datatype(size)….

Column_name-n datatype(size))

Partition by range(Column needs to be partitioned)

(Partition partition_name1 values less than(value1)….

Partition partition_name-n values less than(maxvalue));

Real life Example:

Create table with partition to State column:

Create table Employee(emp_no number(2),

State varchar2(20))

partition by List(State)

(partition p1_Maharashtra values (‘Maharashtra’),

partition p2_Gujrath values(‘Gujrath’),

partition p3_Rajsthan values(‘Bengal’),

partition p4_Other values (Default));

Insert into table:

Insert into Employee

values(‘Amit’, ‘Maharashtra’);

Value inserted in partition p1_Maharashtra which is maximum value.

Insert into Employee

values(‘Rama’, ‘Kerala’);

Value inserted in partition p4_Others.

Queries Related to List Partition:

1.Selecting records from partitioned tables.

Select * from Employee;

Select * from Employee partition(p1_Maharashtra);

2.Adding new partition:

Alter table Employee

add partition p5_Kerala values(‘Kerala’);

3.Drop partition:

Alter table Employee

drop partition p1_Maharashtra;

4.Rename partition:

Alter table Employee

Rename partition p1_Maharashra to p6_Maha;

5.Truncate partition:

Alter table Employee

6.Moving partition:

Alter table Employee

move partition p1_Maharashtra to tablespace ABCD;

3.Hash Partition:

Hash partitioning is type of partitioning where data is partitioned by using the hashing algorithms.Different hashing algorithms are applied to partition key that you identify.Hash partition is mainly used to distribute data among the different storage devices.Hash partition is easy to use and best alternative for list partition when data is not historical.

Syntax:

Create table Table_name

(Column_name1 datatype1…

Column_name n datatype ‘n’)

Partition by Hash(column_name)

Partitions partition_number);

Real Life Example:

Create table Employee

(emp_no number(2),

emp_name varchar(2))

partition by hash(emp_no) partitions 5;

The Above statement will create 5 partitions named:

Sys_P1

Sys_P2

Sys_P3

Sys_P4

Sys_P5

When we insert the records into table according to Hash algorithm data will split into different partitions.

How To create partition on non partitioned Tables?

If you have Employee table which is not partitioned and you need to add the partition to the Employee table.There is direct way to add the partition to the table.The ‘Alter Table Modify’ clause is used to add the partition to the existing non partitioned table.In Addition we need to use the keyword named ‘Online’.

Syntax:

Alter table tablename

Modify

Partition by partition_name(Column_name)(

Partition partition_name values ……

) online;

Real life Example:

Suppose you want to add the partition to the existing table named ‘Employee’ and partition it by using City column.

Query used:

Alter table Employee

Modify

Partition by LIST(Employee_City)

(

Partition P_Kolhapur values(‘Kolhapur’),

Partition P_Sangali values(‘Sangli’),

Partition P_OTH values(default)

) online;

Check out your SQL Topics :

Unix Tutorials :

Oracle Business Intelligence Tutorial :

Click Here for SQL interview Questions

Hope you like this article on Table partitioning to improve the performance of application.If you like this article kindly comment.

HOME

Amit S

Oracle Consultant with vast experience in Oracle BI and PL/SQL Development. Amiet is the admin head of this website who contributes by preparing tutorials and articles related to database technologies. He is responsible to manage the content and front-end of the website.