Understanding RANGE PARTITION In Oracle Database.

Use RANGE PARTITION when you want to access data on a frequent basis in a large table based on some range (dates or numbers).Use RANGE PARTITION when you have to maintain a rolling window of data.

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   (
 11    PARTITION P_2015 VALUES LESS THAN (TO_DATE('2016-01-01','YYYY-MM-DD')),
 12    PARTITION P_2016 VALUES LESS THAN (TO_DATE('2017-01-01','YYYY-MM-DD')),
 13    PARTITION P_2017 VALUES LESS THAN (TO_DATE('2018-01-01','YYYY-MM-DD'))
 14   );

Table created.

SQL> SELECT segment_name,blocks,bytes/1024/1024 MB FROM user_segments WHERE segment_name = 'TRANSACTION_DEMO';

no rows selected

SQL> INSERT INTO transaction_demo VALUES(1,'TEST1',100,TO_DATE('2015-01-31','YYYY-MM-DD'),'AP');

1 row created.

SQL> INSERT INTO transaction_demo VALUES(2,'TEST2',200,TO_DATE('2016-01-31','YYYY-MM-DD'),'NA');

1 row created.

SQL> INSERT INTO transaction_demo VALUES(3,'TEST3',300,TO_DATE('2017-01-31','YYYY-MM-DD'),'EMEA');

1 row created.

SQL> INSERT INTO transaction_demo VALUES(4,'TEST4',400,TO_DATE('2018-01-31','YYYY-MM-DD'),'AP');
INSERT INTO transaction_demo VALUES(4,'TEST4',400,TO_DATE('2018-01-31','YYYY-MM-DD'),'AP')
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

SQL> COMMIT;

Commit complete.
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

SQL> DROP TABLE transaction_demo;

Table dropped.

--Creating MAXVALUE Partition
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   (
 11    PARTITION P_2015 VALUES LESS THAN (TO_DATE('2016-01-01','YYYY-MM-DD')),
 12    PARTITION P_2016 VALUES LESS THAN (TO_DATE('2017-01-01','YYYY-MM-DD')),
 13    PARTITION P_2017 VALUES LESS THAN (TO_DATE('2018-01-01','YYYY-MM-DD')),
 14    PARTITION P_MAX_VALUE VALUES LESS THAN (MAXVALUE)
 15   );

Table created.

SQL> SELECT segment_name,blocks,bytes/1024/1024 MB FROM user_segments WHERE segment_name = 'TRANSACTION_DEMO';

no rows selected

SQL> INSERT INTO transaction_demo VALUES(1,'TEST1',100,TO_DATE('2015-01-31','YYYY-MM-DD'),'AP');

1 row created.

SQL> INSERT INTO transaction_demo VALUES(2,'TEST2',200,TO_DATE('2016-01-31','YYYY-MM-DD'),'NA');

1 row created.

SQL> INSERT INTO transaction_demo VALUES(3,'TEST3',300,TO_DATE('2017-01-31','YYYY-MM-DD'),'EMEA');

1 row created.

SQL> INSERT INTO transaction_demo VALUES(4,'TEST4',400,TO_DATE('2018-01-31','YYYY-MM-DD'),'AP');

1 row created.

SQL> INSERT INTO transaction_demo VALUES(5,'TEST5',500,TO_DATE('2019-01-31','YYYY-MM-DD'),'IND');

1 row created.

SQL> INSERT INTO transaction_demo VALUES(6,'TEST6',600,TO_DATE('2009-01-31','YYYY-MM-DD'),'EMEA');

1 row created.

SQL> COMMIT;

Commit complete.

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

SQL> SELECT * FROM transaction_demo;

TRANSACTION_ID TRANSACTION_TYPE     AMOUNT TRAN_DATE REGION
-------------- -------------------- ------ --------- -------
             1 TEST1                 100   31-JAN-15 AP
             6 TEST6                 600   31-JAN-09 EMEA
             2 TEST2                 200   31-JAN-16 NA
             3 TEST3                 300   31-JAN-17 EMEA
             4 TEST4                 400   31-JAN-18 AP
             5 TEST5                 500   31-JAN-19 IND

6 rows selected.

SQL> SELECT * FROM transaction_demo PARTITION(P_2015);

TRANSACTION_ID TRANSACTION_TYPE     AMOUNT TRAN_DATE REGION
-------------- -------------------- ------ --------- -------
             1 TEST1                 100   31-JAN-15 AP
             6 TEST6                 600   31-JAN-09 EMEA

SQL> SELECT * FROM transaction_demo PARTITION(P_2016);

TRANSACTION_ID TRANSACTION_TYPE     AMOUNT TRAN_DATE REGION
-------------- -------------------- ------ --------- -------
             2 TEST2                 200   31-JAN-16 NA

SQL> SELECT * FROM transaction_demo PARTITION(P_2017);

TRANSACTION_ID TRANSACTION_TYPE     AMOUNT TRAN_DATE REGION
-------------- -------------------- ------ --------- -------
             3 TEST3                 300   31-JAN-17 EMEA

SQL> SELECT * FROM transaction_demo PARTITION(P_MAX_VALUE);

TRANSACTION_ID TRANSACTION_TYPE     AMOUNT TRAN_DATE REGION
-------------- -------------------- ------ --------- -------
             4 TEST4                 400   31-JAN-18 AP
             5 TEST5                 500   31-JAN-19 IND