# Partition Management - Truncating All Data from Specified Partitions

+ **`TRUNCATE PARTITION`** is a **`DDL`** operation
+ Alternative for **`DELETE`** operation

```12
ALTER TABLE <table_name> ~!@TRUNCATE PARTITION@!~ <list of partitions>
```

------------------------------------------------------------------------------
For `RANGE`, `RANGE COLUMNS`, `LIST`, and `LIST COLUMNS` or `HASH` and `KEY` partitioned tables, they can be truncated by 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.57 sec)

tidb> INSERT INTO subscribers VALUES(110, 'Jack','Jonse', 5001, '2023-09-08 17:51:04');
Query OK, 1 row affected (0.01 sec)

tidb> SELECT * FROM subscribers PARTITION(P3);
+--------+-----------+----------+-----------+------------+
| sub_id | firstname | lastname | journalid | joined     |
+--------+-----------+----------+-----------+------------+
| 110    | Jack      | Jonse    | 5001      | 2023-09-08 |
+--------+-----------+----------+-----------+------------+
1 row in set, 1 warning (0.01 sec)

tidb> ALTER TABLE subscribers TRUNCATE PARTITION P3;
Query OK, 0 rows affected (0.52 sec)

tidb> SELECT * FROM subscribers PARTITION(P3);
Empty set, 1 warning (0.01 sec)
tidb> CREATE TABLE users (
 -> uid INT NOT NULL,
 -> uname VARCHAR(25) NOT NULL,
 -> ucity VARCHAR(25) NOT NULL,
 -> gid INT NOT NULL,
 -> ulogin DATE NOT NULL DEFAULT CURRENT_DATE()
 -> )
 -> PARTITION BY KEY(uid)
 -> PARTITIONS 4;
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                  |
+------------+------------------------------+
1 row in set (0.01 sec)

tidb> INSERT INTO users VALUES(1, 'Jack','Columbus', 7, '2023-12-10 14:10:22');
Query OK, 1 row affected (0.01 sec)

tidb> INSERT INTO users VALUES(2, 'William','Charlotte', 141, '2023-11-05 09:11:12');
Query OK, 1 row affected (0.01 sec)

tidb> INSERT INTO users VALUES(3, 'Percy','New York', 20, '2024-10-17 16:16:10');
Query OK, 1 row affected (0.01 sec)

tidb> INSERT INTO users VALUES(4, 'Cecil','New York', 22, '2023-05-11 08:23:05');
Query OK, 1 row affected (0.01 sec)

tidb> SELECT count(*) FROM users PARTITION(p0);
+----------+
| count(*) |
+----------+
| 1        |
+----------+
1 row in set, 1 warning (0.00 sec)

tidb> SELECT count(*) FROM users PARTITION(p1);
+----------+
| count(*) |
+----------+
| 1        |
+----------+
1 row in set, 1 warning (0.00 sec)

tidb> SELECT count(*) FROM users PARTITION(p2);
+----------+
| count(*) |
+----------+
| 0        |
+----------+
1 row in set, 1 warning (0.01 sec)

tidb> SELECT count(*) FROM users PARTITION(p3);
+----------+
| count(*) |
+----------+
| 2        |
+----------+
1 row in set, 1 warning (0.00 sec)

tidb> ALTER TABLE users TRUNCATE PARTITION p3;
Query OK, 0 rows affected (0.53 sec)

tidb> SELECT count(*) FROM users PARTITION(p0);
+----------+
| count(*) |
+----------+
| 1        |
+----------+
1 row in set, 1 warning (0.01 sec)

tidb> SELECT count(*) FROM users PARTITION(p1);
+----------+
| count(*) |
+----------+
| 1        |
+----------+
1 row in set, 1 warning (0.00 sec)

tidb> SELECT count(*) FROM users PARTITION(p2);
+----------+
| count(*) |
+----------+
| 0        |
+----------+
1 row in set, 1 warning (0.00 sec)

tidb> SELECT count(*) FROM users PARTITION(p3);
+----------+
| count(*) |
+----------+
| 0        |
+----------+
1 row in set, 1 warning (0.01 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)
```