# `VERSION::7.5.0` Partition Management - Changing the Partition Type

+ Convert an existing non-partitioned table to a partitioned table
+ Modify the partition type of an existing partitioned table
```12
ALTER TABLE <table_name> PARTITION BY <...>
```

+ Convert a partitioned table to a non-partitioned table
```12
ALTER TABLE <table_name> REMOVE PARTITIONING
```

------------------------------------------------------------------------------
To convert a partitioned table to a non-partitioned table, you can use the following statement, which removes the partitioning, copies all rows of the table, and recreates the indexes online for the table:

```
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>INSERT INTO subscribers VALUES(1, 'Jack','Smith', 5001, '1998-12-10 14:10:22');
Query OK, 1 row affected (0.02 sec)

tidb>INSERT INTO subscribers VALUES(2, 'Maverick','Johnson', 5012, '2008-08-08 11:21:48');
Query OK, 1 row affected (0.00 sec)

tidb>INSERT INTO subscribers VALUES(3, 'Kylo','Brown', 6006, '2019-11-09 15:59:02');
Query OK, 1 row affected (0.00 sec)

tidb>INSERT INTO subscribers VALUES(4, 'Emberly','Miller', 6010, '2023-12-10 18:21:44');
Query OK, 1 row affected (0.01 sec)

tidb>SELECT TABLE_NAME, GROUP_CONCAT(PARTITION_NAME) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='subscribers' GROUP BY TABLE_NAME;
+-------------+------------------------------+
| TABLE_NAME  | GROUP_CONCAT(PARTITION_NAME) |
+-------------+------------------------------+
| subscribers | p0,p1,p2,p3                  |
+-------------+------------------------------+
1 row in set (0.01 sec)

tidb>ALTER TABLE subscribers REMOVE PARTITIONING;
Query OK, 0 rows affected (1.03 sec)

tidb>SELECT TABLE_NAME, GROUP_CONCAT(PARTITION_NAME) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='subscribers' GROUP BY TABLE_NAME;
+-------------+------------------------------+
| TABLE_NAME  | GROUP_CONCAT(PARTITION_NAME) |
+-------------+------------------------------+
| subscribers | NULL                         |
+-------------+------------------------------+
1 row in set (0.01 sec)
```

To convert an existing non-partitioned table to a partitioned table, you can use the following statement:

```
tidb>ALTER TABLE subscribers ADD INDEX idx_journalid(journalid);
Query OK, 0 rows affected (3.02 sec)

tidb>ALTER TABLE subscribers 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, 1 warning (3.04 sec)

tidb>SELECT COUNT(*) FROM subscribers;
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set, 1 warning (0.00 sec)

tidb>SHOW CREATE TABLE subscribers\G;
*************************** 1. row ***************************
       Table: subscribers
Create Table: CREATE TABLE `subscribers` (
  `sub_id` int(11) NOT NULL,
  `firstname` varchar(25) NOT NULL,
  `lastname` varchar(25) NOT NULL,
  `journalid` int(11) NOT NULL,
  `joined` date NOT NULL,
  KEY `idx_journalid` (`journalid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
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))
1 row in set (0.00 sec)

tidb>SELECT TABLE_NAME, GROUP_CONCAT(PARTITION_NAME) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='subscribers' GROUP BY TABLE_NAME;
+-------------+------------------------------+
| TABLE_NAME  | GROUP_CONCAT(PARTITION_NAME) |
+-------------+------------------------------+
| subscribers | p0,p1,p2,p3                  |
+-------------+------------------------------+
1 row in set (0.01 sec)
```

To modify the partition type of an existing partitioned table, you can use the following statement:

```
tidb>ALTER TABLE subscribers PARTITION BY HASH(sub_id) PARTITIONS 4;
Query OK, 0 rows affected, 1 warning (2.03 sec)

tidb>SHOW CREATE TABLE subscribers\G;
*************************** 1. row ***************************
       Table: subscribers
Create Table: CREATE TABLE `subscribers` (
  `sub_id` int(11) NOT NULL,
  `firstname` varchar(25) NOT NULL,
  `lastname` varchar(25) NOT NULL,
  `journalid` int(11) NOT NULL,
  `joined` date NOT NULL,
  KEY `idx_journalid` (`journalid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY HASH (`sub_id`) PARTITIONS 4
1 row in set (0.00 sec)

tidb>SELECT TABLE_NAME, GROUP_CONCAT(PARTITION_NAME) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='subscribers' GROUP BY TABLE_NAME;
+-------------+------------------------------+
| TABLE_NAME  | GROUP_CONCAT(PARTITION_NAME) |
+-------------+------------------------------+
| subscribers | p0,p1,p2,p3                  |
+-------------+------------------------------+
1 row in set (0.01 sec)
```