The size of data has become massive today. Multiple sources of data collection created a flood of structured, semi-structured and non-structured data. Management of these data is a highly competitive task. And above all, the necessity of fetching a small chunk of data in few seconds from the ocean of gigabytes rapidly is exigent. Oracle Partitioning is an important utility to overcome this obstacle.
Creation of partitioned table and the non-partitioned table is similar in Oracle except the usage of PARTITION BY clause in former.
What is Partitioning?
Partitioning divides the data in a table into several logical repositories based on different criteria. They also help to manage these data with a higher level of granularity. With the help of partitioning, accessibility of those data gets faster and easier during high-end transaction flow. Moreover, the failure of operation on that partition does not affect the processing of others.
Advantages of Partitioning
- Performance Improvement
- Easier Accessibility of data
- Efficient Manageability of data
- Types of Partitioning
What are the types of Partition in Oracle?
Partitioning in Oracle is divided as below:
- Range Partition
- Hash Partition
- List Partition
- Composite Partition
- Reference Partition
Let’s understand all the partition one by one with example.
What is Range Partition?
In range partition, data in the table is partitioned logically, depending on range criteria. It could be based on date, a year or a range between higher and lower values of any column. PARTITION BY RANGE clause is used to partition data here.
For example,
Let us partition table employees based on decade in which they joined
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE TABLE employees ( employee_id number(4), emp_name varchar2(200), date_of_joining date, year_of_joining number(4) ) partition by range (year_of_joining) (partition p1 values less than (1980) tablespace tsdec1, partition p2 values less than (1990) tablespace tsdec2, partition p3 values less than (2000) tablespace tsdec3, partition p4 values less than (2010) tablespace tsdec4, partition p5 values less than (2020) tablespace tsdec5, partition p6 values less than (MAXVALUE) tablespacetsdecmax); |
Note: If you will not add a partition of MAXVALUE, a table will not allow inserting data for year greater than 2020.
What is Hash Partition?
Hash partition helps to overcome performance and manageability reasons. In absence of properly defined range of partitioning data, the option to use hash partition is advisable. Rows of the table are distributed specifically based on a hash value of partitioning key. In other words, data are evenly distributed in partitions specified in STORE IN clause. The user does not have control over the partitioning of data in hash partitioning.
For example,
Let us do hash partitioning of attendance table of employees based on the reason of leave:
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE emp_attendance( emp_id NUMBER, present_date DATE, Reason_of_leave VARCHAR2(200) ) PARTITION BY HASH (reason_of_leave) PARTITIONS 4 STORE IN (tablespace1, tablespace2, tablespace3, tablespace4); |
Note: The number of partitions in PARTITIONS clause should be in power of 2 here (2, 4, 8, 16…and so on)
What is List Partition?
The semantics of list partition is similar to that of range partition. Instead of PARTITION BY RANGE, you have to use PARTITION BY LIST clause here. You have to mention a list of literal values in the partitioning clause to properly group the data. Hence in list partition data is organized into a sensible but unorderly fashion.
For example,
Let us partition employees based on North, East, West and South regions of India:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE TABLE employees( emp_id number, emp_name varchar2(200), state varchar2(40), country varchar2(50) ) PARTITION BY LIST (state) (PARTITION p_north VALUES ('JAMMNU-KASHMIR', 'UTTAR_PRADESH'), PARTITION p_east VALUES ('SIKKIM', 'ASSAM'), PARTITION p_west VALUES ('GUJARAT', 'MAHARASHTRA', 'RAJASHTHAN'), PARTITION p_south VALUES ('KARNATAKA', 'TAMIL_NADU') PARTITION p_restofindia VALUES (DEFAULT)); |
What is Composite Partition?
You can do sub-partitioning of data divided among several main partitions in a composite partition. In simple language, partition data of the table first by range or list using PARTITION B RANGE or PARTITION BY LIST clause. Next, sub-partition the data of those parent partitions using SUBPARTITION clause. Sub-partition can be a list, range or hash partition (PARTITION BY LIST / PARTITION BY RANGE / PARTITION BY HASH).
For Example,
Let us create hash subpartitioning of employees data that are already partitioned by range:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | CREATE TABLE employees ( emp_id NUMBER, emp_name VARCHAR2 (200), year_of_joining NUMBER, date_of_joining DATE, organization_id NUMBER ) PARTITION BY RANGE (year_of_joining) SUBPARITION BY HASH (organization_id) SUBPARTITIONS 8 STORE IN (tbsp1, tbsp2, tbsp3, tbsp4) ( PARTITION par1 VALUES LESS THAN (1980), PARTITION par2 VALUES LESS THAN (1990), PARTITION par3 VALUES LESS THAN (2000), PARTITION par4 VALUES LESS THAN (2010), PARTITION par5 VALUES LESS THAN (2020), PARTITION pardef VALUES LESS THAN (MAXVALUE) ); |
Note: The sub-partitions are not required to be residing in the same tablespace as of the main partition.
What is Reference Partition?
PARTITION BY REFERENCE clause is used to reference-partition the table. The referential constraint that is required to be partitioned is mentioned in this clause. It becomes partitioning referential constraint thereafter.
For example,
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | CREATE TABLE employees( emp_id NUMBER, name VARCHAR2(200), country VARCHAR2(200) CONSTRAINT emp_id_pk PRIMARY KEY(emp_id) ) PARTITION BY RANGE(join_date) (PARTITIONquarter1 VALUES LESS THAN (TO_DATE('01-APR-2017','DD-MON-YYYY')), PARTITION quarter2 VALUES LESS THAN (TO_DATE('01-JUL-2017','DD-MON-YYYY')), PARTITION quarter3 VALUES LESS THAN (TO_DATE('01-SEP-2017','DD-MON-YYYY')), PARTITION quarter4 VALUES LESS THAN (TO_DATE('01-DEC-2017','DD-MON-YYYY')) ); CREATE TABLE emp_attendance( attendance_id NUMBER, emp_id NUMBER, date_of_presence DATE, reason_of_leave VARCHAR2(200), CONSTRAINT attendance_id_fk FOREIGN KEY(emp_id) REFERENCES orders(emp_id) ) PARTITION BY REFERENCE(attendance_id_fk); |
Here, employees table acts as a master table. In child table emp_attendance, emp_id is present as a foreign key. And data is partitioned based on referential constraint attendance_id_fk.
The partitions of a reference-partitioned table can be named. If a partition is not explicitly named, then it inherits its name from the corresponding partition in the parent table, unless this inherited name conflicts with an existing explicit name. In this case, the partition has a system-generated name.
Data dictionary tables of Partitions
To list number of partitioning tables in your schema, use below query
1 2 3 | SELECT * FROMuser_part_tables; |
To list partitioning information on partition level, use below query
1 2 3 | SELECT * FROMuser_tab_partitions; |
So what are your thoughts? Do you have any questions? Have you used table partition in any project? share your experience with us in comment section!!
