Use list partition when you already have a predefined set of values for which you are expecting the data.You should use list partitioning when you want to specifically map rows to partitions based on discrete values.
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 LIST(region)
10 (
11 PARTITION P_AP VALUES('AP'),
12 PARTITION P_EMEA VALUES('EMEA'),
13 PARTITION P_NA VALUES('NA')
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,SYSDATE,'AP');
1 row created.
SQL> INSERT INTO transaction_demo VALUES(2,'TEST2',200,SYSDATE,'EMEA');
1 row created.
SQL> INSERT INTO transaction_demo VALUES(3,'TEST3',300,SYSDATE,'NA');
1 row created.
SQL> INSERT INTO transaction_demo VALUES(4,'TEST4',400,SYSDATE,'IND');
INSERT INTO transaction_demo VALUES(4,'TEST4',400,SYSDATE,'IND')
*
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 DEFAULT 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 LIST(region)
10 (
11 PARTITION P_AP VALUES('AP'),
12 PARTITION P_EMEA VALUES('EMEA'),
13 PARTITION P_NA VALUES('NA'),
14 PARTITION P_REGION_UNKNOWN VALUES(DEFAULT)
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,SYSDATE,'AP');
1 row created.
SQL> INSERT INTO transaction_demo VALUES(2,'TEST2',200,SYSDATE,'EMEA');
1 row created.
SQL> INSERT INTO transaction_demo VALUES(3,'TEST3',300,SYSDATE,'NA');
1 row created.
SQL> INSERT INTO transaction_demo VALUES(4,'TEST4',400,SYSDATE,'IND');
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 27-OCT-21 AP
2 TEST2 200 27-OCT-21 EMEA
3 TEST3 300 27-OCT-21 NA
4 TEST4 400 27-OCT-21 IND
SQL> SELECT * FROM transaction_demo PARTITION (P_AP);
TRANSACTION_ID TRANSACTION_TYPE AMOUNT TRAN_DATE REGION
-------------- ---------------- ------ --------- ------
1 TEST1 100 02-MAY-20 AP
SQL> SELECT * FROM transaction_demo PARTITION (P_EMEA);
TRANSACTION_ID TRANSACTION_TYPE AMOUNT TRAN_DATE REGION
-------------- ----------------- ------ --------- ------
2 TEST2 200 02-MAY-20 EMEA
SQL> SELECT * FROM transaction_demo PARTITION (P_NA);
TRANSACTION_ID TRANSACTION_TYPE AMOUNT TRAN_DATE REGION
-------------- ---------------- ------ --------- ------
3 TEST3 300 02-MAY-20 NA
SQL> SELECT * FROM transaction_demo PARTITION (P_REGION_UNKNOWN);
TRANSACTION_ID TRANSACTION_TYPE AMOUNT TRAN_DATE REGION
-------------- ---------------- ------ --------- ------
4 TEST4 400 02-MAY-20 IND