Change Data Partitions

The alter table alters data partitions.

You can use the alter table command to:

Change an unpartitioned table to a multipartitioned table
Split, merge, or move partitions
Add one or more partitions to a list- or range-partitioned tables
Repartition a table for a different partitioning type
Repartition a table for a different partitioning key or bound
Repartition a table for a different number of partitions
Repartition a table to assign partitions to different segments

The general procedures for repartitioning a table are:

If the partition key or type is to change during the repartition process, drop all indexes on the table.
Use alter table to repartition the table.
If the partition key or type changed during the repartition process, re-create the indexes on the table.

Change an Unpartitioned Table to a Partitioned Table

An example of an unpartitioned titles table changing to a table with three range partitions.

alter table titles partition by range (total_sales)
(smallsales values <= (500) on seg1,
mediumsales values <= (5000) on seg2,
bigsales values <= (25000) on seg3)

Split, Merge, and Move Partitions
Over time, a partition’s data distribution might become skewed, or the manner in which the data was originally partitioned may no longer suit the current business requirements. Use alter table to merge, split, or move partitions to redistribute the data and revive performance benefits using partitions.
Add Partitions to a Partitioned Table
You can add partitions to list- or range-partitioned tables, but you cannot add partitions to a hash- or round-robin–partitioned table.
Change the Partitioning Type or Key
Before changing the partition type or key, you must drop all indexes.
Unpartition Round-Robin–Partitioned Tables
You can create an unpartitioned round-robin table from a partitioned round-robin table using alter table with the unpartition clause—as long as all partitions are on the same segment, and there are no indexes on the table.
partition Parameter Usage
You can use the partition number_of_partitions parameter to change an unpartitioned round-robin table to a round-robin-partitioned table that has a specified number of partitions.
Change Partition-Key Columns
Certain rules apply when you modify partition-key columns.

Parent topic: Partition Tables and Indexes