Back to Top

Introduction to Table Partitioning in Oracle

oracle partitioning

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:

  1. Range Partition
  2. Hash Partition
  3. List Partition
  4. Composite Partition
  5. 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

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:

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:

Note: The default partition will store employees whose state is not mentioned in any other regions.

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:

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,

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

To list partitioning information on partition level, use below query

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!!

Share:
Share on Facebook1

Share on LinkedIn0Tweet about this on TwitterShare on Google+0Buffer this pageDigg thisShare on Reddit0

Leave a Reply

Your email address will not be published. Required fields are marked *

Most Popular Posts

How to set DirectoryIndex using .htaccess?

Posted on 2 years ago

Bhumi

How to use WebService in PHP

Posted on 2 years ago

Bhumi

How to Fetch Twitter data using cURL

Posted on 5 years ago

Bhumi

An Introduction to Gulp JS

Updated 1 month ago

Bhumi

Getting started with Laravel

Updated 1 month ago

Bhumi

How to use Git init command

Updated 1 month ago

Bhumi

The Reader’s Poll – June 2015

Posted on 2 years ago

Bhumi

The Reader’s Poll – August 2014

Posted on 3 years ago

Bhumi

The Reader’s Poll – June 2014

Posted on 3 years ago

Bhumi

The Readers’ Poll – May 2014

Posted on 3 years ago

Bhumi

The Readers’ Poll – August 2013

Posted on 4 years ago

Bhumi