content_image/815858.jpg
## 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’; 

<img src='content_image/815858.jpg'>

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: 

