Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 | 

Redefining Range Partitions

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> ),.... 

  • table_name : Specifies the name of the table to be redefined.
  • alter partition name comma list : Specifies the partition to be redefined. Multiple partitions are separated by commas (,).
  • partition definition comma list : Specifies the redefined partitions. Multiple partitions are separated by commas (,).
Example
  • Example 1
  • The following is an example of 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 is an example of combining 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
  • When redefining a range or list partition, duplicate ranges or values are not allowed.
  • The REORGANIZE PARTITION clause cannot be used to change the partition table type. For example, a range partition cannot be changed to a hash partition, or vice versa.
  • The maximum number of partitions cannot exceed 1,024. There must be at least one partition remaining after deleting partitions. In a range-partitioned table, only adjacent partitions can be redefined.