Example: PARTITION BY LIST v17

This example creates a partitioned table sales using the PARTITION BY LIST clause. The sales table stores information in three partitions: europe, asia, and americas.

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST(country)
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);

The resulting table is partitioned by the value specified in the country column:

edb=# SELECT partition_name, high_value from ALL_TAB_PARTITIONS;

 partition_name |     high_value
----------------+---------------------
 EUROPE         | 'FRANCE', 'ITALY'
 ASIA           | 'INDIA', 'PAKISTAN'
 AMERICAS       | 'US', 'CANADA'
(3 rows)

• Rows with a value of FRANCE or ITALY in the country column are stored in the europe partition.
• Rows with a value of INDIA or PAKISTAN in the country column are stored in the asia partition.
• Rows with a value of US or CANADA in the country column are stored in the americas partition.

The server evaluates the following statement against the partitioning rules and stores the row in the europe partition:

INSERT INTO sales VALUES (10, '9519a', 'FRANCE', '18-Aug-2012', '650000');