Partition In Oracle

Article

I just wanted to share partition concept with you all after I had a serious performance issue in a web application. I am going to explain about how partition will help us to boost the performance in the back end (Database).

You are lucky if your project has an Oracle database because we have a lot of features present in Oracle in terms of security, boosting the performance and monitoring. It’s like an ocean. I just pick up one drop from it and explain it.

What is partition? In simple words "It is used to split up the table into multiple partitions internally based on the field which we should give when we create the table". So what is the use to split a table into multiple sub-parts? Surely you will have a question, after reading the definition of partition.
Well, I will explain to you by a small example here.

There is a game, in which I tell you the name, and you need to identify your colleague.

Approach -1

I shuffle all the members in classroom and make them sit randomly. Now I ask you to find the person who is "John". Suddenly you look in the whole classroom and find "John". It may take a little time to identify the person because there is no order here. So you have to look in whole class room and find the person.

Approach-2

Now comes the point. I group (Sub Partition) them based on the alphabetical order (Field) and make them sit like wise. Now if I ask you to find to find the person who is "John", you don't need to look in the whole classroom, you just have to search within the group "J". So you can find them more quickly than in the earlier approach -1.

The same logic is happening if you implement partition in your database. it is physically to split up a table into multiple sub parts.

Types of Partition

Range Partition
List Partition
Hash Partition
Key Partition
Sub Partition.

Here I am going to explain only about Range partition.

The requirement force us to get exact partition type for our database,

Range Partition - Reference partition

We need to set the field as partition key when we create the table, for example I have to create the "Student" table in which i can set "JoiningYear" as partition key.

CREATE TABLE STUDENT
(
STDID INT,
STDNAME VARCHAR(100),
JOININGYEAR DATE
)
PARTITION BY RANGE (JOININGYEAR)
(
partition prtn_tbl_std_pt0 values less than (to_date('31/01/2016','dd/mm/yyyy')),
partition prtn_tbl_std_pt1 values less than (to_date('29/02/22016','dd/mm/yyyy')),
partition prtn_tbl_std_pt2 values less than (to_date('31/03/2016','dd/mm/yyyy')),
partitionprtn_tbl_std_PARTN_MAX values less than (maxvalue)
);

PARTITION BY RANGE - syntax

(JOININGYEAR) - We need to mention which field as partition key here.

partition - syntax

prtn_tbl_std_pt0 - user defined name (you can give whatever name here)
values less than - syntax (more option available)
(to_date('31/01/2016','dd/mm/yyyy')) - give the value, how you want to sub partition in your table.

There will be an entry recorded in partition prtn_tbl_std_pt0 , if I make an entry for a student who has joined in January. Like that all the sub tables will be created when we make an entry for the date and month.

There will be an entry recorded in partition prtn_tbl_std_PARTN_MAX , if I make an entry for a student who is joined i April because i did not create any sub partition for the month "April". So all the entries are recorded in that sub partition table which I did not create the sub partition table for the joiningyear.

So that if I write query to get the student details of those who joined in the month of "Jan-2016" the result set will be fetched easily to search in "prtn_tbl_std_pt0" instead of the whole student table.

We may have multiple tables related with "Student" table such as "Department", "Student_Details".

In this case we have primary and foreign key relationships with those tables. Here we can call "Student" table as "Parent Table" and the rest of them which is related with "Student Table" is "Child Tables".

I can split up a table into multiple sub partitions up to 1048745. You need to do only with parent table. Sub partition will be created automatically for the child tables (Reference Partition).

Reference Partition

CREATE TABLE DEPARTMENT
(
DEPTID INT,
DEPTNAME VARCHAR(20),
STDID INT,
CONSTRAINT fk_student_id
FOREIGN KEY (STDID)
REFERENCES STUDENT(STDID)
)
PARTITION BY REFERENCE (fk_student_id);

The reference partition will be created automatically, if you create the table structure with primary and foreign key relationship.

You can check your partition structure by,

"select * from user_tab_partitions" query.

Conclusion

I hope you can get basic concept of partition in Oracle. Try to implement these in your projects. You can see considerabe improvement. All the best!!!