Understanding LIST PARTITION In Oracle Database.

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