Interval Partitioning has been introduced in oracle 11g. With this method, we can automate the creation of range partition .While creating the partitioned table, we just need to define one partition. New partitions will be created automatically based on interval criteria when the data is inserted to the table. We don’t need to create the future partitions.

Keyword:

    INTERVAL(NUMTOYMINTERVAL(1,’MONTH’))

 

Example:

1. Lets create a monthly partitioned table ( with interval).

CREATE TABLE DBATEST.DBA_BREL_ATTRI
(
CORRELATION_ID VARCHAR2(100 BYTE),
ATTRIBUTE_KEY VARCHAR2(50 BYTE),
ATTRIBUTE_VALUE VARCHAR2(100 BYTE),
EVENT_TIMESTAMP TIMESTAMP(6)
)
TABLESPACE STCDBA
PARTITION BY RANGE (EVENT_TIMESTAMP)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION DBA_BREL_ATTRI_MIN VALUES LESS THAN (TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);

SQL> CREATE INDEX DBATEST.DBA_BREL_ATTRI_COR_ID_IDX ON DBATEST.DBA_BREL_ATTRI (CORRELATION_ID) LOCAL;

Index created.

 

Here the date 2016-05-01 is known as TRANSIT POINT . Any data inserted beyond this transit point will led to creation of a new partition automatically.

INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point

 

2. Now check dba_tab_partitions

SQL> SELECT table_name,partition_name,high_value from dba_tab_partitions where table_name='DBA_BREL_ATTRI';

TABLE_NAME PARTITION_NAME HIGH_VALUE
---------------- ---------------------------------- ---------------------------------------------------------------------
DBA_BREL_ATTRI DBA_BREL_ATTRI_MIN TIMESTAMP' 2016-05-01 00:00:00'

3. Lets insert some data and check the dba_tab_partitions:

SQL > insert into dbatest.DBA_BREL_ATTRI values ('387fade4-f0f4-4bc3-9ba3-41458fad7a4c','ApplicationName','ULA','01-JUN-2016')

commit

SQL> SELECT table_name,partition_name,high_value,interval  from dba_tab_partitions where table_name='DBA_BREL_ATTRI';

TABLE_NAME       PARTITION_NAME                     HIGH_VALUE                         INT
---------------- ---------------------------------- ---------------------------------- ---
DBA_BREL_ATTRI   SYS_P6066                          TIMESTAMP' 2016-07-01 00:00:00'    YES
DBA_BREL_ATTRI   DBA_BREL_ATTRI_MIN                 TIMESTAMP' 2016-05-01 00:00:00'    NO

 

We can see a new monthly partition has been created automatically .

Similarly

for weekly partition use the parameter – INTERVAL (numtodsinterval(7,’day’))
for yearly partition use the parameter – INTERVAL (NUMTOYMINTERVAL(1,’YEAR’))

 

Convert Existing Range partitioned table to interval partition:

ALTER TABLE dbatest.range_part SET INTERVAL(NUMTOYMINTERVAL(1,'MONTH'));

 

Restriction of Interval Partitioning:

1. Cannot be created for Index organized table(IOT)

2. The partitioning column can be only one and it must be of type NUMBER or DATE

3. Cannot use the MAXVALUE clause