Author: jstephens
Date: 2006-08-21 13:38:33 +0200 (Mon, 21 Aug 2006)
New Revision: 3070
Log:
You can now use ALTER TABLE ... PARTITION BY to partition a previously non-partitioned table.
Modified:
trunk/refman-5.1/partitioning.xml
trunk/refman-5.1/sql-syntax.xml
Modified: trunk/refman-5.1/partitioning.xml
===================================================================
--- trunk/refman-5.1/partitioning.xml 2006-08-21 10:33:14 UTC (rev 3069)
+++ trunk/refman-5.1/partitioning.xml 2006-08-21 11:38:33 UTC (rev 3070)
Changed blocks: 3, Lines Added: 55, Lines Deleted: 5; 3339 bytes
@@ -4317,7 +4317,7 @@
<secondary>and partitioning keys</secondary>
</indexterm>
- <para>
+ <para id="partitioning-limitations-partitioning-keys-primary-keys">
If a table that is to be partitioned has a primary key, then
any columns used in the partitioning expression must be part
of the primary key. For example, the following two statements
@@ -4377,9 +4377,12 @@
<para>
If the table does not have a primary key or unique key, then
this restriction does not apply, and you may use any column or
- columns in the partitioning expression. This also means that
- you cannot later add a primary or unique key to a partitioned
- table unless the primary key or unique key includes all
+ columns in the partitioning expression.
+ </para>
+
+ <para>
+ This also means that you cannot later add a primary or unique key to a
+ partitioned table unless the primary key or unique key includes all
columns used for the partitioning key. Consider given the
partitioned table defined as shown here:
</para>
@@ -4418,12 +4421,59 @@
# fails with ERROR 1482
ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);
</programlisting>
+
+ <para>
+ These rules also apply to existing non-partitioned tables that you
+ wish to partition using <literal>ALTER TABLE ... PARTITION
+ BY</literal>. Consider a table <literal>np_pk</literal> defined as
+ shown here:
+ </para>
+
+<programlisting>
+CREATE TABLE np_pk (
+ id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+ name VARCHAR(50),
+ added DATE
+);
+</programlisting>
+
+ <para>
+ The following <literal>ALTER TABLE</literal> statements fails with an
+ error, because the <literal>added</literal> column is not part of the
+ table's primary key:
+ </para>
+
+<programlisting>
+ALTER TABLE np_pk
+ PARTITION BY HASH( TO_DAYS(added) )
+ PARTITIONS 4;
+</programlisting>
+
+ <para>
+ This statement, however, is valid:
+ </para>
+
+<programlisting>
+ALTER TABLE np_pk
+ PARTITION BY HASH(id)
+ PARTITIONS 4;
+</programlisting>
+
+ <para>
+ In the case of <literal>np_pk</literal>, the only column that may be
+ used as part of a partitioning expression is <literal>id</literal>; if
+ you wish to partition this table using any other columns for the
+ partitioning key, you must first modify the table's primary key,
+ either by adding the desired columns to the primary key, or by
+ dropping the primary key altogether.
+ </para>
</listitem>
<listitem>
<para>
Subpartitions are limited to <literal>HASH</literal> or
- <literal>KEY</literal> partitioning.
+ <literal>KEY</literal> partitioning. <literal>HASH</literal> and
+ <literal>KEY</literal> partitions cannot be subpartitioned.
</para>
</listitem>
Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml 2006-08-21 10:33:14 UTC (rev 3069)
+++ trunk/refman-5.1/sql-syntax.xml 2006-08-21 11:38:33 UTC (rev 3070)
Changed blocks: 4, Lines Added: 35, Lines Deleted: 19; 3526 bytes
@@ -205,7 +205,7 @@
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| <replaceable>table_option</replaceable> ...
- | <replaceable>partition_options</replaceable>
+ | PARTITION BY <replaceable>partition_options</replaceable>
| ADD PARTITION (<replaceable>partition_definition</replaceable>)
| DROP PARTITION <replaceable>partition_names</replaceable>
| COALESCE PARTITION <replaceable>number</replaceable>
@@ -848,13 +848,7 @@
repartitioning, for adding, dropping, merging, and splitting
partitions, and for performing partitioning maintenance.
</para>
-
- <remark role="todo">
- [js] Commented out following until this is actually
- implemented.
- </remark>
-
-<!--
+
<para>
Simply using a <replaceable>partition_options</replaceable>
clause with <literal>ALTER TABLE</literal> on a partitioned
@@ -866,12 +860,40 @@
<replaceable>partition_options</replaceable> clause for
<literal>CREATE TABLE</literal> (see
<xref linkend="create-table"/>, for more detailed
- information). <emphasis role="bold">Note</emphasis>: This
- syntax currently is accepted by the MySQL 5.1 server, but
- does not yet actually do anything; we expect to implement
- this as MySQL 5.1 is developed.
+ information), and can also be used to partition an existing table
+ that is not already partitioned. For example, consider a
+ (non-partitioned) table defined as shown here:
</para>
- -->
+
+<programlisting>
+CREATE TABLE t1 (
+ id INT,
+ year_col INT
+);
+</programlisting>
+
+ <para>
+ This table can be partitioned by <literal>RANGE</literal> using the
+ <literal>id</literal> column as the partitioning key into 8
+ partitions by means of this statement:
+ </para>
+
+<programlisting>
+ALTER TABLE t1
+ PARTITION BY HASH(id)
+ PARTITIONS 8;
+</programlisting>
+
+ <para>
+ The table that results from using an <literal>ALTER TABLE ...
+ PARTITION BY</literal> statement must follow the same rules as one
+ created using <literal>CREATE TABLE ... PARTITION BY</literal>. This
+ includes the rules governing the relationship between a table's
+ primary key (or possibly unique key) and the column or columns used
+ in its partitioning expression, as discussed in
+ <link linkend="partitioning-limitations-partitioning-keys-primary-keys">Partitioning
+ Limitations & Partitioning Keys and Primary Keys</link>.
+ </para>
<para>
The <replaceable>partition_definition</replaceable> clause
@@ -906,12 +928,6 @@
</programlisting>
<para>
- <emphasis role="bold">Note</emphasis>: You cannot use
- <literal>ALTER TABLE</literal> to add partitions to a table
- that is not already partitioned.
- </para>
-
- <para>
<literal>DROP PARTITION</literal> can be used to drop one or
more <literal>RANGE</literal> or <literal>LIST</literal>
partitions. This statement cannot be used with
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r3070 - trunk/refman-5.1 | jon | 21 Aug |
