New features of Oracle 11g simplified management of range partitions allowing INTERVAL partitioning. It allows move part of functionality solved currently by ETL pre-wrappers to default processing of RDBMS defined in Data dictionary metadata.
Simple example of range - interval partitioning with monthly period.
create table tt3 (
a integer
,b date
)partition by range (b) interval (NUMTOYMINTERVAL(1,'MONTH'))
(partition P1 values less than(to_date('10000101','yyyymmdd')))
/
SQL> select partition_name,high_value from user_tab_partitions where table_name='TT3';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------
P1 TO_DATE(' 1000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'
Insert into tt3 (a,b) values(1,to_date('20080430','yyyymmdd'))
/
Formerly it would be cause of an error.
In 11g with an INTERVAL specified new partition is automatically created.
Disadvantage:
System generates its own name of partition out of comfortable convention. Supporting programs should analyze HIGH_VALUE (Long data type) instead of PARTITION_NAME.
Advantage:
It supports both adding new partition at the end of table and inside of existing partition. You should not create complex mechanism to prepare partitions before, system creates partitions when they are needed.
Insert into tt3 (a,b) values(1,to_date('20080330','yyyymmdd'))
/