# `VERSION::7.1.0` Partition Management - Reorganizing Partitions

+ Merge partitions, split a partition, and change the partition definitions
+ Available for **`RANGE`**, **`RANGE COLUMNS`**, **`LIST`**, and **`LIST COLUMNS`** partitioned tables only

```
ALTER TABLE <table_name>
~!@REORGANIZE PARTITION@!~ <list of partitions>
~!@INTO@!~ (<new partition definitions>)
```

------------------------------------------------------------------------------
Only `RANGE`, `RANGE COLUMNS`, `LIST`, and `LIST COLUMNS` partitioned tables support reorganizing partitions operations, for example:

```
tidb> CREATE TABLE subscribers (
 -> sub_id INT NOT NULL,
 -> firstname VARCHAR(25) NOT NULL,
 -> lastname VARCHAR(25) NOT NULL,
 -> journalid INT NOT NULL,
 -> joined DATE NOT NULL
 -> )
 -> PARTITION BY RANGE( YEAR(joined) ) (
 -> PARTITION p0 VALUES LESS THAN (2000),
 -> PARTITION p1 VALUES LESS THAN (2010),
 -> PARTITION p2 VALUES LESS THAN (2020),
 -> PARTITION p3 VALUES LESS THAN (2030)
 -> );
Query OK, 0 rows affected (0.54 sec)

tidb> SELECT PARTITION_NAME, PARTITION_DESCRIPTION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='subscribers';
+----------------+-----------------------+
| PARTITION_NAME | PARTITION_DESCRIPTION |
+----------------+-----------------------+
| p0             | 2000                  |
| p1             | 2010                  |
| p2             | 2020                  |
| p3             | 2030                  |
+----------------+-----------------------+
4 rows in set (0.01 sec)
```

Merge partitions:
```
tidb> ALTER TABLE subscribers REORGANIZE PARTITION p1,p2 INTO (PARTITION p12 VALUES LESS THAN (2020));
Query OK, 0 rows affected, 1 warning (1.03 sec)

tidb> SELECT PARTITION_NAME, PARTITION_DESCRIPTION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='subscribers';
+----------------+-----------------------+
| PARTITION_NAME | PARTITION_DESCRIPTION |
+----------------+-----------------------+
| p0 | 2000 |
| p12 | 2020 |
| p3 | 2030 |
+----------------+-----------------------+
3 rows in set (0.01 sec)
```

Split a partition:
```
tidb> ALTER TABLE subscribers REORGANIZE PARTITION p12 INTO (PARTITION p1 VALUES LESS THAN (2010), PARTITION p2 VALUES LESS THAN (2020));
Query OK, 0 rows affected, 1 warning (2.03 sec)

tidb> SELECT PARTITION_NAME, PARTITION_DESCRIPTION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='subscribers';
+----------------+-----------------------+
| PARTITION_NAME | PARTITION_DESCRIPTION |
+----------------+-----------------------+
| p0             | 2000                  |
| p1             | 2010                  |
| p2             | 2020                  |
| p3             | 2030                  |
+----------------+-----------------------+
4 rows in set (0.01 sec)
```

Change the partitioning definition:
```
tidb> ALTER TABLE subscribers REORGANIZE PARTITION p0, p1, p2, p3 INTO
 -> (PARTITION p1 VALUES LESS THAN (2000),
 -> PARTITION p2 VALUES LESS THAN (2005),
 -> PARTITION p3 VALUES LESS THAN (2010),
 -> PARTITION p4 VALUES LESS THAN (2015),
 -> PARTITION p5 VALUES LESS THAN (2020),
 -> PARTITION p6 VALUES LESS THAN (2025),
 -> PARTITION p7 VALUES LESS THAN (2030),
 -> PARTITION p8 VALUES LESS THAN (MAXVALUE)
 -> );
Query OK, 0 rows affected, 1 warning (6.03 sec)

tidb> SELECT PARTITION_NAME, PARTITION_DESCRIPTION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='subscribers';
+----------------+-----------------------+
| PARTITION_NAME | PARTITION_DESCRIPTION |
+----------------+-----------------------+
| p1             | 2000                  |
| p2             | 2005                  |
| p3             | 2010                  |
| p4             | 2015                  |
| p5             | 2020                  |
| p6             | 2025                  |
| p7             | 2030                  |
| p8             | MAXVALUE              |
+----------------+-----------------------+
8 rows in set (0.02 sec)
```

Reorganizing partitions (including merging or splitting partitions) can change the specified partitions into a new set of partition definitions but cannot change the type of partitioning (for example, change the `LIST` type to the `RANGE` type, or change the `RANGE COLUMNS` type to the `RANGE` type).

For a `RANGE` partition table, you can reorganize adjacent partitions only, for example:
```
tidb> CREATE TABLE subscribers (
 -> sub_id INT NOT NULL,
 -> firstname VARCHAR(25) NOT NULL,
 -> lastname VARCHAR(25) NOT NULL,
 -> journalid INT NOT NULL,
 -> joined DATE NOT NULL
 -> )
 -> PARTITION BY RANGE( YEAR(joined) ) (
 -> PARTITION p0 VALUES LESS THAN (2000),
 -> PARTITION p1 VALUES LESS THAN (2010),
 -> PARTITION p2 VALUES LESS THAN (2020),
 -> PARTITION p3 VALUES LESS THAN (2030)
 -> );
Query OK, 0 rows affected (0.53 sec)

tidb> SELECT PARTITION_NAME, PARTITION_DESCRIPTION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='subscribers';
+----------------+-----------------------+
| PARTITION_NAME | PARTITION_DESCRIPTION |
+----------------+-----------------------+
| p0             | 2000                  |
| p1             | 2010                  |
| p2             | 2020                  |
| p3             | 2030                  |
+----------------+-----------------------+
4 rows in set (0.01 sec)

tidb> ALTER TABLE subscribers REORGANIZE PARTITION p0,p2 INTO (PARTITION p02 VALUES LESS THAN (2020));
ERROR 8200 (HY000): Unsupported REORGANIZE PARTITION of RANGE; not adjacent partitions
```

For a `LIST` partitioned table, to modify the set of values defined for a partition, the new definition must cover the existing values in that partition. Otherwise, an error is reported:
```
tidb> CREATE TABLE subscribers (
 -> sub_id INT NOT NULL,
 -> firstname VARCHAR(25) NOT NULL,
 -> lastname VARCHAR(25) NOT NULL,
 -> journalid INT NOT NULL,
 -> joined DATE NOT NULL
 -> )
 -> PARTITION BY LIST ( journalid ) (
 -> PARTITION p0 VALUES IN (5001, 5002),
 -> PARTITION p1 VALUES IN (6001, 6002),
 -> PARTITION p2 VALUES IN (7001, 7002),
 -> PARTITION p3 VALUES IN (8001, 8002),
 -> PARTITION p4 VALUES IN (9001, 9002)
 -> );
Query OK, 0 rows affected (0.53 sec)

tidb> SELECT PARTITION_NAME, PARTITION_DESCRIPTION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='subscribers';
+----------------+-----------------------+
| PARTITION_NAME | PARTITION_DESCRIPTION |
+----------------+-----------------------+
| p0             | 5001,5002             |
| p1             | 6001,6002             |
| p2             | 7001,7002             |
| p3             | 8001,8002             |
| p4             | 9001,9002             |
+----------------+-----------------------+
5 rows in set (0.01 sec)

tidb> ALTER TABLE subscribers REORGANIZE PARTITION p1 INTO (PARTITION p1 VALUES IN (6001, 6003));
Query OK, 0 rows affected, 1 warning (1.03 sec)

tidb> INSERT INTO subscribers VALUES(1, 'Utada', 'Hikaru', 6003, Now());
Query OK, 1 row affected (0.01 sec)

tidb> ALTER TABLE subscribers REORGANIZE PARTITION p1 INTO (PARTITION p1 VALUES IN (6001, 6002));
ERROR 1526 (HY000): Table has no partition for value 6003
```

After reorganizing partitions, the statistics of the respective partitions become outdated, causing you to receive the following warning. In this scenario, you can use the `ANALYZE TABLE` statement to refresh the statistics.