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, Adaptive Server 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. Sybase 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))
