Creating a List-Partitioned Table

Example 4-6 creates a list-partitioned table. It creates table q1_sales_by_region which is partitioned by regions consisting of groups of US states. A row is mapped to a partition by checking whether the value of the partitioning column for a row matches a value in the value list that describes the partition. For example, the following list describes how some sample rows are inserted into the table.

(10, 'accounting', 100, 'WA') maps to partition q1_northwest
(20, 'R&D', 150, 'OR') maps to partition q1_northwest
(30, 'sales', 100, 'FL') maps to partition q1_southeast
(40, 'HR', 10, 'TX') maps to partition q1_southwest
(50, 'systems engineering', 10, 'CA') does not map to any partition in the table and raises an error

Example 4-6 Creating a list-partitioned table

CREATE TABLE q1_sales_by_region
      (deptno number, 
       deptname varchar2(20),
       quarterly_sales number(10, 2),
       state varchar2(2))
   PARTITION BY LIST (state)
      (PARTITION q1_northwest VALUES ('OR', 'WA'),
       PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
       PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
       PARTITION q1_southeast VALUES ('FL', 'GA'),
       PARTITION q1_northcentral VALUES ('SD', 'WI'),
       PARTITION q1_southcentral VALUES ('OK', 'TX'));