Interval Partitioning in Oracle - 11g new feature

As we are aware of the concept of Partitioning in Oracle which helps in three ways  a) Performance  b) Manageability  and  c) Availability.

Having basic idea about partition ( especially range partition ) will help you here to understand about interval partitioning easily. 

We have an article already posted in this site in explaining about Partitioning, types and benefits. Here we go with the link 

Article about Oracle Partitioning

Assuming you have gone through the above link already; here we start to know about Interval Partitioning. 

Why Interval Partition?  How it works ?

Interval Partition is one of the key new features of Oracle 11i.  We can highlight interval partition as extended version of Range partition. 

For example:  we have created partitions in a table for the below range criteria of data belongs to a column of a table. 

Partition 1    for the data lesser than Jan-2016

Partition 2    for the data between  Jan-2016 to Jun 2016

Partition 3    for the data between  Jul-2016  to Dec 2016

Partition 4    for the data between  Jan-2017 to Jun 2017

Inserting data lesser than Jun-2017  will be placed into its right partition; but when we insert a data greater than Jun 2017; for example .    15-Aug-2017;  we will end up with an error message saying there is no partition available. 

To fix this growing data scenario, we will have to create or add new partitions as a maintenance activity month on month.  So, Interval Partition helps here.  Interval Partition is automatically created new partitions whenever required as per the new data is getting inserted.

Example code  to create Interval Partitioned table :-

CREATE TABLE interval_tab (
  id           NUMBER,
  code         VARCHAR2(10),
  description  VARCHAR2(50),
  created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('30-Apr-2017','DD-MON-YYYY'))
);

Adding the yellow marked line specially for interval partitioning orders Oracle to create new partitions for every new month data greater than 30-Apr-2017. 

For example;  if we insert into a column "created date"  with the data lesser than 30-Apr-2017 it will be a part of Partition part_01.  If we inserted a data greater than 30-Apr-2017, it will create new partition and then insert the new data. 

The new partitions will be created for  the new months of May-2017, Jun-2017, Jul-2017 etc.,  and so on..  So there is no maintenance activity required.  This will be managed automatically. 

List down all the partitions created for a particular table:-

Below query will provide the details about the partitions created by user and interval partitions created by Oracle automatically.

SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
WHERE table_name = <table_name>;

How to add interval partition into already partitioned table?

We are in a scenario of a table which was partitioned already, but we would like to add interval partitioning to avoid further maintenance activities. We can go with an ALTER command. 

ALTER TABLE TABLE_PARTITIONED1 SET INTERVAL(NUMTOMYINTERVAL (1, 'MONTH'));

As we have possibility of creating Composite partitioning  such as ( Range-Hash Partitioning, Range-List Partitioning, Range -Range, List-List and so on.. );

We can have Composite Partitioning with Interval Partitions as well as mentioned below.

• Interval-Hash
• Interval-List
• Interval-Range

Hope this helps.. if yes, then please share this post and comment your feedback.  

Please LIKE this   FB Page    to get more articles like this.