List Partitioning Definition

Description

You can define a list partition by using the PARTITION BY LIST statement.

Syntax

CREATE TABLE(

...

)

PARTITION BY LIST ( <partition_expression> ) (

PARTITION <partition_name> VALUES IN ( <partition_value_list> ),

PARTITION <partition_name> VALUES IN ( <partition_value_ list>

,

...

);

Example 1

The following example shows how to create the athlete2 table with athlete names and sport events, and define list partitions based on event values.

CREATE TABLE athlete2( name VARCHAR(40), event VARCHAR(30) )

PARTITION BY LIST (event) (

PARTITION event1 VALUES IN ('Swimming', 'Athletics ),

PARTITION event2 VALUES IN ('Judo', 'Taekwondo','Boxing'),

PARTITION event3 VALUES IN ('Football', 'Basketball', 'Baseball')

);

Example 2

The following example shows how to insert data to the list partition created in the example 1. In the last query of the example 2, if you insert an argument that has not been specified in the partition expression of the example 1, data inserting fails.

INSERT INTO athlete2 VALUES ('Hwang Young-Cho', 'Athletics');

INSERT INTO athlete2 VALUES ('Lee Seung-Yuop', 'Baseball');

INSERT INTO athlete2 VALUES ('Moon Dae-Sung','Taekwondo');

INSERT INTO athlete2 VALUES ('Cho In-Chul', 'Judo');

INSERT INTO athlete2 VALUES ('Hong Kil-Dong', 'Volleyball');

Example 3

The following example shows in which an error occurs with no data inserted when the partition key value is NULL. To define a partition where a NULL value can be inserted, define one that has a list including a NULL value as in the event3 partition as below.

INSERT INTO athlete2 VALUES ('Hong Kil-Dong','NULL');

 

CREATE TABLE athlete2( name VARCHAR(40), event VARCHAR(30) )

PARTITION BY LIST (event) (

PARTITION event1 VALUES IN ('Swimming', 'Athletics ' ),

PARTITION event2 VALUES IN ('Judo', 'Taekwondo','Boxing'),

PARTITION event3 VALUES IN ('Football', 'Basketball', 'Baseball', NULL)

);

Caution

The maximum number of partitions cannot exceed 1,024.