Author: jstephens
Date: 2010-08-12 14:20:48 +0200 (Thu, 12 Aug 2010)
New Revision: 22204
Log:
Documented WL#4445 (ALTER TABLE ... EXCHANGE PARTITION)
Modified:
trunk/dynamic-docs/changelog/mysqld-2.xml
trunk/refman-5.6/partitioning.xml
trunk/refman-5.6/sql-syntax-data-definition.xml
Modified: trunk/dynamic-docs/changelog/mysqld-2.xml
===================================================================
--- trunk/dynamic-docs/changelog/mysqld-2.xml 2010-08-12 11:26:08 UTC (rev 22203)
+++ trunk/dynamic-docs/changelog/mysqld-2.xml 2010-08-12 12:20:48 UTC (rev 22204)
Changed blocks: 1, Lines Added: 35, Lines Deleted: 0; 1175 bytes
@@ -6,6 +6,41 @@
]>
<changelog>
+ <logentry entrytype="feature">
+
+ <tags>
+ <highlight type="partitioning"/>
+ <manual type="ALTER TABLE"/>
+ <manual type="EXCHANGE PARTITION"/>
+ </tags>
+
+ <bugs>
+ <fixes wlid="4445"/>
+ </bugs>
+
+ <versions>
+ <version ver="5.6.0"/>
+ </versions>
+
+ <message>
+
+ <para>
+ It is now possible to exchange a partition of a partitioned
+ table or a subpartition of a subpartitioned table with a
+ nonpartitioned table that otherwise has the same structure using
+ the <literal role="stmt" condition="alter-table">ALTER TABLE ...
+ EXCHANGE PARTITION</literal> statement.
+ </para>
+
+ <para>
+ For more information and examples, see
+ <xref linkend="partitioning-management-exchange"/>.
+ </para>
+
+ </message>
+
+ </logentry>
+
<logentry entrytype="bug">
<tags>
Modified: trunk/refman-5.6/partitioning.xml
===================================================================
--- trunk/refman-5.6/partitioning.xml 2010-08-12 11:26:08 UTC (rev 22203)
+++ trunk/refman-5.6/partitioning.xml 2010-08-12 12:20:48 UTC (rev 22204)
Changed blocks: 1, Lines Added: 451, Lines Deleted: 0; 17664 bytes
@@ -4573,6 +4573,457 @@
</section>
+ <section id="partitioning-management-exchange">
+
+ <title>Exchanging Partitions and Subpartitions with Tables</title>
+
+ <para>
+ Beginning with MySQL 5.6.0, it is possible to exchange a table
+ partition or subpartition with a table using the
+ <literal role="stmt" condition="alter-table">ALTER TABLE ...
+ EXCHANGE PARTITION</literal> statement—that is, to move
+ any existing rows in the partition or subpartition to the
+ nonpartitioned table, and any existing rows in the
+ nonpartitioned table to the table partition or subpartition.
+ </para>
+
+ <para>
+ Such operations are subject to the following conditions:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The table to be exchanged must not be partitioned, but must
+ otherwise have the same table structure as the partitioned
+ table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Any rows exisitng in the nonpartitioned table prior to the
+ exchange must lie within the range defined for the partition
+ or subpartition.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The complete syntax of the the
+ <literal role="stmt" condition="alter-table">ALTER TABLE ...
+ EXCHANGE PARTITION</literal> statement is shown here, where
+ <replaceable>pt</replaceable> is the partitioned table,
+ <replaceable>p</replaceable> is the partition or subpartition to
+ be exchanged, and <replaceable>t</replaceable> is the
+ nonpartitioned table to be exchanged with
+ <replaceable>p</replaceable>:
+ </para>
+
+<programlisting>
+ALTER TABLE <replaceable>pt</replaceable>
+ EXCHANGE PARTITION <replaceable>p</replaceable>
+ WITH TABLE <replaceable>t</replaceable>;
+</programlisting>
+
+ <para>
+ One and only one partition or subpartition may be exchanged with
+ one and only one nonpartitioned table in a single
+ <literal role="stmt" condition="alter-table">ALTER TABLE
+ EXCHANGE PARTITION</literal> statement. To exchange multiple
+ partitions or subpartitions, use multiple
+ <literal role="stmt" condition="alter-table">ALTER TABLE
+ EXCHANGE PARTITION</literal> statements. <literal>EXCHANGE
+ PARTITION</literal> may not be combined with other
+ <literal role="stmt">ALTER TABLE</literal> options. The
+ partitioning and (if applicable) subpartitioning used by the
+ partitioned table may be of any type or types supported in MySQL
+ ¤t-series;.
+ </para>
+
+ <para>
+ Suppose that a partitioned table <literal>e</literal> has been
+ created and populated using the following SQL statements:
+ </para>
+
+<programlisting>
+CREATE TABLE e (
+ id INT NOT NULL,
+ fname VARCHAR(30),
+ lname VARCHAR(30)
+)
+ PARTITION BY RANGE (id) (
+ PARTITION p0 VALUES LESS THAN (50),
+ PARTITION p1 VALUES LESS THAN (100),
+ PARTITION p2 VALUES LESS THAN (150),
+ PARTITION p3 VALUES LESS THAN (MAXVALUE)
+);
+
+INSERT INTO e VALUES
+ (1669, "Jim", "Smith"),
+ (337, "Mary", "Jones"),
+ (16, "Frank", "White"),
+ (2005, "Linda", "Black");
+</programlisting>
+
+ <para>
+ Now we create a nonpartitioned copy of <literal>e</literal>
+ named <literal>e2</literal>. This can be done using the
+ <command>mysql</command> client as shown here:
+ </para>
+
+<programlisting>
+mysql> <userinput>CREATE TABLE e2 LIKE e;</userinput>
+Query OK, 0 rows affected (1.34 sec)
+
+mysql> <userinput>ALTER TABLE e2 REMOVE PARTITIONING;</userinput>
+Query OK, 0 rows affected (0.90 sec)
+Records: 0 Duplicates: 0 Warnings: 0
+</programlisting>
+
+ <para>
+ You can see which partitions in table <literal>e</literal>
+ contain rows by querying the
+ <literal role="is">INFORMATION_SCHEMA.PARTITIONS</literal>
+ table:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT PARTITION_NAME, TABLE_ROWS</userinput>
+ -> <userinput>FROM INFORMATION_SCHEMA.PARTITIONS</userinput>
+ -> <userinput>WHERE TABLE_NAME = 'e';</userinput>
++----------------+------------+
+| PARTITION_NAME | TABLE_ROWS |
++----------------+------------+
+| p0 | 1 |
+| p1 | 0 |
+| p2 | 0 |
+| p3 | 3 |
++----------------+------------+
+4 rows in set (0.00 sec)
+</programlisting>
+
+ <para>
+ To exchange partition <literal>p0</literal> in table
+ <literal>e</literal> with table <literal>e2</literal>, you can
+ use the <literal role="stmt">ALTER TABLE</literal> statement
+ shown here:
+ </para>
+
+<programlisting>
+mysql> <userinput>ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;</userinput>
+Query OK, 0 rows affected (0.28 sec)
+</programlisting>
+
+ <para>
+ More precisely, the statement just issued causes any rows found
+ in the partition to be swapped with those found in the table.
+ You can observe how this has happened by querying the
+ <literal role="is">INFORMATION_SCHEMA.PARTITIONS</literal>
+ table, as before. The table row that was previously found in
+ partition <literal>p0</literal> is no longer present:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT PARTITION_NAME, TABLE_ROWS</userinput>
+ -> <userinput>FROM INFORMATION_SCHEMA.PARTITIONS</userinput>
+ -> <userinput>WHERE TABLE_NAME = 'e';</userinput>
++----------------+------------+
+| PARTITION_NAME | TABLE_ROWS |
++----------------+------------+
+| p0 | 0 |
+| p1 | 0 |
+| p2 | 0 |
+| p3 | 3 |
++----------------+------------+
+4 rows in set (0.00 sec)
+</programlisting>
+
+ <para>
+ If you query table <literal>e2</literal>, you can see that the
+ <quote>missing</quote> row can now be found there:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT * FROM e2;</userinput>
++----+-------+-------+
+| id | fname | lname |
++----+-------+-------+
+| 16 | Frank | White |
++----+-------+-------+
+1 row in set (0.00 sec)
+</programlisting>
+
+ <para>
+ The table to be exchanged with the partition does not
+ necessarily have to be empty. To demonstrate this, we first
+ insert a new row into table <literal>e</literal>, making sure
+ that this row is stored in partition <literal>p0</literal> by
+ choosing an <literal>id</literal> column value that is less than
+ 50, and verifying this afterwards by querying the
+ <literal role="is">PARTITIONS</literal> table:
+ </para>
+
+<programlisting>
+mysql> <userinput>INSERT INTO e VALUES (41, "Michael", "Green");</userinput>
+Query OK, 1 row affected (0.05 sec)
+
+mysql> <userinput>SELECT PARTITION_NAME, TABLE_ROWS</userinput>
+ -> <userinput>FROM INFORMATION_SCHEMA.PARTITIONS</userinput>
+ -> <userinput>WHERE TABLE_NAME = 'e';</userinput>
++----------------+------------+
+| PARTITION_NAME | TABLE_ROWS |
++----------------+------------+
+| p0 | 1 |
+| p1 | 0 |
+| p2 | 0 |
+| p3 | 3 |
++----------------+------------+
+4 rows in set (0.00 sec)
+</programlisting>
+
+ <para>
+ Now we once again exchange partition <literal>p0</literal> with
+ table <literal>e2</literal> using the same
+ <literal role="stmt">ALTER TABLE</literal> statement as
+ previously:
+ </para>
+
+<programlisting>
+mysql> <userinput>ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;</userinput>
+Query OK, 0 rows affected (0.28 sec)
+</programlisting>
+
+ <para>
+ The output of the following queries shows that the table row
+ that was stored in partition <literal>p0</literal> and the table
+ row that was stored in table <literal>e2</literal>, prior to
+ issuing the <literal role="stmt">ALTER TABLE</literal>
+ statement, have now switched places:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT * FROM e;</userinput>
++------+-------+-------+
+| id | fname | lname |
++------+-------+-------+
+| 16 | Frank | White |
+| 1669 | Jim | Smith |
+| 337 | Mary | Jones |
+| 2005 | Linda | Black |
++------+-------+-------+
+4 rows in set (0.00 sec)
+
+mysql> <userinput>SELECT PARTITION_NAME, TABLE_ROWS</userinput>
+ -> <userinput>FROM INFORMATION_SCHEMA.PARTITIONS</userinput>
+ -> <userinput>WHERE TABLE_NAME = 'e';</userinput>
++----------------+------------+
+| PARTITION_NAME | TABLE_ROWS |
++----------------+------------+
+| p0 | 1 |
+| p1 | 0 |
+| p2 | 0 |
+| p3 | 3 |
++----------------+------------+
+4 rows in set (0.00 sec)
+
+mysql> <userinput>SELECT * FROM e2;</userinput>
++----+---------+-------+
+| id | fname | lname |
++----+---------+-------+
+| 41 | Michael | Green |
++----+---------+-------+
+1 row in set (0.00 sec)
+</programlisting>
+
+ <para>
+ You should keep in mind that any rows found in the
+ nonpartitioned table prior to issuing the
+ <literal role="stmt" condition="alter-table">ALTER TABLE ...
+ EXCHANGE PARTITION</literal> statement must meet any conditions
+ required for them to be stored in the target partition;
+ otherwise, the statement fails. To show how this occurs, first
+ insert a row into <literal>e2</literal> that cannot be stored in
+ partition <literal>p0</literal> of table <literal>e</literal>
+ because its <literal>id</literal> column value is too large;
+ then, try to exchange the table with the partition again:
+ </para>
+
+<programlisting>
+mysql> <userinput>INSERT INTO e2 VALUES (51, "Ellen", "McDonald");</userinput>
+Query OK, 1 row affected (0.08 sec)
+
+mysql> <userinput>ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;</userinput>
+<errortext>ERROR 1707 (HY000): Found row that does not match the partition</errortext>
+</programlisting>
+
+ <para>
+ You can also exchange a subpartition of a subpartitioned table (see
+ <xref linkend="partitioning-subpartitions"/>) with a
+ nonpartitioned table using an
+ <literal role="stmt" condition="alter-table">ALTER TABLE ...
+ EXCHANGE PARTITION</literal> statement. In the following
+ example, we first create a table <literal>es</literal> that is
+ partitioned by <literal>RANGE</literal> and subpartitioned by
+ <literal>KEY</literal>, populate this table as we did table
+ <literal>e</literal>, and then create an empty, nonpartitioned
+ copy <literal>es2</literal> of the table, as shown here:
+ </para>
+
+<programlisting>
+mysql> <userinput>CREATE TABLE es (</userinput>
+ -> <userinput>id INT NOT NULL,</userinput>
+ -> <userinput>fname VARCHAR(30),</userinput>
+ -> <userinput>lname VARCHAR(30)</userinput>
+ -> <userinput>)</userinput>
+ -> <userinput>PARTITION BY RANGE (id)</userinput>
+ -> <userinput>SUBPARTITION BY KEY (lname)</userinput>
+ -> <userinput>SUBPARTITIONS 2 (</userinput>
+ -> <userinput>PARTITION p0 VALUES LESS THAN (50),</userinput>
+ -> <userinput>PARTITION p1 VALUES LESS THAN (100),</userinput>
+ -> <userinput>PARTITION p2 VALUES LESS THAN (150),</userinput>
+ -> <userinput>PARTITION p3 VALUES LESS THAN (MAXVALUE)</userinput>
+ -> <userinput>);</userinput>
+Query OK, 0 rows affected (2.76 sec)
+
+mysql> <userinput>INSERT INTO es VALUES</userinput>
+ -> <userinput>(1669, "Jim", "Smith"),</userinput>
+ -> <userinput>(337, "Mary", "Jones"),</userinput>
+ -> <userinput>(16, "Frank", "White"),</userinput>
+ -> <userinput>(2005, "Linda", "Black");</userinput>
+Query OK, 4 rows affected (0.04 sec)
+Records: 4 Duplicates: 0 Warnings: 0
+
+mysql> <userinput>CREATE TABLE es2 LIKE es;</userinput>
+Query OK, 0 rows affected (1.27 sec)
+
+mysql> <userinput>ALTER TABLE es2 REMOVE PARTITIONING;</userinput>
+Query OK, 0 rows affected (0.70 sec)
+Records: 0 Duplicates: 0 Warnings: 0
+</programlisting>
+
+ <para>
+ Although we did not explicitly any of the subpartitions, we can
+ obtain generated names for these by including the
+ <literal>SUBPARTITON_NAME</literal> of the
+ <literal role="is">PARTITIONS</literal> table from
+ <literal>INFORMATION_SCHEMA</literal> when selecting from that
+ table, as shown here:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS</userinput>
+ -> <userinput>FROM INFORMATION_SCHEMA.PARTITIONS</userinput>
+ -> <userinput>WHERE TABLE_NAME = 'es';</userinput>
++----------------+-------------------+------------+
+| 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)
+</programlisting>
+
+ <para>
+ The following <literal role="stmt">ALTER TABLE</literal>
+ statement exchanges subpartition <literal>p3sp0</literal> table
+ <literal>es</literal> with the nonpartitioned table
+ <literal>es2</literal>:
+ </para>
+
+<programlisting>
+mysql> <userinput>ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;</userinput>
+Query OK, 0 rows affected (0.29 sec)
+</programlisting>
+
+ <para>
+ You can verify that the rows were exchnaged by issuing the
+ following queries:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS</userinput>
+ -> <userinput>FROM INFORMATION_SCHEMA.PARTITIONS</userinput>
+ -> <userinput>WHERE TABLE_NAME = 'es';</userinput>
++----------------+-------------------+------------+
+| 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 | 0 |
+| p3 | p3sp1 | 0 |
++----------------+-------------------+------------+
+8 rows in set (0.00 sec)
+
+mysql> <userinput>SELECT * FROM es2;</userinput>
++------+-------+-------+
+| id | fname | lname |
++------+-------+-------+
+| 1669 | Jim | Smith |
+| 337 | Mary | Jones |
+| 2005 | Linda | Black |
++------+-------+-------+
+3 rows in set (0.00 sec)
+</programlisting>
+
+ <para>
+ If a table is subpartitioned, you can exchange only a
+ subpartition of the table—not an entire
+ partition—with an unpartitioned table, as shown here:
+ </para>
+
+<programlisting>
+mysql> <userinput>ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;</userinput>
+<errortext>ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition</errortext>
+</programlisting>
+
+ <para>
+ The comparison of table structures used by MySQL is very strict. The
+ number, order, names, and types of columns and indexes of the
+ partitioned table and the nonpartitioned table must match exactly. In
+ addition, both tables must use the same storage engine:
+ </para>
+
+<programlisting>
+mysql> <userinput>CREATE TABLE es3 LIKE e;</userinput>
+Query OK, 0 rows affected (1.31 sec)
+
+mysql> <userinput>ALTER TABLE es3 REMOVE PARTITIONING;</userinput>
+Query OK, 0 rows affected (0.53 sec)
+Records: 0 Duplicates: 0 Warnings: 0
+
+mysql> <userinput>SHOW CREATE TABLE es3\G</userinput>
+*************************** 1. row ***************************
+ Table: es3
+Create Table: CREATE TABLE `es3` (
+ `id` int(11) NOT NULL,
+ `fname` varchar(30) DEFAULT NULL,
+ `lname` varchar(30) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+1 row in set (0.00 sec)
+
+mysql> <userinput>ALTER TABLE es3 ENGINE = MyISAM;</userinput>
+Query OK, 0 rows affected (0.15 sec)
+Records: 0 Duplicates: 0 Warnings: 0
+
+mysql> <userinput>ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;</userinput>
+<errortext>ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL</errortext>
+</programlisting>
+
+ </section>
+
<section id="partitioning-maintenance">
<title>Maintenance of Partitions</title>
Modified: trunk/refman-5.6/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.6/sql-syntax-data-definition.xml 2010-08-12 11:26:08 UTC (rev 22203)
+++ trunk/refman-5.6/sql-syntax-data-definition.xml 2010-08-12 12:20:48 UTC (rev 22204)
Changed blocks: 4, Lines Added: 50, Lines Deleted: 2; 3449 bytes
@@ -580,7 +580,7 @@
ENABLE RENAME ORDER MODIFY CHANGE DEFAULT SET FOREIGN IGNORE
ENGINE TYPE WITH PARSER DISCARD IMPORT TABLESPACE PARTITION
COALESCE REORGANIZE ANALYZE CHECK OPTIMIZE REBUILD REPAIR REMOVE
- TRUNCATE
+ TRUNCATE EXCHANGE
</remark>
<remark role="help-syntax"/>
@@ -631,6 +631,7 @@
| TRUNCATE PARTITION {<replaceable>partition_names</replaceable> | ALL }
| COALESCE PARTITION <replaceable>number</replaceable>
| REORGANIZE PARTITION <replaceable>partition_names</replaceable> INTO (<replaceable>partition_definitions</replaceable>)
+ | EXCHANGE PARTITION <replaceable>partition_name</replaceable> WITH TABLE <replaceable>tbl_name</replaceable>
| ANALYZE PARTITION {<replaceable>partition_names</replaceable> | ALL }
| CHECK PARTITION {<replaceable>partition_names</replaceable> | ALL }
| OPTIMIZE PARTITION {<replaceable>partition_names</replaceable> | ALL }
@@ -1851,6 +1852,53 @@
<listitem>
<para>
+ It is also possible in MySQL ¤t-series; to exchange a
+ table partition or subpartition with a table using
+ <literal>ALTER TABLE <replaceable>pt</replaceable> EXCHANGE
+ PARTITION <replaceable>p</replaceable> WITH TABLE
+ <replaceable>nt</replaceable></literal>, where
+ <replaceable>pt</replaceable> is the partitioned table and
+ <replaceable>p</replaceable> is the partition or subpartition
+ of <replaceable>pt</replaceable> to be exchanged with
+ unpartitioned table <replaceable>nt</replaceable>, provided
+ that the following statements are true:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ Table <replaceable>nt</replaceable> is not itself
+ partitioned.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The structures of tables <replaceable>pt</replaceable> and
+ <replaceable>nt</replaceable> are otherwise identical.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ There are no rows in <replaceable>nt</replaceable> that
+ lie outside the boundaries of the partition definition for
+ <replaceable>p</replaceable>.
+ </para>
+ </listitem>
+
+ </orderedlist>
+
+ <para>
+ For more information about and examples of <literal>ALTER
+ TABLE ... EXCHANGE PARTITION</literal>, see
+ <xref linkend="partitioning-management-exchange"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
Several additional options provide partition maintenance and
repair functionality analogous to that implemented for
nonpartitioned tables by statements such as
@@ -1912,7 +1960,7 @@
<literal>ANALYZE PARTITION</literal>, <literal>CHECK
PARTITION</literal>, <literal>OPTIMIZE PARTITION</literal>,
<literal>REBUILD PARTITION</literal>, <literal>REMOVE
- PARTITIONING</literal>.
+ PARTITIONING</literal>, <literal>EXCHANGE PARTITION</literal>.
</para>
<para>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r22204 - in trunk: dynamic-docs/changelog refman-5.6 | jon.stephens | 12 Aug |
