Oracle Interval Partitioning - RANGE Partitioning

Interval partitioning is an enhancement to range partitioning in Oracle 11g and interval partitioning automatically creates time-based partitions as new data is added. Range partitioning allows an object to be partitioned by a specified range on the partitioning key.  

For example, if a table was used to store sales data, it might be range partitioned by a DATE column, with each month in a different partition. Therefore, every month a new partition would need to be defined in order to store rows for that month.  If a row was inserted for a new month before a partition was defined for that month, the following error would result:

ORA-14400: inserted partition key does not map to any partition 

If this situation occurs, data loading will fail until the new partitions are created.  This can cause serious problems in larger data warehouses where complex reporting has many steps and dependencies in a batch process.  Mission critical reports might be delayed or incorrect due to this problem. 

An Interval Partitioning Example

Interval partitioning can simplify the manageability by automatically creating the new partitions as needed by the data.  Interval partitioning is enabled in the table’s definition by defining one or more range partitions and including a specified interval.  For example, consider the following table:

create table
pos_data (
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER(6),
   qty_sold          NUMBER(3)
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
   PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
); 

Here, two partitions have been defined and an interval of one month has been specified.  If data is loaded into this table with a later date than the greatest defined partition, Oracle will automatically create a new partition for the new month.  In the table above, the greatest defined interval is between July 1, 2007 and August 1, 2007. 

Inserting a row that has a date later than August 1, 2007 would raise an error with normal range partitioning.  However, with interval partitioning, Oracle determines the high value of the defined range partitions, called the transition point, and creates new partitions for data that is beyond that high value.

insert into pos_data (start_date, store_id, inventory_id, qty_sold)
values ( '15-AUG-07', 1, 1, 1);

SELECT
   TABLE_NAME,
   PARTITION_NAME,
   PARTITION_POSITION,
   HIGH_VALUE
FROM
   Remote DBA_TAB_PARTITIONS
WHERE
   TABLE_NAME='POS_DATA'
ORDER BY
   PARTITION_NAME;

PARTITION_NAME    HIGH_VALUE 

POS_DATA_P0       TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

POS_DATA_P1       TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P81    TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Notice that a system generated partition named SYS_P81 has been created upon inserting a row with a partition key greater than the transition point.  Oracle will manage the creation of new partitions for any value beyond the high value.  Therefore, the values do not need to be inserted in sequence.

Since the partitions are named automatically, Oracle has added a new syntax in order to reference specific partitions effectively.  The normal way to reference a specific partition is to use the partition (partition_name) in the query:

select
   *
from
   pos_data partition (SYS_P81);

However, it would be cumbersome to look up the system generated partition name each time.  Therefore, the new syntax to specify a partition is by using thepartition for (DATE) clause in the query:

select 

   *
from
   pos_data partition for (to_date('15-AUG-2007','dd-mon-yyyy'));

Another useful feature of partitioning is the ability to distribute partitions across different tablespaces.  With interval partitioning, this can be accomplished by naming all of the tablespaces in the table definition’s “store in” clause.  The system created partitions are then assigned to different tablespaces in a round robin manner.  For example, if the choice was to distribute the table across three tablespaces - tablespaceA, tablespaceB, and tablespaceC - use the following clause in the table definition.

INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
STORE IN (tablespaceA, tablespaceB, tablespaceC)

Example 2:
Defining Interval for Range partitioning where each partition specifies a range of an Year ( Range is specified on A Date Type column). 
CREATE TABLE orders_range(order_id NUMBER 
                         ,client_id NUMBER 
                         ,order_date DATE)  
  PARTITION BY RANGE(order_date)
    INTERVAL (NUMTOYMINTERVAL(1,'year'))
    (PARTITION orders2011 VALUES LESS THAN (to_date('1/1/2012','dd/mm/yyyy'))

    ,PARTITION orders2012 VALUES LESS THAN (to_date('1/1/2013','dd/mm/yyyy')));

Example 3:
Defining Interval for Range partitioning on a column with Number Data type, each new partition will be created for accommodating  5000 more in range of values than previous partition as shown below:

create table test

           (sno number(6),
           last_name varchar2(30),
           salary number(6))
           partition by range(salary)
           Interval  (5000)
          (
       partition p1 values less than (5000),
       partition p2 values less than (10000),
       partition p3 values less than (15000),
       partition p4 values less than (20000));

Table created.

NUMTOYMINTERVAL

Syntax: NUMTOYMINTERVAL(n, 'interval_unit')

NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. The argument n can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value. The argumentinterval_unit can be of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype. The value for interval_unit specifies the unit of n and must resolve to one of the following string values:

• 'YEAR'
• 'MONTH'

interval_unit is case insensitive. 

Restrictions on  Interval Partitioning

There are a few restrictions on interval partitioning that must be taken into consideration before deciding if it is appropriate for the business requirement:

• Cannot be used for index organized tables
• Must use only one partitioning key column and it must be a DATE or NUMBER
• Cannot create domain indexes on interval partitioned tables
• Are not supported at the sub-partition level

NOTE: This feature should be used as an enhancement to range partitioning when uniform distribution of range intervals for new partitions is acceptable.  If the requirement demands the use of uneven intervals when adding new partitions, then interval partitioning would not be the best solution.

Interval Partitioning Commands

There are a few new commands to manage interval partitioning.  First, convert a range partitioned table to use interval partitioning by using command:

alter table set interval(expr).

Consider this range partitioned table:

create table
pos_data_range (
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER(6),
   qty_sold          NUMBER(3)
)
   PARTITION BY RANGE (start_date)
(
   PARTITION pos_data_p0 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
   PARTITION pos_data_p1 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
); 

If a row with a date of August 15, 2007 is inserted into the table, it will cause an error.

SQL> insert into pos_data_range (start_date, store_id, inventory_id, qty_sold)
  2  values ( '15-AUG-07', 1, 1, 1);
insert into pos_data_range (start_date, store_id, inventory_id, qty_sold)
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

This range partitioned table can easily be converted to use interval partitioning by using the following command:

alter table pos_data_range set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));

Interval partitioning is now enabled, and the row with 15-AUG-07 can be inserted without error since Oracle will automatically create the new partition. To convert the table back to only range partitioning, use the following command:

alter table pos_data_range set INTERVAL();

The table is converted back to a range partitioned table and the boundaries for the interval partitions are set to the boundaries for the range partitions.

Using the same syntax, the interval can also be changed for existing interval partitioned tables.  If changing the original table to be partitioned every three months instead of monthly, use:

alter table pos_data set INTERVAL(NUMTOYMINTERVAL(3, 'MONTH'));

After inserting a row with the date of 15-NOV-07, a new partition is automatically generated with a high value of 01-DEC-07.

insert into
   pos_data (start_date, store_id, inventory_id, qty_sold)
values
   ('15-NOV-07', 1, 1, 1);

SELECT
   TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
FROM
   Remote DBA_TAB_PARTITIONS
WHERE    TABLE_NAME='POS_DATA'
ORDER BY
   PARTITION_NAME;

PARTITION_NAME    HIGH_VALUE 

POS_DATA_P0       TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

POS_DATA_P1       TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P81    TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P84    TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

The tablespace storage of the interval partitioned table can also be changed using a similar syntax.  For example, when using a round robin tablespace assignment for the table between tablespace1 to tablespace3, issue the following command:

alter table pos_data set STORE IN(tablespace1, tablespace2, tablespace3);

Oracle interval partitioning offers a very useful extension to range partitioning.  This greatly improves the manageability of range partitioned tables.  In addition to providing system generated new partitions, Oracle has provided a new syntax to simplify the reference of specific partitions.  Furthermore, Oracle offers a group of commands to manage the new partitioning option. 

Source: http://www.dba-oracle.com/t_interval_partitioning.htm