Oracle Partitioning Types - IV (Interval Partition)

1.) Interval-Partitioned Tables (11g)

Extract from Oracle Documentation
-----------------------------------------------------------------------------------------
The INTERVAL clause of the CREATE TABLE statement establishes interval partitioning for the table. You must specify at least one range partition using the PARTITION clause. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database automatically creates interval partitions for data beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.
-----------------------------------------------------------------------------------------

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (load_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION data_01_2006 VALUES LESS THAN (TO_DATE('01-01-2006','DD-MM-YYYY')) TABLESPACE DATA_01,
PARTITION data_01_2007 VALUES LESS THAN (TO_DATE('01-01-2007','DD-MM-YYYY')) TABLESPACE DATA_02,
PARTITION data_01_2008 VALUES LESS THAN (TO_DATE('01-01-2008','DD-MM-YYYY')) TABLESPACE DATA_03,
PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-01-2009','DD-MM-YYYY')) TABLESPACE DATA_04
);

Table created.
Before data insert:
SQL> col TABLE_NAME format a15
SQL> col PARTITION_NAME format a15
SQL> col HIGH_VALUE format a80
SQL> set lines 300
SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME COM PARTITION_NAME HIGH_VALUE
------------------------------------------------------------
DATA_LOAD NO DATA_01_2006 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2007 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2008 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2009 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Now we insert data in this table:

insert into DATA_LOAD values (1,1,sysdate,1,'DELHI');
insert into DATA_LOAD values (2,2,sysdate-30,2,'KERELA');
insert into DATA_LOAD values (3,3,sysdate-300,3,'RAJASTHAN');
insert into DATA_LOAD values (4,4,sysdate-600,4,'PUNJAB');
insert into DATA_LOAD values (5,5,sysdate-60,5,'HARYANA');


SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME='DATA_LOAD';

TABLE_NAME COM PARTITION_NAME HIGH_VALUE
--------------- --- --------------- --------------------------------------------------------------------------------
DATA_LOAD NO DATA_01_2006 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2007 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2008 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2009 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P81 TO_DATE(' 2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P82 TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P83 TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Adding future rows:

SQL> insert into DATA_LOAD values (6,6,sysdate+300,6,'HARYANA');

1 row created.

SQL> commit;

Commit complete.

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME='DATA_LOAD';

TABLE_NAME COM PARTITION_NAME HIGH_VALUE
--------------- --- --------------- --------------------------------------------------------------------------------
DATA_LOAD NO DATA_01_2006 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2007 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2008 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2009 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P81 TO_DATE(' 2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P82 TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P83 TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P84 TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

8 rows selected.


So we see that the new INTERVAL PARTITIONS are being created for future dates and dates beyond the high value of the last range partition.

Part I
Part II
Part III
Part V
Part VI
Part VII