Example - PARTITION BY LIST v11
The following 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 | FOR VALUES IN ('FRANCE', 'ITALY') ASIA | FOR VALUES IN ('INDIA', 'PAKISTAN') AMERICAS | FOR VALUES IN ('US', 'CANADA') (3 rows)
- Rows with a value of
FRANCE
orITALY
in thecountry
column are stored in theeurope
partition. - Rows with a value of
INDIA
orPAKISTAN
in thecountry
column are stored in theasia
partition. - Rows with a value of
US
orCANADA
in thecountry
column are stored in theamericas
partition.
The server would evaluate the following statement against the partitioning rules, and store the row in the europe
partition.
INSERT INTO sales VALUES (10, '9519a', 'FRANCE', '18-Aug-2012', '650000');