Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

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>

,

...

);

  • partition_expression : Specifies a partition expression. The expression can be specified by the name of the column to be partitioned or by a function. For more information on the data types and functions available, see Data Types Available for Partition Expression.
  • partition_name : Specifies the partition name.
  • partition_value_list : Specifies the list of the partition by values.
Example 1

The following is an example of creating the athlete2 table with athlete names and sport events, and defining 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 is an example of inserting 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 is an example where 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.