Split Partitions

Use the alter table ... split partition parameter to redistribute data to two or more partitions.

The syntax is:

alter table table_name
split partition partition_name
into partition_condition_clause

where:

partition_name – the partition you are splitting.
partition_condition_clause – conditions that specify how to split the source partition data. Typically, conditions consist of a numerical range or a data range. The partition conditions should cover all, and only, the data in the source partition.

partition_condition_clause may be on the same segment as the source partition, or on a new segment. If you do not specify destination partition segments, SAP ASE creates the new partitions on the segment on which the source partition resides.

See Reference Manual: Commands.

You must enable select into/bulkcopy to issue alter table ... split partition. By default, alter table ... split partition rebuilds the section of the local or global index on the partitioned table affected by the split operation.

Except for the step that rebuilds the index, alter table ... split partition is not a logged operation. SAP recommends that you perform a database dump after running the alter table ... split partition command.

This example creates the orders table and then splits its partitions to redistribute the data:

create table orders (orderid int, amount float, orderdate datetime) 
partition by range (amount)
( P1 values <= (10000) on seg1,
  P2 values <= (50000) on seg2,
  P3 values <= (100000) on seg3,
  P4 values <= (MAX) on seg4)

create clustered index ind_orderid 
on orders(orderid) local index (i1 on seg1, i2 on seg2, i3 on seg3, i4 on seg4) 

alter table orders
split partition P2 
into 
( P5 values <= (25000) on seg2, 
  P6 values <= (50000) on seg3)

alter table orders
split partition P3 
into 
( P7 values <= (50000) on seg2, 
  P8 values <= (100000) on seg3)

alter table orders
split partition P4
into 
( P9 values <= (200000),
  P10 values <= (MAX))