In effect, this command splits partition p0  into two new partitions s0  and s1 . It also moves the data that was stored in p0  into the new partitions according to the rules embodied in the two PARTITION ... VALUES ...  clauses, so that s0  contains only those records for which YEAR(dob)  is less than 1960 and s1  contains those rows in which YEAR(dob)  is greater than or equal to 1960 but less than 1970. 

A REORGANIZE PARTITION  clause may also be used for merging adjacent partitions. You can reverse the effect of the previous statement on the members  table as shown here: 

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO ( PARTITION p0 VALUES LESS THAN (1970) ); 

No data is lost in splitting or merging partitions using REORGANIZE PARTITION . In executing the above statement, MySQL moves all of the records that were stored in partitions s0  and s1  into partition p0 . 

The general syntax for REORGANIZE PARTITION  is shown here: 

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO ( partition_definitions ); 

Here, tbl_name  is the name of the partitioned table, and partition_list  is a comma-separated list of names of one or more existing partitions to be changed. partition_definitions is a comma-separated list of new partition definitions, which follow the same rules as for the partition_definitions  list used in CREATE TABLE . You are not limited to merging several partitions into one, or to splitting one partition into many, when using REORGANIZE PARTITION . For example, you can reorganize all four partitions of the members  table into two, like this: 

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO ( PARTITION m0 VALUES LESS THAN (1980), PARTITION m1 VALUES LESS THAN (2000) ); 

You can also use REORGANIZE PARTITION  with tables that are partitioned by LIST . Let us return to the problem of adding a new partition to the list-partitioned tt  table and failing because the new partition had a value that was already present in the value-list of one of the existing partitions. We can handle this by adding a partition that contains only nonconflicting values, and then reorganizing the new partition and the existing one so that the value which was stored in the existing one is now moved to the new one: 

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8)); ALTER TABLE tt REORGANIZE PARTITION p1,np INTO ( PARTITION p1 VALUES IN (6, 18), PARTITION np VALUES in (4, 8, 12) ); 

Here are some key points to keep in mind when using ALTER TABLE ... REORGANIZE PARTITION  to repartition tables that are partitioned by RANGE  or LIST : 

•The PARTITION  options used to determine the new partitioning scheme are subject to the same rules as those used with a CREATE TABLE  statement. 

A new RANGE  partitioning scheme cannot have any overlapping ranges; a new LIST  partitioning scheme cannot have any overlapping sets of values. 

•The combination of partitions in the partition_definitions  list should account for the same range or set of values overall as the combined partitions named in the partition_list . 

For example, partitions p1  and p2  together cover the years 1980 through 1999 in the members  table used as an example in this section. Any reorganization of these two partitions should cover the same range of years overall. 

