Oracle 11g Interval Partitioning

New in 11g is Interval Partitioning. This extends the functionality of range partitioning to where you define equal partitions using and interval definition. When using Interval partitioning Oracle will automatically create new partitions as they are needed. Oracle creates the new partition at the time of the first record insert for the new partition. This greatly helps the manageability of partitioned tables by saving the DBA from having to manually create new partitions.

The new interval partitioning valid combinations are Interval, Interval-List, Interval-Hash and Interval-Range.

This will work well for range partitioning where partitioning was done based on date ranges.

Original Range Partitioning Example:

CREATE TABLE mrmessin.emp
(
EMPNO NUMBER(4),
ENAME VARCHAR2(30),
JOB VARCHAR2(20),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
PARTITION BY RANGE (hiredate)
( PARTITION part_1999 values LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
PARTITION part_2000 values LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY'))
PARTITION part_2001 values LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY'))
PARTITION part_2002 values LESS THAN (TO_DATE('01-JAN-2003','DD-MON-YYYY'))
PARTITION part_2003 values LESS THAN (TO_DATE('01-JAN-2004','DD-MON-YYYY'))
) ;

The new interval partitioning can simplify the creation of the table and then partitions created automatically as data is added that would go into the new partitions.

New Interval Ranger Partitioning Example:

CREATE TABLE mrmessin.emp
(
EMPNO NUMBER(4),
ENAME VARCHAR2(30),
JOB VARCHAR2(20),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
PARTITION BY RANGE (hiredate)
INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
( PARTITION part_1999 values LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
) ;

Now lets go through an example to see if the partitions are created as the data is inserted. This will allow us to see how the partitions are created and how oracle names them. We will also see if we can rename the new partitions to better names after the fact.

SQL> -- drop the table if it already exists
SQL> drop table mrmessin.emp ;

Table dropped.

SQL>
SQL> -- create a interval partition table.
SQL> -- we will use the definition of the scott.emp table
SQL> CREATE TABLE mrmessin.emp
2 (
3 EMPNO NUMBER(4),
4 ENAME VARCHAR2(30),
5 JOB VARCHAR2(20),
6 MGR NUMBER(4),
7 HIREDATE DATE,
8 SAL NUMBER(7,2),
9 COMM NUMBER(7,2),
10 DEPTNO NUMBER(2)
11 )
12 PARTITION BY RANGE (hiredate)
13 INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
14 (
15 PARTITION part_1999 values LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
16 ) ;

Table created.

SQL>
SQL>
SQL> -- add a primary key to the partitioned table
SQL> alter table mrmessin.emp add constraint emp_empno_pk primary key (empno) disable ;

Table altered.

SQL>
SQL> -- create the unique index for our primary key
SQL> create unique index mrmessin.emp_empno_pk on mrmessin.emp (empno) tablespace users ;

Index created.

SQL>
SQL> -- enable our primary key
SQL> alter table mrmessin.emp enable primary key using index ;

Table altered.

SQL>
SQL> -- gather stats
SQL> EXEC DBMS_STATS.gather_table_stats('MRMESSIN','EMP') ;

PL/SQL procedure successfully completed.

SQL>
SQL> -- take a look at the partitions of the table
SQL> select table_owner, table_name, partition_name, tablespace_name, num_rows
2 from dba_tab_partitions
3 where table_name = 'EMP' ;

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
MRMESSIN EMP
PART_1999 USERS 0


SQL> -- let insert some data for a partition.
SQL> insert into mrmessin.emp values (1, 'Mike Messina', 'CEO', 1, to_date('15-NOV-1999', 'DD-MON-YYYY'), 10000, 1000, 1) ;

1 row created.

SQL> insert into mrmessin.emp values (2, 'Joe Messina', 'COO', 1, to_date('10-JAN-2000', 'DD-MON-YYYY'), 10000, 1000, 1) ;

1 row created.

SQL> commit ;

Commit complete.

SQL>
SQL> -- gather stats again
SQL> EXEC DBMS_STATS.gather_table_stats('MRMESSIN','EMP') ;

PL/SQL procedure successfully completed.

SQL>
SQL> -- take a look at the partitions of the table
SQL> select table_owner, table_name, partition_name, tablespace_name, num_rows
2 from dba_tab_partitions
3 where table_name = 'EMP' ;

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
MRMESSIN EMP
SYS_P46 USERS 1

MRMESSIN EMP
PART_1999 USERS 1


SQL>
SQL> -- insert some more data that should cause more partitions to be created
SQL> insert into mrmessin.emp values (3, 'Frank Messina', 'CIO', 1, to_date('12-DEC-2001', 'DD-MON-YYYY'), 10000, 1000, 1) ;

1 row created.

SQL> insert into mrmessin.emp values (4, 'Joe Joe Shamo', 'Bouncer', 1, to_date('14-FEB-2002', 'DD-MON-YYYY'), 1000, 100, 1) ;

1 row created.

SQL> insert into mrmessin.emp values (5, 'Mixer Man', 'Bar Tender', 1, to_date('04-APR-2003', 'DD-MON-YYYY'), 1000, 100, 1) ;

1 row created.

SQL> commit ;

Commit complete.

SQL>
SQL> -- gather stats again
SQL> EXEC DBMS_STATS.gather_table_stats('MRMESSIN','EMP') ;

PL/SQL procedure successfully completed.

SQL>
SQL> -- take a look at the partitions of the table
SQL> select table_owner, table_name, partition_name, tablespace_name, num_rows
2 from dba_tab_partitions
3 where table_name = 'EMP' ;

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
MRMESSIN EMP
SYS_P46 USERS 1

MRMESSIN EMP
SYS_P47 USERS 1

MRMESSIN EMP
SYS_P48 USERS 1

MRMESSIN EMP
SYS_P49 USERS 1

MRMESSIN EMP
PART_1999 USERS 1


SQL> alter table mrmessin.emp rename partition sys_p46 to part_2000 ;

Table altered.

SQL> alter table mrmessin.emp rename partition sys_p47 to part_2001 ;

Table altered.

SQL> alter table mrmessin.emp rename partition sys_p48 to part_2002 ;

Table altered.

SQL> alter table mrmessin.emp rename partition sys_p49 to part_2003 ;

Table altered.

SQL> select table_owner, table_name, partition_name, tablespace_name, num_rows
2 from dba_tab_partitions
3 where table_name = 'EMP' ;


TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ---------
MRMESSIN EMP
PART_2000 USERS 1

MRMESSIN EMP
PART_2001 USERS 1

MRMESSIN EMP
PART_2002 USERS 1


MRMESSIN EMP
PART_2003 USERS 1

MRMESSIN EMP
PART_1999 USERS 1

SQL>


Summary

Interval Partitioning provides a huge boost in partition management. This makes implementing partitioning much more attractive to those DBAs that have very full plates making partition maintenance more automatic and reducing our DBA maintenance activities. All other activity appears to match normal range partitioning keeping all the other benefits of range partitioning.