Introduction
I just wanted to share the partition concept with you all after I had a serious performance issue in a web application. I am going to explain 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 performance, and monitoring. It’s like an ocean. I just pick up one drop from it and explain it.
What is a 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 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 the 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 the whole classroom 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 likewise. Now if I ask you 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 a partition in your database. it is physical 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 forces us to get the exact partition type for our database
Range Partition - Reference partition
We need to set the field as a partition key when we create the table, for example, I have to create the " Student" table in which I can set "JoiningYear" as the 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
(JOININGYEAR) - We need to mention which field is the 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 joined in January. For 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 in 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 relationships.
You can check your partition structure by
"select * from user_tab_partitions
Conclusion
I hope you can get a basic concept of partition in Oracle. Try to implement these in your projects. You can see considerable improvement. All the best
Read more articles on Oracle