svn commit - mysqldoc@docsrva: r3082 - trunk/refman-5.1

[jon]

Author: jstephens
Date: 2006-08-22 16:03:13 +0200 (Tue, 22 Aug 2006)
New Revision: 3082

Log:
Update/expand discussion of PKs/UKs vs partitioning keys (Thanks, Mikael!)

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-22 11:33:18 UTC (rev 3081)
+++ trunk/refman-5.1/partitioning.xml	2006-08-22 14:03:13 UTC (rev 3082)
Changed blocks: 7, Lines Added: 88, Lines Deleted: 24; 6132 bytes

@@ -4151,7 +4151,7 @@
       <literal>=</literal> relation against a column used in the
       partitioning expression. Consider a table created like this:
     </para>
-    
+
 <programlisting>
 CREATE TABLE t4 (
     fname VARCHAR(50) NOT NULL,

@@ -4162,46 +4162,46 @@
 PARTITION BY KEY(region_code)
 PARTITIONS 8;
 </programlisting>
-    
+
     <para>
       Any query such as this one can be pruned:
     </para>
-    
+
 <programlisting>
 SELECT * FROM t4 WHERE region_code = 7;
-</programlisting>    
-    
+</programlisting>
+
     <para>
       Pruning can also be employed for short ranges, because the
       optimizer can turn such conditions into <literal>IN</literal>
       relations. For example, using the same table <literal>t4</literal>
       as defined previously, queries such as these can be pruned:
     </para>
-    
+
 <programlisting>
 SELECT * FROM t4 WHERE region_code &gt; 2 AND region_code &lt; 6;  
 
 SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;
 </programlisting>
-    
+
     <para>
       In both these cases, the <literal>WHERE</literal> clause is
       transformed by the optimizer into <literal>WHERE region_code IN
-        (3, 4, 5)</literal>. <emphasis
role="bold">Important</emphasis>:
+      (3, 4, 5)</literal>. <emphasis role="bold">Important</emphasis>:
       This optimization is used only if the range size is smaller than
-      the number of partitions. Consider this query: 
+      the number of partitions. Consider this query:
     </para>
-    
+
 <programlisting>
 SELECT * FROM t4 WHERE region_code BETWEEN 4 AND 8;
 </programlisting>
-    
+
     <para>
       The range in the <literal>WHERE</literal> clause covers 5 values
       (4, 5, 6, 7, 8), but <literal>t4</literal> has only 4 partitions.
-      This means that the previous query cannot be pruned. 
+      This means that the previous query cannot be pruned.
     </para>
-    
+
     <para>
       Pruning can be used only on integer columns of tables partitioned
       by <literal>HASH</literal> or <literal>KEY</literal>. For
example,

@@ -4209,19 +4209,18 @@
       because <literal>dob</literal> is a <literal>DATE</literal>
       column:
     </para>
-    
+
 <programlisting>
 SELECT * FROM t4 WHERE dob &gt;=- '2001-04-14' AND dob &lt;= '2005-10-15';
 </programlisting>
-    
+
     <para>
       However, if the table stores year values in an
       <literal>INT</literal> column, then a query having <literal>WHERE
-        year_col &gt;= 2001 AND year_col &lt;= 2005</literal> can be
+      year_col &gt;= 2001 AND year_col &lt;= 2005</literal> can be
       pruned.
     </para>
-    
-    
+
   </section>
 
   <section id="partitioning-limitations">

@@ -4439,12 +4438,59 @@
 </programlisting>
 
         <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 rule also applies with regard to any unique keys that the
+          table might have. For example, all of the following table
+          creation statements fail:
         </para>
 
+<programlisting>
+CREATE TABLE t1 (
+    col1 INT NOT NULL,
+    col2 DATE NOT NULL,
+    col3 INT NOT NULL,
+    col4 INT NOT NULL,
+    UNIQUE KEY(col1, col2)
+)
+PARTITION BY HASH(col3)
+PARTITIONS 4;
+
+CREATE TABLE t1 (
+    col1 INT NOT NULL,
+    col2 DATE NOT NULL,
+    col3 INT NOT NULL,
+    col4 INT NOT NULL,
+    PRIMARY KEY(col1, col2),
+    UNIQUE KEY(col3)
+)
+PARTITION BY HASH(col1 + col3)
+PARTITIONS 4;
+
+CREATE TABLE t1 (
+    col1 INT NOT NULL,
+    col2 DATE NOT NULL,
+    col3 INT NOT NULL,
+    col4 INT NOT NULL,
+    PRIMARY KEY(col3),
+    UNIQUE KEY(col1, col2)
+)
+PARTITION BY HASH(col3)
+PARTITIONS 4;
+</programlisting>
+
         <para>
+          In each case, the table would have at least one primary key or
+          unique key that does not include all columns used in the
+          partitioning expression.
+        </para>
+
+        <para>
+          If the table does not have a primary key or any unique keys,
+          then this restriction does not apply, and you may use any
+          column or columns in the partitioning expression as long as
+          the column type is compatible with the partitioning type.
+        </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.

@@ -4487,6 +4533,14 @@
 </programlisting>
 
         <para>
+          Since <literal>t_no_pk</literal> has only
+          <literal>c1</literal> in its partitioning expression,
+          attempting to adding a unique key on <literal>c2</literal>
+          alone fails. However, you can add a unique key that uses both
+          <literal>c1</literal> and <literal>c2</literal>.
+        </para>
+
+        <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>

@@ -4495,9 +4549,10 @@
 
 <programlisting>
 CREATE TABLE np_pk (
-    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+    id INT NOT NULL AUTO_INCREMENT,
     name VARCHAR(50),
-    added DATE
+    added DATE,
+    PRIMARY KEY (id)
 );
 </programlisting>
 

@@ -4514,9 +4569,18 @@
 </programlisting>
 
         <para>
-          This statement, however, is valid:
+          If <literal>np_pk</literal> had a unique key defined on the
+          <literal>id</literal> column, then the previous statement
+          would fail since <literal>added</literal> would not be part of
+          that unique key.
         </para>
 
+        <para>
+          This statement, however, would be valid in either case
+          (<literal>PRIMARY KEY (id)</literal> or <literal>UNIQUE KEY
+          (id)</literal>):
+        </para>
+
 <programlisting>
 ALTER TABLE np_pk
     PARTITION BY HASH(id)


Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml	2006-08-22 11:33:18 UTC (rev 3081)
+++ trunk/refman-5.1/sql-syntax.xml	2006-08-22 14:03:13 UTC (rev 3082)
Changed blocks: 1, Lines Added: 4, Lines Deleted: 3; 1097 bytes

@@ -889,11 +889,12 @@
             ... 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
+            the relationship between a table's primary key, any unique
+            keys it might have, and the column or columns used in the
             partitioning expression, as discussed in
             <link
linkend="partitioning-limitations-partitioning-keys-primary-keys">Partitioning
-            Limitations &mdash; Partitioning Keys and Primary Keys</link>.
+            Limitations &mdash; Partitioning Keys and Primary
+            Keys</link>.
           </para>
 
           <para>