# Partition Management - Dropping Partitions

+ **`RANGE`**, **`RANGE COLUMNS`**, **`LIST`**, and **`LIST COLUMNS`**
```
ALTER TABLE <table_name> DROP PARTITION <list of partitions>
```

+ **`HASH`** and **`KEY`**
```
ALTER TABLE <table_name> COALESCE PARTITION <number of partitions to be dropped>
```

------------------------------------------------------------------------------
For `RANGE`, `RANGE COLUMNS`, `LIST`, and `LIST COLUMNS` partitioned tables, you use `ALTER TABLE <table_name> DROP PARTITION <list of partitions>`, 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 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 DROP PARTITION p1;
Query OK, 0 rows affected (0.52 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,p2,p3 |
+-------------+------------------------------+
1 row in set (0.02 sec)
```

For `HASH` and `KEY` partitioned tables, we use `ALTER TABLE <table_name> COALESCE PARTITION <number of partitions to decrease by>`, this feature is supported since TiDB v7.1.0, for example:

```
tidb> CREATE TABLE users (
 -> uid INT NOT NULL,
 -> uname VARCHAR(25) NOT NULL,
 -> ucity VARCHAR(25) NOT NULL,
 -> addr VARCHAR(25) NOT NULL,
 -> ulogin DATE NOT NULL DEFAULT CURRENT_DATE()
 -> )
 -> PARTITION BY KEY(uid, ucity)
 -> PARTITIONS 6;
Query OK, 0 rows affected (0.54 sec)

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

tidb> ALTER TABLE users COALESCE PARTITION 2;
Query OK, 0 rows affected, 1 warning (2.03 sec)

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

The process of changing the number of partitions for `HASH` or `KEY` partitioned tables reorganizes the partitions by copying all data to the new number of partitions. Therefore, after changing the number of partitions for a `HASH` or `KEY` partitioned table, you will get the warning about the outdated statistics. In this case, you can use the `ANALYZE TABLE` statement to update the statistics.