Exchanging Partitions and Subpartitions with Tables mysql> DELETE FROM e WHERE id < 1000001; Query OK, 1000000 rows affected (5.55 sec) mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ’e’; mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITH VALIDATION; Query OK, 0 rows affected (0.74 sec) mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ’e’; mysql> DELETE FROM e WHERE id < 1000001; Query OK, 1000000 rows affected (5.55 sec) mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ’e’; mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e3 WITHOUT VALIDATION; Query OK, 0 rows affected (0.01 sec) # Confirm that there are no rows in partition p0 2 rows in set (0.00 sec) # Exchange partition p0 of table e with the table e2 ’WITH VALIDATION’ # Confirm that the partition was exchanged with table e2 2 rows in set (0.00 sec) # Once again, drop the rows from p0 of table e # Confirm that there are no rows in partition p0 2 rows in set (0.00 sec) # Exchange partition p0 of table e with the table e3 ’WITHOUT VALIDATION’ # Confirm that the partition was exchanged with table e3 2 rows in set (0.00 sec) mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ’e’; +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 1000000 | | p1 | 1000000 | +----------------+------------+ +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 0 | | p1 | 1000000 | +----------------+------------+ +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 1000000 | | p1 | 1000000 | +----------------+------------+ +----------------+------------+ | PARTITION_NAME | TABLE_ROWS | +----------------+------------+ | p0 | 0 | | p1 | 1000000 | +----------------+------------+ If a partition is exchanged with a table that contains rows that do not match the partition definition, it is the responsibility of the database administrator to fix the non-matching rows, which can be performed using REPAIR TABLE  or  ALTER TABLE ... REPAIR PARTITION . 4699