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

Live SQL

View and run a related example on Oracle Live SQL at Oracle Live SQL: List Partitioning Example.

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 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'));