Interval Partitioning in 11g is a extension of range partitioning. They automate creation of equi-sized partitions. Partitions are created as metadata and only the initial partition is made persistent.Additional partitions and local indexes are created automatically when the data is inserted.
Creating Interval Partitioned table:
Example :
create table invoices
(
invoice_no number not null,
invoice_date date not null,
comments varchar2(500)
)
partition by range (invoice_date)
interval (numtoyminterval(1,'month'))
(partition p_first values less than ('01-JAN-2008') tablespace users);
The line interval (numtoyminterval(1,'month')) indicates the table is partitioned by monthly intervals of INVOICE_DATE.
The Function numtoyminterval function converts a number to an INTERVAL YEAR TO MONTH literal.In this context it indicates an interval of 1 month.Likewise, For yearly partitions use the keyword "YEAR" on the same function.
Also the function numtodsinterval will be used to create partitions with intervals of ‘week’,’day’,’hour’,’minute’,’second’
Example :
To Create a weekly partition the syntax would be
create table invoices
(
invoice_no number not null,
invoice_date date not null,
comments varchar2(500)
)
partition by range (invoice_date)
interval (numtodsinterval(7,'day'))
(partition p_first values less than ('01-JAN-2008') tablespace users);
Querying the table ‘user_tab_partitions’ will show one partition created.
SQL> select table_name, partition_name, high_value from user_tab_partitions
where table_name = 'invoices'
order by table_name, partition_name;
TABLE_NAME | PARTITION_NAME | HIGH_VALUE |
| INVOICES | P_FIRST | TO_DATE( 2008-01-01 00:00:00, 'SYYYY-MM-DD HH24:MI:SS') |
-- Insert statements for Invoice Data
SQL>insert into invoices values(1,to_date('16-jan-2008','dd-mon-yyyy'), 'test invoice1');
SQL> insert into invoices values(1,to_date('16-feb-2008','dd-mon-yyyy'), 'test invoice2');
SQL>> commit;
Adding data beyond the range of the initial partition(P_FIRST) will create new partitions as shown below.
SQL> select table_name, partition_name, high_value from user_tab_partitions
where table_name = 'invoices'
order by table_name, partition_name;
TABLE_NAME | PARTITION_NAME | HIGH_VALUE |
| INVOICES | P_FIRST | TO_DATE( 2008-01-01 00:00:00, 'SYYYY-MM-DD HH24:MI:SS') |
| INVOICES | SYS_P4008 | TO_DATE(' 2008-02-01 0:00:00', 'SYYYY-M M-DD HH24:MI:SS') |
| INVOICES | SYS_P4009 | TO_DATE(' 2008-03-01 00:00:00', 'SYYYY-M M-DD HH24:MI:SS' |
Note: Whenever a partition is created, the high value column indicates all records whose date is less than the specified high_value date falls under that partition
If data is inserted later than the current maximum high value(say like 6 months ahead),only the required partition is created, not the intermediate partitions.
SQL> insert into invoices values(3,to_date('16-Jul-2008','dd-mon-yyyy'), 'test invoice3');
SQL> commit;
where table_name = 'invoices'
order by table_name, partition_name;
TABLE_NAME | PARTITION_NAME | HIGH_VALUE |
| INVOICES | P_FIRST | TO_DATE( 2008-01-01 00:00:00, 'SYYYY-MM-DD HH24:MI:SS') |
| INVOICES | SYS_P4008 | TO_DATE(' 2008-02-01 0:00:00', 'SYYYY-M M-DD HH24:MI:SS') |
| INVOICES | SYS_P4009 | TO_DATE(' 2008-03-01 00:00:00', 'SYYYY-M M-DD HH24:MI:SS' |
| INVOICES | SYS_P4010 | TO_DATE(' 2008-08-01 00:00:00', 'SYYYY-M M-DD HH24:MI:SS' |
The partition is created is for the month of ‘2008-07’ and not for the intermediate months between ‘2009-03’ and ‘2009-08’.
Converting Range-Partitioned Table to Interval Partitioned Table:
Example : create a range partitioned table called ‘SALES’ which is range-paritioned by sale_date
create table sales
(
sales_no number,
sale_date date not null
)
partition by range (sale_date)
(partition p_first values less than ('01-jan-2008') tablespace users);
To convert the range-partitioned table ‘SALES’ to interval partition(yearly intervals),the following Alter statement will suffice.
SQL>alter table sales set interval (numtoyminterval(1,'year'));
Disabling Interval Partition:
In order to disable interval partitioning on the SALES table,
SQL>alter table sales set interval ();
The table will go back to range partitioned where you have to manually add partitions.
Manually adding a partition to a Interval Partitioned Table(Exchange Partitions):
The database automatically creates a partition for an interval when data for that interval is inserted.But if there's a scenario for a partition exchange where you need to explicitly create interval partitions you can do it by using the ‘lock table partition for ‘ command shown below.
Create a staging table for invoices called "INVOICES_STAGING" that holds invoices for the month of August,2008
create table invoices_staging
(
invoice_no number not null,
invoice_date date not null,
comments varchar2(500)
);
Insert records into "INVOICES_STAGING” table for the month of August,2008.
SQL>insert into invoices_staging values(5,to_date('18-aug-2008', 'dd-mon-yyyy'), 'test invoice4');
SQL>insert into invoices_staging values(6,to_date('21-aug-2008', 'dd-mon-yyyy'), 'test invoice5');
SQL>insert into invoices_staging values(7,to_date('31-aug-2008', 'dd-mon-yyyy'), 'test invoice6');
SQL> commit;
The data from the staging table "INVOICES_STAGING" can now be exchanged onto the main "INVOICES" table that we created earlier which is interval partitioned by month.
First, manually create the partition on the main "INVOICES" table using the following lock command
SQL> lock table invoices partition for(to_date('01-aug-2008','dd-mon-yyyy')) in share mode;
SQL> commit;
Check to see if the partition for August 2008 is created
SQL> select table_name, partition_name, high_value from user_tab_partitions
where table_name = 'invoices'
order by table_name, partition_name;
TABLE_NAME | PARTITION_NAME | HIGH_VALUE |
| INVOICES | P_FIRST | TO_DATE( 2008-01-01 00:00:00, 'SYYYY-MM-DD HH24:MI:SS') |
| INVOICES | SYS_P4008 | TO_DATE(' 2008-02-01 0:00:00', 'SYYYY-M M-DD HH24:MI:SS') |
| INVOICES | SYS_P4009 | TO_DATE(' 2008-03-01 00:00:00', 'SYYYY-M M-DD HH24:MI:SS' |
| INVOICES | SYS_P4010 | TO_DATE(' 2008-08-01 00:00:00', 'SYYYY-M M-DD HH24:MI:SS' |
| INVOICES | SYS_P4013 | TO_DATE(' 2008-09-01 00:00:00', 'SYYYY-M M-DD HH24:MI:SS' |
The last partition ‘SYS_P4013’ is the partition creating using the lock command.
Exchange the data for August 2008 from the staging table "INVOICES_STAGING" to the main table "INVOICES" using the following alter command.
SQL>alter table invoices
exchange partition for (to_date('01-AUG-2008','dd-mon-yyyy'))
with table invoices_staging
excluding indexes
without validation;
The data for the month of August 2008 is exchanged onto the main ‘INVOICES’ table we created earlier which is interval partitioned by month
SQL> select t.invoice_no,t.invoice_date from invoices t;
| INVOICE_NO | INVOICE_DATE |
| 1 | 16-JAN-08 |
| 1 | 16-FEB -08 |
| 3 | 16-JUL-08 |
| 5 | 18-AUG-08 |
| 6 | 21-AUG-08 |
| 7 | 31-AUG-09 |
