In this article we will see types of partitions available in Oracle.

Range Partitioning :

Range Partitioning works with range of column values. It sets lower boundaries and upper boundaries to the partition. It is widely used with DATE data types.

Lets create one range partition to understand it better.It is generally used with DATE columns.

Example :

HASH Partitioning :                                                                                                   

This method uses hash function on the partitioning columns to stripe data into partitions. The hashing algorithms distributes data evenly across the partitions. It is the ideal method of distributing data evenly provided that number of partitions are power of two. 

Example :

List Partitioning  :

This method allows control over how rows maps to the Partitions. Unlike range partition , List partitions are used to partition discrete unrelated data into partitions. You need to provide list of values for partitions.

Example :

Interval Partition :

In Interval Partitions database automatically create a partition of a specified interval when data is inserted beyond the existing range partitions.
Note : IOT does not support interval partitioning

Example :

Reference Partitioned Table : 

In reference partitioning table , partitioning works with primary key and foreign key relationship. You do not need to specify partitions at child table. Those are automatically created based on parent table.

Let’s create reference partitions table

Step 1 : Create parent table with primary key.

Step 2 : Now create table with reference partitioning 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.