Interval partitioning

Samples

• Create a RANGE-partitioned table with one partition.

CREATE TABLE SALES (
    PRODUCT_NAME        VARCHAR2(50),
		TRANSACTION_DATE    DATE,
		SALE_PRICE          NUMBER(10,2),
    AMOUNT_SOLD         NUMBER
)
PARTITION BY RANGE (TRANSACTION_DATE)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
(
    PARTITION P1 values less than (TO_DATE('03-01-2011','MM-DD-YYYY'))
);

Table created

• Table can be created on multiple tablespaces if needed.

CREATE TABLE SALES (
    PRODUCT_NAME        VARCHAR2(50),
		TRANSACTION_DATE    DATE,
		SALE_PRICE          NUMBER(10,2),
    AMOUNT_SOLD         NUMBER
)
PARTITION BY RANGE (TRANSACTION_DATE)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) STORE IN (tbs1, tbs2, tbs3, tbs4) 
(
    PARTITION P1 values less than (TO_DATE('03-01-2011','MM-DD-YYYY'))
);

Table created

• Verify the partition creation.

SQL> column TABLE_NAME format A10
SQL> column PARTITION_NAME format A8

SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions
WHERE table_name='SALES';

TABLE_NAME PARTITIO HIGH_VALUE
---------- -------- --------------------------------------------------------------------------------
SALES      P1       TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
 

• Insert a value within the created partition -> No new partition created.

SQL> insert into SALES values ('XOOM','01-FEB-11', 600, 30000);
 
1 row inserted
 
SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions
  2  WHERE table_name='SALES';
 
TABLE_NAME PARTITIO HIGH_VALUE
---------- -------- --------------------------------------------------------------------------------
SALES      P1       TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'

• Insert a value higher than the threshold of the initial partition. Expected a new partition with new one-month range.

SQL> insert into SALES values ('DROID','15-MAR-11', 20, 50000);
 
1 row inserted
 
SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions
  2  WHERE table_name='SALES';
 
TABLE_NAME PARTITIO HIGH_VALUE
---------- -------- --------------------------------------------------------------------------------
SALES      P1       TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
SALES      SYS_P81  TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
 

• Insert a value couple months in advance. New partition is created. However, there is a gap still UN-MATERIALIZED.

SQL> insert into SALES values ('DROID2','15-JUL-11', 30, 70000);
 
1 row inserted
 
SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions
  2  WHERE table_name='SALES';
 
TABLE_NAME PARTITIO HIGH_VALUE
---------- -------- --------------------------------------------------------------------------------
SALES      P1       TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
SALES      SYS_P81  TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
SALES      SYS_P82  TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'

• Insert a value in between that un-materialized gap.

 
SQL> insert into SALES values ('DROID PRO','15-JUN-11', 300, 65000);
 
1 row inserted
 
SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions
  2  WHERE table_name='SALES';
 
TABLE_NAME PARTITIO HIGH_VALUE
---------- -------- --------------------------------------------------------------------------------
SALES      P1       TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
SALES      SYS_P81  TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
SALES      SYS_P83  TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
SALES      SYS_P82  TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'

Can I change the interval after partitions are created already?

-- To disable the INTERVAL partition
SQL> alter table SALES set interval ();
 
Table altered.
 
-- To enable the INTERVAL partition
SQL> alter table SALES set interval (NUMTOYMINTERVAL(1,'MONTH'));
 
Table altered.

-- To switch it back to the range partition
SQl> alter table SALES set interval();

Table altered.

• Check whether or not the table is using the INTERVAL partition.

SQL> SELECT table_name, INTERVAL FROM user_part_tables
where table_name = 'SALES';

TABLE_NAME          INTERVAL                
----------------    -------------------------------                                              
SALES               NUMTOYMINTERVAL(1,'MONTH')