Altering Regular Table into Partitioning Table

Description

To alter a regular table into a partitioned one, use the ALTER TABLE statement. Three partitioning methods can be used with the ALTER TABLE statement. The data in the existing table are moved to and stored in each partition according to the partition definition.

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 Expressions.

partition_name : Specifies the name of the partition.

partition_value_option : Specifies the value or the value list on which the partition is based.

Example

The following are examples of altering the record table into a range, list and hash table respectively.

ALTER TABLE record PARTITION BY RANGE (host_year)

( PARTITION before_1996 VALUES LESS THAN (1996),

PARTITION after_1996 VALUES LESS THAN MAXVALUE);

ALTER TABLE record PARTITION BY list (unit)

( PARTITION time_record VALUES IN ('Time'),

PARTITION kg_record VALUES IN ('kg'),

PARTITION meter_record VALUES IN ('Meter'),

PARTITION score_record VALUES IN ('Score') );

ALTER TABLE record

PARTITION BY HASH (score) PARTITIONS 4;

Caution

If there is data that does not satisfy the partition condition, partitions cannot be defined.