Exchanging Partitions and Subpartitions with Tables Exchanging a Subpartition with a Nonpartitioned Table You can also exchange a subpartition of a subpartitioned table (see Section 24.2.6, “Subpartitioning” ) with a nonpartitioned table using an  ALTER TABLE ... EXCHANGE PARTITION  statement. In the following example, we first create a table  es  that is partitioned by  RANGE  and subpartitioned by  KEY , populate this table as we did table  e , and then create an empty, nonpartitioned copy  es2  of the table, as shown here: mysql>  CREATE TABLE es ( -> id INT NOT NULL, -> fname VARCHAR(30), -> lname VARCHAR(30) ->  ) -> PARTITION BY RANGE (id) -> SUBPARTITION BY KEY (lname) -> SUBPARTITIONS 2 ( -> PARTITION p0 VALUES LESS THAN (50), -> PARTITION p1 VALUES LESS THAN (100), -> PARTITION p2 VALUES LESS THAN (150), -> PARTITION p3 VALUES LESS THAN (MAXVALUE) -> ); Query OK, 0 rows affected (2.76 sec) mysql>  INSERT INTO es VALUES -> (1669, "Jim", "Smith"), -> (337, "Mary", "Jones"), -> (16, "Frank", "White"), -> (2005, "Linda", "Black"); Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql>  CREATE TABLE es2 LIKE es; Query OK, 0 rows affected (1.27 sec) mysql>  ALTER TABLE es2 REMOVE PARTITIONING; Query OK, 0 rows affected (0.70 sec) Records: 0 Duplicates: 0 Warnings: 0 Although we did not explicitly name any of the subpartitions when creating table  es , we can obtain generated names for these by including the  SUBPARTITION_NAME  column of the  PARTITIONS  table from INFORMATION_SCHEMA  when selecting from that table, as shown here: mysql>  SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = ’es’; +----------------+-------------------+------------+ | PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS | +----------------+-------------------+------------+ | p0 | p0sp0 | 1 | | p0 | p0sp1 | 0 | | p1 | p1sp0 | 0 | | p1 | p1sp1 | 0 | | p2 | p2sp0 | 0 | | p2 | p2sp1 | 0 | | p3 | p3sp0 | 3 | | p3 | p3sp1 | 0 | +----------------+-------------------+------------+ 8 rows in set (0.00 sec) The following  ALTER TABLE  statement exchanges subpartition  p3sp0  in table  es  with the nonpartitioned table  es2 : mysql>  ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2; Query OK, 0 rows affected (0.29 sec) You can verify that the rows were exchanged by issuing the following queries: 4700