Interval partitioning method is introduced in Oracle 11g. This is an extension to range partitioning where Oracle automatically creates a partition when the inserted value exceeds all other partition ranges.
The following restrictions apply :-
• You can only specify one partitioning key column, and it must be of NUMBER or DATE type.
• Interval partitioning is NOT supported for index-organized tables.
SQL> CREATE TABLE transaction_demo
2 (
3 transaction_id INT,
4 transaction_type VARCHAR2(10),
5 amount INT,
6 tran_date DATE,
7 region VARCHAR2(4)
8 )
9 PARTITION BY RANGE(tran_date)
10 INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
11 (
12 PARTITION P_JAN_2018 VALUES LESS THAN (TO_DATE('2018-02-01','YYYY-MM-DD')),
13 PARTITION P_FEB_2018 VALUES LESS THAN (TO_DATE('2018-03-01','YYYY-MM-DD')),
14 PARTITION P_MAR_2018 VALUES LESS THAN (TO_DATE('2018-04-01','YYYY-MM-DD'))
15 );
Table created.
SQL> SELECT segment_name,blocks,bytes/1024/1024 MB FROM user_segments WHERE segment_name = 'TRANSACTION_DEMO';
no rows selected
SQL> SELECT table_name,partition_name,num_rows FROM user_tab_partitions WHERE table_name = 'TRANSACTION_DEMO';
TABLE_NAME PARTITION_NAME NUM_ROWS
---------------- -------------- ---------
TRANSACTION_DEMO P_FEB_2018
TRANSACTION_DEMO P_JAN_2018
TRANSACTION_DEMO P_MAR_2018
SQL> INSERT INTO transaction_demo VALUES(1,'TEST1',100,TO_DATE('2018-01-01','YYYY-MM-DD'),'AP');
1 row created.
SQL> INSERT INTO transaction_demo VALUES(2,'TEST2',200,TO_DATE('2018-02-25','YYYY-MM-DD'),'NA');
1 row created.
SQL> INSERT INTO transaction_demo VALUES(3,'TEST3',300,TO_DATE('2018-03-31','YYYY-MM-DD'),'EMEA');
1 row created.
SQL> INSERT INTO transaction_demo VALUES(4,'TEST4',400,TO_DATE('2018-04-20','YYYY-MM-DD'),'AP');
1 row created.
SQL> INSERT INTO transaction_demo VALUES(5,'TEST5',500,TO_DATE('2018-05-29','YYYY-MM-DD'),'AP');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> EXEC dbms_stats.gather_table_stats('COURSE','TRANSACTION_DEMO');
PL/SQL procedure successfully completed.
SQL> SELECT segment_name,blocks,bytes/1024/1024 MB FROM user_segments WHERE segment_name = 'TRANSACTION_DEMO';
SEGMENT_NAME BLOCKS MB
------------------------ ----------
TRANSACTION_DEMO 1024 8
TRANSACTION_DEMO 1024 8
TRANSACTION_DEMO 1024 8
TRANSACTION_DEMO 1024 8
TRANSACTION_DEMO 1024 8
SQL> SELECT table_name,partition_name,num_rows FROM user_tab_partitions WHERE table_name = 'TRANSACTION_DEMO';
TABLE_NAME PARTITION_NAME NUM_ROWS
---------------- -------------- ---------
TRANSACTION_DEMO P_FEB_2018 1
TRANSACTION_DEMO P_JAN_2018 1
TRANSACTION_DEMO P_MAR_2018 1
TRANSACTION_DEMO SYS_P1174 1
TRANSACTION_DEMO SYS_P1175 1
SQL> SELECT * FROM transaction_demo;
TRANSACTION_ID TRANSACTION_TYPE AMOUNT TRAN_DATE REGION
-------------- -------------------- ------ --------- -------
1 TEST1 100 01-JAN-18 AP
2 TEST2 200 25-FEB-18 NA
3 TEST3 300 31-MAR-18 EMEA
4 TEST4 400 20-APR-18 AP
5 TEST5 500 29-MAY-18 AP
SQL> SELECT * FROM transaction_demo PARTITION (P_JAN_2018);
TRANSACTION_ID TRANSACTION_TYPE AMOUNT TRAN_DATE REGION
-------------- -------------------- ------ --------- -------
1 TEST1 100 01-JAN-18 AP
SQL> SELECT * FROM transaction_demo PARTITION (P_FEB_2018);
TRANSACTION_ID TRANSACTION_TYPE AMOUNT TRAN_DATE REGION
-------------- -------------------- ------ --------- -------
2 TEST2 200 25-FEB-18 NA
SQL> SELECT * FROM transaction_demo PARTITION (P_MAR_2018);
TRANSACTION_ID TRANSACTION_TYPE AMOUNT TRAN_DATE REGION
-------------- -------------------- ------ --------- -------
3 TEST3 300 31-MAR-18 EMEA
SQL> SELECT * FROM transaction_demo PARTITION (SYS_P1174);
TRANSACTION_ID TRANSACTION_TYPE AMOUNT TRAN_DATE REGION
-------------- -------------------- ------ --------- -------
4 TEST4 400 20-APR-18 AP
SQL> SELECT * FROM transaction_demo PARTITION (SYS_P1175);
TRANSACTION_ID TRANSACTION_TYPE AMOUNT TRAN_DATE REGION
-------------- -------------------- ------ --------- -------
5 TEST5 500 29-MAY-18 AP
Here it is clearly visible that Oracle didn’t throw any error message – ORA-14400: inserted partition key does not map to any partition for INTERVAL partition.
