List Partitioning Redefinition

Description

You can redefine a partition by using the REORGANIZE PARTITION clause of the ALTER statement. By redefining partitions, you can combine multiple partitions into one or divide one into multiple.

Syntax

ALTER {TABLE | CLASS} <table_name>

REORGANIZEPARTITION

<alter partition name comma list>

INTO ( <partition definition comma list> )

partition definition comma list:

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

Example 1

The following example shows how to create the athlete2 table partitioned by the list of sport events, and redefine the event2 partition to be divided into event2_1 (Judo) and event2_2 (Taekwondo, Boxing).

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

);

 

ALTER TABLE athlete2 REORGANIZE PARTITION event2 INTO

(PARTITION event2_1 VALUES IN ('Judo'),

PARTITION event2_2 VALUES IN ( 'Taekwondo','Boxing'));

Example 2

The following example shows how to combine the event2_1 and event2_2 partitions divided in Example 1 back into a single event2 partition.

ALTER TABLE athlete2 REORGANIZE PARTITION event2_1, event2_2 INTO

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