Range 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>

REORGANIZE PARTITION

<alter partition name comma list>

INTO ( <partition definition comma list> )

 

partition definition comma list:

PARTITION <partition_name> VALUES LESS THAN ( <range_value> ),.... 

Example 1

The following example shows how to perform repartitioning the before_2000 partition into the before_1996 and before_2000 partitions.

CREATE TABLE participant2 ( host_year INT, nation CHAR(3), gold INT, silver INT, bronze INT)

PARTITION BY RANGE (host_year)

( PARTITION before_2000 VALUES LESS THAN (2000),

PARTITION before_2008 VALUES LESS THAN (2008) );

 

ALTER TABLE participant2 REORGANIZE PARTITION before_2000 INTO (

PARTITION before_1996 VALUES LESS THAN (1996),

PARTITION before_2000 VALUES LESS THAN (2000)

);

Example 2

The following example shows how to combine two partitions redefined in Example 1 back into a single before_2000 partition.

ALTER TABLE participant2 REORGANIZE PARTITION before_1996, before_2000 INTO

(PARTITION before_2000 VALUES LESS THAN (2000) );

Caution