11g Interval Partitioning

In versions prior to Oracle 11g, we were very likely to have faced the error shown below especially if we used range partitioning and the partition column was a date field.

We needed to ensure that we precreated all the partitions before hand based on the expected values of data that would be inserted (or updated) in a table.

ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

While we could use the MAXVALUE clause to create a ‘catch-all’ partition, this would not help us when we would like to perform any partition maintenance operations based on a date range or if wanted to use the partitioning feature to perform some kind of data archiving at the partition level.

11g Interval Partitioning

In Oracle 11g, the creation of partitions (for range) is automated and partitions are created as and when needed and takes the task of managing the creation of new partitions from the DBA. All that is required is to define the interval criteria and create the first partition. Subsequent partitions are created automatically based on the interval criteria.

create table mypart
   (ename varchar2(20), doj date)
   partition by range (doj)
   INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
   STORE IN (tbs1,tbs2)
  (partition p_2009 values less than (to_date('01-JAN-2010','DD-MON-YYYY'))
  )
;

Note the NUMTOYMINTERVAL is an SQL Funtion used to convert a number to an INTERVAL YEAR TO MONTH literal. The accepted values are ‘YEAR’ and ‘MONTH’.

The STORE IN clause will create in the partitions in a round robin manner in tablespaces tbs1 and tbs2 as we will see below.

Let us now insert some values into the table.

SQL> insert into mypart
  2   values
  3   ('Tom','21-SEP-2009');

1 row created.

SQL> insert into mypart
  2  values
  3  ('Joe','02-JAN-2010');

1 row created.

What has happened after the second insert? – a new partition ‘SYS_P42′ has been created for the year 2010 with a high value of ’01-JAN-2011’.

The first partition that we had precreated has been created in the default tablespace USERS since no tablespace name has been prescribed and the new partition has been created in the tablespace TBS2. The next partition that comes along will be created in tablespace TBS1 and so on.

SQL> select partition_name,high_value from user_tab_partitions
  2  where table_name='MYPART';

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P_2009                         TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P42                        TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> select partition_name,tablespace_name from user_tab_partitions
  2  where table_name='MYPART';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
P_2009                         USERS
SYS_P43                        TBS2

We can use the ALTER TABLE SET INTERVAL command to convert a range partitioned table to an interval partitioned table as shown below.

SQL> create table mypart2
   (ename varchar2(20), doj date)
   partition by range (doj)
  (partition p_2009 values less than (to_date('01-JAN-2010','DD-MON-YYYY'))
  )
;    2    3    4    5    6

Table created.

SQL> alter table mypart2
  2  SET INTERVAL (NUMTOYMINTERVAL(1,'YEAR');

Table altered.

We need to keep the following points in mind when using Interval Partitioning: