List partitioning provides the ability to specify in which partition a row will reside, based on discrete lists of values. It is often useful to be able to partition by some code, such as a state or region code. For example, we might want to pull together in a single partition all records for people in the states of Maine (ME), New Hampshire (NH), Vermont (VT), and Massachusetts (MA), since those states are located next to or near each other, and our application queries data by geographic region. Similarly, we might want to group together Connecticut (CT), Rhode Island (RI), and New York (NY).

We can’t use a range partition, since the range for the first partition would be ME through VT, and the second range would be CT through RI. Those ranges overlap. We can’t use hash partitioning since we can’t control which partition any given row goes into; the built-in hash function provided by Oracle does that. With list partitioning, we can accomplish this custom partitioning scheme easily:
$ sqlplus eoda/foo@PDB1
SQL> create table list_example( state_cd varchar2(2),data varchar2(20))partition by list(state_cd)(partition part_1 values ( ‘ME’, ‘NH’, ‘VT’, ‘MA’ ), partition part_2 values ( ‘CT’, ‘RI’, ‘NY’ )
);Table created.

Figure 13-3 shows that Oracle will inspect the STATE_CD column and, based on its value, place the row into the correct partition.

Figure 13-3.  List partition insert example

As we saw for range partitioning, if we try to insert a value that isn’t specified in the list partition, Oracle will raise an appropriate error back to the client application. In other words, a list partitioned table without a DEFAULT partition will implicitly impose a constraint much like a check constraint on the table:
SQL> insert into list_example values ( ‘VA’, ‘data’ ); insert into list_example values ( ‘VA’, ‘data’ )
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

If we want to segregate these seven states into their separate partitions, as we have, but have all remaining state codes (or, in fact, any other row that happens to be inserted that doesn’t have one of these seven codes) go into a third partition, then we can use the VALUES ( DEFAULT ) clause. Here, we’ll alter the table to add this partition (we could use this in the CREATE TABLE statement as well):
SQL1> alter table list_example add partition part_3 values ( DEFAULT ); Table altered.
SQL> insert into list_example values ( ‘VA’, ‘data’ ); 1 row created.

All values that are not explicitly in our list of values will go here. A word of caution on the use of DEFAULT: Once a list partitioned table has a DEFAULT partition, you cannot add any more partitions to it, so
SQL> alter table list_example add partition part_4 values( ‘CA’, ‘NM’ ); alter table list_example
ERROR at line 1:
ORA-14323: cannot add partition when DEFAULT partition exists

We would have to remove the DEFAULT partition, then add PART_4, and then put the DEFAULT partition back. The reason behind this is that the DEFAULT partition could have had rows with the list partition key value of CA or NM—they would not belong in the DEFAULT partition after adding PART_4.