You should use list partitioning when you want to specifically map rows to partitions based on discrete values. In Example 3-7, all the customers for states Oregon and Washington are stored in one partition and customers in other states are stored in different partitions. Account managers who analyze their accounts by region can take advantage of partition pruning.
Unlike range and hash partitioning, multi-column partition keys are not supported for list partitioning. If a table is partitioned by list, the partitioning key can only consist of a single column of the table.
Example 3-7 Creating a table with list partitioning
CREATE TABLE accounts ( id NUMBER , account_number NUMBER , customer_id NUMBER , branch_id NUMBER , region VARCHAR(2) , status VARCHAR2(1) ) PARTITION BY LIST (region) ( PARTITION p_northwest VALUES ('OR', 'WA') , PARTITION p_southwest VALUES ('AZ', 'UT', 'NM') , PARTITION p_northeast VALUES ('NY', 'VM', 'NJ') , PARTITION p_southeast VALUES ('FL', 'GA') , PARTITION p_northcentral VALUES ('SD', 'WI') , PARTITION p_southcentral VALUES ('OK', 'TX') );