Understanding RANGE PARTITION In Oracle Database.
- Performance Tuning
- October 29, 2024
- 0 Comment
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