Understanding INTERVAL PARTITION In Oracle Database.

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.