A convenient new feature introduced in Oracle 11g is interval-based partitioning.  One simply has to create a single starting partition and specify the interval for partitions above that transition point.  As data is added to the table, Oracle will automatically create new partitions as needed.  For example, consider the following table:

CREATE TABLE interval_event_history (
   event_history_id  NUMBER (10),
   event_date        DATE,
   event_name        VARCHAR2 (50),
   event_type        VARCHAR2 (1),
   event_desc        VARCHAR2 (500)
)
PARTITION BY RANGE (event_date)
INTERVAL (NUMTOYMINTERVAL (1, ‘YEAR’))
   (PARTITION p2009 VALUES LESS THAN (TO_DATE (‘20100101’,‘YYYYMMDD’)),
    PARTITION p2010 VALUES LESS THAN (TO_DATE (‘20110101’,‘YYYYMMDD’))
    );

After that statement is executed, the table will exist with two partitions created.  If the following inserts are done:

INSERT INTO interval_event_history VALUES
   (1, ‘01-MAR-09’, ‘Circus’, ‘Q’, ‘Ringling Bros Circus’);

INSERT INTO interval_event_history VALUES
   (2, ’04-JUL-10’, ‘Fireworks’, ‘Y’, ‘LA July 4 Fireworks’);
The inserts will succeed and the table structure will remain the same because the dates fall within the predefined ranges.  When the following insert is done:

INSERT INTO interval_event_history VALUES
   (1, ’05-DEC-11’, ‘Christmas’, ‘Y’, ‘Christmas Festival’);

The insert will still succeed; however, the table structure will change.  Oracle will add a new partition to handle all dates between January 1, 2011 and December 31, 2011.  Within the table creation statement, it is possible to specify the tablespaces used for the new partitions in a round-robin format.

This new feature makes dealing with historical data much easier than in the past when either partitions had to be created at the start or maintenance had to be done on a regular basis to create new partitions.  Additionally, with the NUMTOYMINTERVAL and NUMTODSINTERVAL functions, the interval partitioning can be done in increments of years, months, days, hours, minutes or even seconds.

Link to script that contains the examples in this post.

 

Comment