List:Commits« Previous MessageNext Message »
From:jon.stephens Date:January 2 2010 9:42pm
Subject:svn commit - mysqldoc@docsrva: r18390 - trunk/refman-5.5
View as plain text  
Author: jstephens
Date: 2010-01-02 22:42:06 +0100 (Sat, 02 Jan 2010)
New Revision: 18390

Log:

Fixes Docs Bug #49875 (Incorrect descriptions relating to RANGE COLUMNS partitioning)

This fix also moves the material discussing RANGE COLUMNS and LIST COLUMNS partitioning
into a subsections of a new COLUMNS Partitioning section of the 5.5 Partitioning chapter,
and supplies additional notes, links, and examples.

(Thanks Giuseppe, Mikael, and Cyril S.!)



Modified:
   trunk/refman-5.5/partitioning.xml


Modified: trunk/refman-5.5/partitioning.xml
===================================================================
--- trunk/refman-5.5/partitioning.xml	2010-01-02 11:03:58 UTC (rev 18389)
+++ trunk/refman-5.5/partitioning.xml	2010-01-02 21:42:06 UTC (rev 18390)
Changed blocks: 10, Lines Added: 835, Lines Deleted: 179; 42180 bytes

@@ -536,7 +536,7 @@
             This type of partitioning assigns rows to partitions based
             on column values falling within a given range. MySQL 5.5
             adds an extension, <literal>RANGE COLUMNS</literal>, to this
-            type. See <xref linkend="partitioning-range"/>.
+            type. See <xref linkend="partitioning-columns-range"/>.
           </para>
 
         </formalpara>

@@ -552,7 +552,7 @@
             that the partition is selected based on columns matching one
             of a set of discrete values. MySQL 5.5 adds an extension,
             <literal>LIST COLUMNS</literal>, to this type. See
-            <xref linkend="partitioning-list"/>.
+            <xref linkend="partitioning-columns-list"/>.
           </para>
 
         </formalpara>

@@ -857,7 +857,7 @@
         using a <quote>catchall</quote> <literal>VALUES LESS
         THAN</literal> clause in the <literal role="stmt">CREATE
         TABLE</literal> statement that provides for all values greater
-        than highest value explicitly named:
+        than the highest value explicitly named:
       </para>
 
 <programlisting>

@@ -911,8 +911,8 @@
         assuming that two-digit job codes are used for regular
         (in-store) workers, three-digit codes are used for office and
         support personnel, and four-digit codes are used for management
-        positions &mdash; you could create the partitioned table using
-        the following:
+        positions &mdash; you could create this partitioned table using
+        the following statement:
       </para>
 
 <programlisting>

@@ -1039,94 +1039,16 @@
       <para>
         A variant on this type of partitioning, <literal>RANGE
         COLUMNS</literal> partitioning, was introduced in MySQL 5.5.0.
-        <literal>RANGE COLUMNS</literal> partitioning differs
-        significantly from <literal>RANGE</literal> partitioning in the
-        following ways:
+        Partitioning by <literal>RANGE COLUMNS</literal> makes it
+        possible to employ multiple columns for defining partitioning
+        ranges that apply both to placement of rows in partitions and
+        for determining the inclusion or exclusion of specific
+        partitions when performing partition pruning. See
+        <xref linkend="partitioning-columns-range"/>, for more
+        information.
       </para>
 
-      <itemizedlist>
-
-        <listitem>
-          <para>
-            <literal>RANGE COLUMNS</literal> does not accept
-            expressions, only names of columns.
-          </para>
-        </listitem>
-
-        <listitem>
-          <para>
-            <literal>RANGE COLUMNS</literal> accepts a list of one or
-            more columns.
-          </para>
-        </listitem>
-
-        <listitem>
-          <para>
-            <literal>RANGE COLUMNS</literal> is not restricted to
-            columns using integer types; it accepts integer, string,
-            <literal role="type">DATE</literal> and
-            <literal role="stmt">DATETIME</literal> columns.
-          </para>
-        </listitem>
-
-      </itemizedlist>
-
       <para>
-        For example, consider the table defined by the following
-        <literal role="stmt">CREATE TABLE</literal> statement:
-      </para>
-
-<programlisting>
-CREATE TABLE rc (
-    a int, 
-    b int
-) 
-PARTITION BY RANGE COLUMNS(a, b) (
-    PARTITION p0 VALUES LESS THAN (5, 5),
-    PARTITION p1 VALUES LESS THAN (10, 10),
-    PARTITION p2 VALUES LESS THAN (15, MAXVALUE),
-    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
-);
-</programlisting>
-
-      <para>
-        Placement of rows into partitions is determined by the first
-        column listed in the list supplied in
-        <literal>COLUMNS(<replaceable>column_list</replaceable>)</literal>
-        and the first value in each <literal>VALUES LESS THAN</literal>
-        clause. In other words, for purposes of storing rows in
-        individual partitions, the previous table is treated as if it
-        were defined like this:
-      </para>
-
-<programlisting>
-CREATE TABLE rc (
-    a int, 
-    b int
-) 
-PARTITION BY RANGE(a) (
-    PARTITION p0 VALUES LESS THAN (5),
-    PARTITION p1 VALUES LESS THAN (10),
-    PARTITION p2 VALUES LESS THAN (15),
-    PARTITION p3 VALUES LESS THAN (MAXVALUE)
-);
-</programlisting>
-
-      <para>
-        As shown in the preceding example, it is possible for
-        <literal>MAXVALUE</literal> to appear for the same column in
-        more than one <literal>VALUES LESS THAN</literal> clause when
-        using <literal>RANGE COLUMNS</literal>. However, the limiting
-        values for individual columns in successive partition
-        definitions should otherwise be increasing, there should be no
-        more than one partition defined where
-        <literal>MAXVALUE</literal> is used as the upper limit for all
-        column values, and this partition definition should appear last
-        in the list of <literal>PARTITION ... VALUES LESS THAN</literal>
-        clauses.
-      </para>
-
-      <para>
         If you wish to use partitioning based on ranges or intervals of
         time in MySQL &current-series;, you have two options:
       </para>

@@ -1219,18 +1141,15 @@
 
       </orderedlist>
 
-      <para>
-        The use of partitioning columns employing date or time types
-        other than <literal role="type">DATE</literal> or
-        <literal role="type">DATETIME</literal> is not supported with
-        <literal>RANGE COLUMNS</literal>.
-      </para>
+      <note>
+        <para>
+          The use of partitioning columns employing date or time types
+          other than <literal role="type">DATE</literal> or
+          <literal role="type">DATETIME</literal> is not supported with
+          <literal>RANGE COLUMNS</literal>.
+        </para>
+      </note>
 
-      <para>
-        For additional information about <literal>RANGE COLUMN</literal>
-        syntax, see <xref linkend="create-table"/>.
-      </para>
-
     </section>
 
     <section id="partitioning-list">

@@ -1478,60 +1397,790 @@
       <para>
         Beginning with MySQL 5.5.0, MySQL provides support for
         <literal>LIST COLUMNS</literal> partitioning. This is a variant
-        of <literal>LIST</literal> partitioning that allows columns for
-        types other than integer types to be used as partitioning
-        columns; you can use string types,
-        <literal role="type">DATE</literal>, and
-        <literal role="type">DATETIME</literal> columns for this purpose
-        when partitioning a table by <literal>LIST COLUMNS</literal>.
+        of <literal>LIST</literal> partitioning that allows you to use
+        columns of types other than integer types for partitioning
+        columns, as well as to use multiple columns as partitioning
+        keys. For more information, see
+        <xref linkend="partitioning-columns-list"/>.
       </para>
 
+    </section>
+
+    <section id="partitioning-columns">
+
+      <title><literal>COLUMNS</literal> Partitioning</title>
+
+      <indexterm>
+        <primary>partitioning</primary>
+        <secondary>COLUMNS</secondary>
+      </indexterm>
+
+      <indexterm>
+        <primary>columns partitioning</primary>
+      </indexterm>
+
+      <indexterm>
+        <primary>PARTITION BY RANGE COLUMNS</primary>
+      </indexterm>
+
+      <indexterm>
+        <primary>PARTITION BY LIST COLUMNS</primary>
+      </indexterm>
+
+      <indexterm>
+        <seealso>range partitioning</seealso>
+      </indexterm>
+
+      <indexterm>
+        <seealso>list partitioning</seealso>
+      </indexterm>
+
       <para>
-        For example, suppose that you have a business that has customers
-        in 12 cities which, for sales and marketing purposes, you
-        organize into 4 regions of 3 cities each as shown in the
-        following table:
+        The next two sections discuss
+        <firstterm><literal>COLUMNS</literal> partitioning</firstterm>,
+        which are variants on <literal>RANGE</literal> and
+        <literal>LIST</literal> partitioning that were introduced in
+        MySQL 5.5.0. <literal>COLUMNS</literal> partitioning allows the
+        use of multiple columns in partitioning keys. The multiple
+        columns are taken into account both for the purpose of placing
+        rows in partitions and for the determination of which partitions
+        are to be checked for matching rows in partition pruning.
       </para>
 
-      <informaltable>
-        <tgroup cols="2">
-          <colspec colwidth="50*"/>
-          <colspec colwidth="50*"/>
-          <thead>
-            <row>
-              <entry>Region</entry>
-              <entry>Cities</entry>
-            </row>
-          </thead>
-          <tbody>
-            <row>
-              <entry>1</entry>
-              <entry>Oskarshamn, Högsby, Mönsterås</entry>
-            </row>
-            <row>
-              <entry>2</entry>
-              <entry>Vimmerby, Hultsfred, Västervik</entry>
-            </row>
-            <row>
-              <entry>3</entry>
-              <entry>Nässjö, Eksjö, Vetlanda</entry>
-            </row>
-            <row>
-              <entry>4</entry>
-              <entry>Uppvidinge, Alvesta, Växjo</entry>
-            </row>
-          </tbody>
-        </tgroup>
-      </informaltable>
+      <para>
+        In addition, both <literal>RANGE COLUMNS</literal> partitioning
+        and <literal>LIST COLUMNS</literal> partitioning support the use
+        of non-integer columns for defining value ranges or list
+        members. The permitted data types are shown in the following
+        list:
+      </para>
 
+      <itemizedlist>
+
+        <listitem>
+          <para>
+            All integer types: <literal role="type">TINYINT</literal>,
+            <literal role="type">SMALLINT</literal>,
+            <literal role="type">MEDIUMINT</literal>,
+            <literal role="type">INT</literal>
+            (<literal role="type">INTEGER</literal>), and
+            <literal role="type">BIGINT</literal>. (This is the same as
+            with partitioning by <literal>RANGE</literal> and
+            <literal>LIST</literal>.)
+          </para>
+
+          <para>
+            Other numeric data types (such as
+            <literal role="type">DECIMAL</literal> or
+            <literal role="type">FLOAT</literal>) are not supported as
+            partitioning columns.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            <literal role="type">DATE</literal> and
+            <literal role="type">DATETIME</literal>.
+          </para>
+
+          <para>
+            Columns using other data types relating to dates or times
+            are not supported as partitioning columns.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            The following string types:
+            <literal role="type">CHAR</literal>,
+            <literal role="type">VARCHAR</literal>,
+            <literal role="type">BINARY</literal>, and
+            <literal role="type">VARBINARY</literal>.
+          </para>
+
+          <para>
+            <literal role="type">TEXT</literal> and
+            <literal role="type">BLOB</literal> columns are not
+            supported as partitioning columns.
+          </para>
+        </listitem>
+
+      </itemizedlist>
+
       <para>
-        With <literal>LIST COLUMNS</literal> partitioning, you can
-        create a table for customer data that assigns a row to any of 4
-        partitions corresponding to these regions based on the name of
-        the city where a customer resides, as shown here:
+        The discussions of <literal>RANGE COLUMNS</literal> and
+        <literal>LIST COLUMNS</literal> partitioning in the next two
+        sections assume that you are already familiar with partitioning
+        based on ranges and lists as supported in MySQL 5.1 and later;
+        for more information about these, see
+        <xref linkend="partitioning-range"/>, and
+        <xref linkend="partitioning-list"/>, respectively.
       </para>
 
+      <section id="partitioning-columns-range">
+
+        <title>Range columns partitioning</title>
+
+        <para>
+          Range columns partitioning is similar to range partitioning,
+          but allows you to define partitions using ranges based on
+          multiple column values. In addition, you can define the ranges
+          using columns of types other than integer types.
+        </para>
+
+        <para>
+          <literal>RANGE COLUMNS</literal> partitioning differs
+          significantly from <literal>RANGE</literal> partitioning in
+          the following ways:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              <literal>RANGE COLUMNS</literal> does not accept
+              expressions, only names of columns.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>RANGE COLUMNS</literal> accepts a list of one or
+              more columns.
+            </para>
+
+            <para>
+              <literal>RANGE COLUMNS</literal> partitions are based on
+              comparisons between <firstterm>tuples</firstterm> (lists
+              of column values) rather than comparisons between scalar
+              values. Placement of rows in <literal>RANGE
+              COLUMNS</literal> partitions is also based on comparisons
+              between tuples; this is discussed further later in this
+              section.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>RANGE COLUMNS</literal> partitioning columns are
+              not restricted to integer columns; string,
+              <literal role="type">DATE</literal> and
+              <literal role="type">DATETIME</literal> columns can also
+              be used as partitioning columns. (See
+              <xref linkend="partitioning-columns"/>, for details.)
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          The basic syntax for creating a table partitioned by
+          <literal>RANGE COLUMNS</literal> is shown here:
+        </para>
+
 <programlisting>
+CREATE TABLE <replaceable>table_name</replaceable>
+PARTITIONED BY RANGE COLUMNS(<replaceable>column_list</replaceable>) (
+    PARTITION <replaceable>partition_name</replaceable> VALUES LESS THAN (<replaceable>value_list</replaceable>)[,
+    PARTITION <replaceable>partition_name</replaceable> VALUES LESS THAN (<replaceable>value_list</replaceable>)][,
+    ...]
+) 
+
+<replaceable>column_list</replaceable>:
+    <replaceable>column_name</replaceable>[, <replaceable>column_name</replaceable>][, ...]
+
+<replaceable>value_list</replaceable>:
+    <replaceable>value</replaceable>[, <replaceable>value</replaceable>][, ...]
+</programlisting>
+
+        <note>
+          <para>
+            Not all <literal role="stmt">CREATE TABLE</literal> options
+            that can be used when creating partitioned tables are shown
+            here. For complete information, see
+            <xref linkend="create-table"/>.
+          </para>
+        </note>
+
+        <para>
+          In the syntax just shown,
+          <replaceable>column_list</replaceable> is a list of one or
+          more columns (sometimes called a <firstterm>partitioning
+          column list</firstterm>), and
+          <replaceable>value_list</replaceable> is a list of values
+          (that is, it is a <firstterm>partition definition value
+          list</firstterm>). A <replaceable>value_list</replaceable>
+          must be supplied for each partition definition, and each
+          <replaceable>value_list</replaceable> must have the same
+          number of values as the <replaceable>column_list</replaceable>
+          has columns. Generally speaking, if you use
+          <replaceable>N</replaceable> columns in the
+          <literal>COLUMNS</literal> clause, then each <literal>VALUES
+          LESS THAN</literal> clause must also be supplied with a list
+          of <replaceable>N</replaceable> values.
+        </para>
+
+        <para>
+          The elements in the partitioning column list and in the value
+          list defining each partition must occur in the same order. In
+          addition, each element in the value list must be of the same
+          data type as the corresponding element in the column list.
+          However, the order of the column names in the partitioning
+          column list and the value lists does not have to be the same
+          as the order of the table column definitions in the main part
+          of the <literal role="stmt">CREATE TABLE</literal> statement.
+          As with table partitioned by <literal>RANGE</literal>, you can
+          use <literal>MAXVALUE</literal> to represent a value such that
+          any legal value inserted into a given column is always less
+          than this value. Here is an example of a
+          <literal role="stmt">CREATE TABLE</literal> statement that
+          helps to illustrate all of these points:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>CREATE TABLE rcx (</userinput>
+    -&gt;     <userinput>a INT,</userinput>
+    -&gt;     <userinput>b INT,</userinput>
+    -&gt;     <userinput>c CHAR(3),</userinput>
+    -&gt;     <userinput>d INT</userinput>
+    -&gt; <userinput>)</userinput>
+    -&gt; <userinput>PARTITION BY RANGE COLUMNS(a,d,c) (</userinput>
+    -&gt;     <userinput>PARTITION p0 VALUES LESS THAN (5,10,'ggg'),</userinput>
+    -&gt;     <userinput>PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),</userinput>
+    -&gt;     <userinput>PARTITION p2 VALUES LESS THAN (15,30,'sss'),</userinput>
+    -&gt;     <userinput>PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)</userinput>
+    -&gt; <userinput>);</userinput>
+Query OK, 0 rows affected (0.15 sec)
+</programlisting>
+
+        <para>
+          Table <literal>rcx</literal> contains the columns
+          <literal>a</literal>, <literal>b</literal>,
+          <literal>c</literal>, <literal>d</literal>. The partitioning
+          column list supplied to the <literal>COLUMNS</literal> clause
+          uses 3 of these columns, in the order <literal>a</literal>,
+          <literal>d</literal>, <literal>c</literal>. Each value list
+          used to define a partition contains 3 values in the same
+          order; that is, each value list tuple has the form
+          (<literal>INT</literal>, <literal>INT</literal>,
+          <literal>CHAR(3)</literal>), which corresponds to the data
+          types used by columns <literal>a</literal>,
+          <literal>d</literal>, and <literal>c</literal> (in that
+          order).
+        </para>
+
+        <para>
+          Placement of rows into partitions is determined by comparing
+          the tuple from a row to be inserted that matches the column
+          list in the <literal>COLUMNS</literal> clause with the tuples
+          used in the <literal>VALUES LESS THAN</literal> clauses to
+          define partitions of the table. Because we are comparing
+          tuples (that is, lists or sets of values) rather than scalar
+          values, the semantics of <literal>VALUES LESS THAN</literal>
+          as used with <literal>RANGE COLUMNS</literal> partitions
+          differs somewhat from the case with simple
+          <literal>RANGE</literal> partitions. In
+          <literal>RANGE</literal> partitioning, a row generating an
+          expression value that is equal to a limiting value in a
+          <literal>VALUES LESS THAN</literal> is never placed in the
+          corresponding partition; however, when using <literal>RANGE
+          COLUMNS</literal> partitioning, it is sometimes possible for a
+          row whose partitioning partitioning column list&apos;s first
+          element is equal in value to the that of the first element in
+          a <literal>VALUES LESS THAN</literal> value list to be placed
+          in the corresponding partition.
+        </para>
+
+        <para>
+          For example, consider the <literal>RANGE</literal> partitioned
+          table defined by this <literal>CREATE TABLE</literal>
+          statement:
+        </para>
+
+<programlisting>
+CREATE TABLE r1 (
+    a INT,
+    b INT
+)
+PARTITION BY RANGE (a)  (
+    PARTITION p0 VALUES LESS THAN (5),
+    PARTITION p1 VALUES LESS THAN (MAXVALUE)
+);
+</programlisting>
+
+        <para>
+          If we insert 3 rows into this table such that the column value
+          for <literal>a</literal> is <literal>5</literal> for each row,
+          all 3 rows are stored in partition <literal>p1</literal>
+          because the <literal>a</literal> column value is in each case
+          not less than 5, as we can see by executing the proper query
+          against the
+          <literal role="is">INFORMATION_SCHEMA.PARTITIONS</literal>
+          table:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>INSERT INTO r1 VALUES (5,10), (5,11), (5,12);</userinput>
+Query OK, 3 rows affected (0.00 sec)
+Records: 3  Duplicates: 0  Warnings: 0
+
+mysql&gt; <userinput>SELECT PARTITION_NAME,TABLE_ROWS</userinput>
+    -&gt;     <userinput>FROM INFORMATION_SCHEMA.PARTITIONS</userinput>
+    -&gt;     <userinput>WHERE TABLE_NAME = 'r1';</userinput>
++----------------+------------+
+| PARTITION_NAME | TABLE_ROWS |
++----------------+------------+
+| p0             |          0 |
+| p1             |          3 |
++----------------+------------+
+2 rows in set (0.00 sec)
+</programlisting>
+
+        <para>
+          Now consider a similar table <literal>rc1</literal> that uses
+          <userinput>RANGE COLUMNS</userinput> partitioning with both
+          columns <literal>a</literal> and <literal>b</literal>
+          referenced in the <literal>COLUMNS</literal> clause, created
+          as shown here:
+        </para>
+
+<programlisting>
+CREATE TABLE rc1 (
+    a INT, 
+    b INT
+) 
+PARTITION BY RANGE COLUMNS(a, b) (
+    PARTITION p0 VALUES LESS THAN (5, 12),
+    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
+);
+</programlisting>
+
+        <para>
+          If we insert exactly the same rows into
+          <userinput>rc1</userinput> as we just inserted into
+          <userinput>r1</userinput>, the distribution of the rows is
+          quite different:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);</userinput>
+Query OK, 3 rows affected (0.00 sec)
+Records: 3  Duplicates: 0  Warnings: 0
+
+mysql&gt; <userinput>SELECT PARTITION_NAME,TABLE_ROWS</userinput>
+    -&gt;     <userinput>FROM INFORMATION_SCHEMA.PARTITIONS</userinput>
+    -&gt;     <userinput>WHERE TABLE_NAME = 'rc1';</userinput>
++--------------+----------------+------------+
+| TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS |
++--------------+----------------+------------+
+| p            | p0             |          2 |
+| p            | p1             |          1 |
++--------------+----------------+------------+
+2 rows in set (0.00 sec)
+</programlisting>
+
+        <para>
+          This is because we are comparing rows rather than scalar
+          values. We can compare the row values inserted with the
+          limiting row value from the <literal>VALUES THAN LESS
+          THAN</literal> clause used to define partition
+          <literal>p0</literal> in table <literal>rc1</literal>, like
+          this:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT (5,10) &lt; (5,12), (5,11) &lt; (5,12), (5,12) &lt; (5,12);</userinput>
++-----------------+-----------------+-----------------+
+| (5,10) &lt; (5,12) | (5,11) &lt; (5,12) | (5,12) &lt; (5,12) |
++-----------------+-----------------+-----------------+
+|               1 |               1 |               0 |
++-----------------+-----------------+-----------------+
+1 row in set (0.00 sec)
+</programlisting>
+
+        <para>
+          The 2 tuples <literal>(5,10)</literal> and
+          <literal>(5,11)</literal> evaluate as less than
+          <literal>(5,12)</literal>, so they are stored in partition
+          <literal>p0</literal>. Since 5 is not less than 5 and 12 is
+          not less than 12, <literal>(5,12)</literal> is considered not
+          less than <literal>(5,12)</literal>, and is stored in
+          partition <literal>p1</literal>.
+        </para>
+
+        <note>
+          <para>
+            The <literal role="stmt">SELECT</literal> statement in the
+            preceding example could also have been written using
+            explicit row constructors, like this:
+          </para>
+
+<programlisting>
+SELECT ROW(5,10) &lt; ROW(5,12), ROW(5,11) &lt; ROW(5,12), ROW(5,12) &lt; ROW(5,12);
+</programlisting>
+
+          <para>
+            For more information about the use of row constructors in
+            MySQL, see <xref linkend="row-subqueries"/>.
+          </para>
+        </note>
+
+        <para>
+          For a table partitioned by <literal>RANGE COLUMNS</literal>
+          using only a single partitioning column, the storing of rows
+          in partitions is the same as that of an equivalent table that
+          is partitioned by <literal>RANGE</literal>. The following
+          <literal>CREATE TABLE</literal> statement creates a table
+          partitioned by <literal>RANGE COLUMNS</literal> using 1
+          partitioning column:
+        </para>
+
+<programlisting>
+CREATE TABLE rx (
+    a INT,
+    b INT
+)
+PARTITION BY RANGE COLUMNS (a)  (
+    PARTITION p0 VALUES LESS THAN (5),
+    PARTITION p1 VALUES LESS THAN (MAXVALUE)
+); 
+</programlisting>
+
+        <para>
+          If we insert the rows <literal>(5,10)</literal>,
+          <literal>(5,11)</literal>, and <literal>(5,12)</literal> into
+          this table, we can see that their placement is the same as it
+          is for the table <literal>r</literal> we created and populated
+          earlier:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>INSERT INTO rx VALUES (5,10), (5,11), (5,12);</userinput>
+Query OK, 3 rows affected (0.00 sec)
+Records: 3  Duplicates: 0  Warnings: 0
+
+mysql&gt; <userinput>SELECT PARTITION_NAME,TABLE_ROWS</userinput>
+    -&gt;     <userinput>FROM INFORMATION_SCHEMA.PARTITIONS</userinput>
+    -&gt;     <userinput>WHERE TABLE_NAME = 'rx';</userinput>
++--------------+----------------+------------+
+| TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS |
++--------------+----------------+------------+
+| p            | p0             |          0 |
+| p            | p1             |          3 |
++--------------+----------------+------------+
+2 rows in set (0.00 sec)
+</programlisting>
+
+        <para>
+          It is also possible to create tables partitioned by
+          <literal>RANGE COLUMNS</literal> where limiting values for one
+          or more columns are repeated in successive partition
+          definitions. You can do this as long as the tuples of column
+          values used to define the partitions are strictly increasing.
+          For example, each of the following <literal role="stmt">CREATE
+          TABLE</literal> statements is valid:
+        </para>
+
+<programlisting>
+CREATE TABLE rc2 (
+    a INT,
+    b INT
+)
+PARTITION BY RANGE COLUMNS(a,b) (
+    PARTITION p0 VALUES LESS THAN (0,10),
+    PARTITION p1 VALUES LESS THAN (10,20),
+    PARTITION p2 VALUES LESS THAN (10,30),
+    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
+ );
+ 
+CREATE TABLE rc3 (
+    a INT,
+    b INT
+)
+PARTITION BY RANGE COLUMNS(a,b) (
+    PARTITION p0 VALUES LESS THAN (0,10),
+    PARTITION p1 VALUES LESS THAN (10,20),
+    PARTITION p2 VALUES LESS THAN (10,30),
+    PARTITION p3 VALUES LESS THAN (10,35),
+    PARTITION p4 VALUES LESS THAN (20,40),
+    PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
+ );
+</programlisting>
+
+        <para>
+          The following statement also succeeds, even though it might
+          appear at first glance that it would not, since the limiting
+          value of column <literal>b</literal> is 25 for partition
+          <literal>p0</literal> and 20 for partition
+          <literal>p1</literal>, and the limiting value of column
+          <literal>c</literal> is 100 for partition
+          <literal>p1</literal> and 50 for partition
+          <literal>p2</literal>:
+        </para>
+
+<programlisting>
+CREATE TABLE rc4 (
+    a INT,
+    b INT,
+    c INT
+)
+PARTITION BY RANGE COLUMNS(a,b,c) (
+    PARTITION p0 VALUES LESS THAN (0,25,50),
+    PARTITION p1 VALUES LESS THAN (10,20,100),
+    PARTITION p2 VALUES LESS THAN (10,30,50)
+    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE) 
+ );
+</programlisting>
+
+        <para>
+          When designing tables partitioned by <literal>RANGE
+          COLUMNS</literal>, you can always test successive partition
+          definitions by comparing the desired tuples using the
+          <command>mysql</command> client, like this:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT (0,25,50) &lt; (10,20,100), (10,20,100) &lt; (10,30,50);</userinput>
++-------------------------+--------------------------+
+| (0,25,50) &lt; (10,20,100) | (10,20,100) &lt; (10,30,50) |
++-------------------------+--------------------------+
+|                       1 |                        1 |
++-------------------------+--------------------------+
+1 row in set (0.00 sec)
+</programlisting>
+
+        <para>
+          The following <literal role="stmt">CREATE TABLE</literal>
+          statement fails with an error:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>CREATE TABLE rcf (</userinput>
+    -&gt;     <userinput>a INT,</userinput>
+    -&gt;     <userinput>b INT,</userinput>
+    -&gt;     <userinput>c INT</userinput>
+    -&gt; <userinput>)</userinput>
+    -&gt; <userinput>PARTITION BY RANGE COLUMNS(a,b,c) (</userinput>
+    -&gt;     <userinput>PARTITION p0 VALUES LESS THAN (0,25,50),</userinput>
+    -&gt;     <userinput>PARTITION p1 VALUES LESS THAN (20,20,100),</userinput>
+    -&gt;     <userinput>PARTITION p2 VALUES LESS THAN (10,30,50),</userinput>
+    -&gt;     <userinput>PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)</userinput>
+    -&gt;  <userinput>);</userinput>
+<errortext>ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition</errortext>
+</programlisting>
+
+        <para>
+          When you get this error, you can deduce which partition
+          definitions are invalid by making <quote>less than</quote>
+          comparisons between their column lists. In this case, the
+          problem is with the definition of partition
+          <literal>p2</literal> because the tuple used to define it is
+          not less than the tuple used to define partition
+          <literal>p3</literal>, as shown here:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT (0,25,50) &lt; (20,20,100), (20,20,100) &lt; (10,30,50);</userinput>
++-------------------------+--------------------------+
+| (0,25,50) &lt; (20,20,100) | (20,20,100) &lt; (10,30,50) |
++-------------------------+--------------------------+
+|                       1 |                        0 |
++-------------------------+--------------------------+
+1 row in set (0.00 sec)
+</programlisting>
+
+        <para>
+          It is also possible for <literal>MAXVALUE</literal> to appear
+          for the same column in more than one <literal>VALUES LESS
+          THAN</literal> clause when using <literal>RANGE
+          COLUMNS</literal>. However, the limiting values for individual
+          columns in successive partition definitions should otherwise
+          be increasing, there should be no more than one partition
+          defined where <literal>MAXVALUE</literal> is used as the upper
+          limit for all column values, and this partition definition
+          should appear last in the list of <literal>PARTITION ...
+          VALUES LESS THAN</literal> clauses. In addition, you cannot
+          use <literal>MAXVALUE</literal> as the limiting value for the
+          first column in more than one partition definition.
+        </para>
+
+        <para>
+          As stated previously, it is also possible with <literal>RANGE
+          COLUMNS</literal> partitioning to use non-integer columns as
+          partitioning columns. (See
+          <xref linkend="partitioning-columns"/>, for a complete listing
+          of these.) For example, consider a table named
+          <literal>employees</literal> (which is not partitioned),
+          defined using the following <literal role="stmt">CREATE
+          TABLE</literal> statement:
+        </para>
+
+<programlisting>
+CREATE TABLE employees (
+    id INT NOT NULL,
+    fname VARCHAR(30),
+    lname VARCHAR(30),
+    hired DATE NOT NULL DEFAULT '1970-01-01',
+    separated DATE NOT NULL DEFAULT '9999-12-31',
+    job_code INT NOT NULL,
+    store_id INT NOT NULL
+);
+</programlisting>
+
+        <para>
+          Using <literal>RANGE COLUMNS</literal> partitioning, you can
+          create a version of this table that stores each row in one of
+          four partitions based on the employye&apos;s last name, like
+          this:
+        </para>
+
+<programlisting>
+CREATE TABLE employees_by_lname (
+    id INT NOT NULL,
+    fname VARCHAR(30),
+    lname VARCHAR(30),
+    hired DATE NOT NULL DEFAULT '1970-01-01',
+    separated DATE NOT NULL DEFAULT '9999-12-31',
+    job_code INT NOT NULL,
+    store_id INT NOT NULL
+)
+PARTITION BY RANGE COLUMNS (lname)  (
+    PARTITION p0 VALUES LESS THAN ('g'),
+    PARTITION p1 VALUES LESS THAN ('m'),
+    PARTITION p2 VALUES LESS THAN ('t'),
+    PARTITION p3 VALUES LESS THAN (MAXVALUE)
+);
+</programlisting>
+
+        <para>
+          Alternatively, you could cause the
+          <literal>employees</literal> table as created previously to be
+          partitioned using this scheme by executing the following
+          <literal role="stmt">ALTER TABLE</literal> statement:
+        </para>
+
+<programlisting>
+ALTER TABLE employees PARTITION BY RANGE COLUMNS (lname)  (
+    PARTITION p0 VALUES LESS THAN ("g"),
+    PARTITION p1 VALUES LESS THAN ("m"),
+    PARTITION p2 VALUES LESS THAN ("s"),
+    PARTITION p3 VALUES LESS THAN (MAXVALUE)
+);
+</programlisting>
+
+        <note>
+          <para>
+            Because different character sets and collations have
+            different sort orders, the character sets and collations in
+            use may effect which partition of a table partitioned by
+            <literal>RANGE COLUMNS</literal> a given row is stored in
+            when using string columns as partitioning columns. In
+            addition, changing the character set or collation for a
+            given database, table, or column after such a table is
+            created may cause changes in how rows are distributed. For
+            example, when using a case-sensitive collation,
+            <literal>'and'</literal> sorts before
+            <literal>'Andersen'</literal>, but when using a collation
+            that is case insensitive, the reverse is true.
+          </para>
+
+          <para>
+            For information about how MySQL handles character sets and
+            collations, see <xref linkend="charset"/>.
+          </para>
+        </note>
+
+        <para>
+          Similarly, you can cause the <literal>employees</literal>
+          table to be partitioned in such a way that each row is stored
+          in one of several partitions based on the decade in which the
+          corresponding employee was hired using the
+          <literal role="stmt">ALTER TABLE</literal> statement shown
+          here:
+        </para>
+
+<programlisting>
+ALTER TABLE employees PARTITION BY RANGE COLUMNS (hired)  (
+    PARTITION p0 VALUES LESS THAN ("1970-01-01"),
+    PARTITION p1 VALUES LESS THAN ("1980-01-01"),
+    PARTITION p2 VALUES LESS THAN ("1990-01-01"),
+    PARTITION p3 VALUES LESS THAN ("2000-01-01"),
+    PARTITION p4 VALUES LESS THAN ("2010-01-01"),
+    PARTITION p5 VALUES LESS THAN (MAXVALUE)
+);
+</programlisting>
+
+        <para>
+          See <xref linkend="create-table"/>, for additional information
+          about <literal>PARTITION BY RANGE COLUMNS</literal> syntax.
+        </para>
+
+      </section>
+
+      <section id="partitioning-columns-list">
+
+        <title>List columns partitioning</title>
+
+        <para>
+          Beginning with MySQL 5.5.0, MySQL provides support for
+          <literal>LIST COLUMNS</literal> partitioning. This is a
+          variant of <literal>LIST</literal> partitioning that allows
+          the use of multiple columns as partition keys, and for columns
+          of data types other than integer types to be used as
+          partitioning columns; you can use string types,
+          <literal role="type">DATE</literal>, and
+          <literal role="type">DATETIME</literal> columns. (For more
+          information about allowed data types for
+          <literal>COLUMNS</literal> partitioning columns, see
+          <xref linkend="partitioning-columns"/>.)
+        </para>
+
+        <para>
+          For example, suppose that you have a business that has
+          customers in 12 cities which, for sales and marketing
+          purposes, you organize into 4 regions of 3 cities each as
+          shown in the following table:
+        </para>
+
+        <informaltable>
+          <tgroup cols="2">
+            <colspec colwidth="50*"/>
+            <colspec colwidth="50*"/>
+            <thead>
+              <row>
+                <entry>Region</entry>
+                <entry>Cities</entry>
+              </row>
+            </thead>
+            <tbody>
+              <row>
+                <entry>1</entry>
+                <entry>Oskarshamn, Högsby, Mönsterås</entry>
+              </row>
+              <row>
+                <entry>2</entry>
+                <entry>Vimmerby, Hultsfred, Västervik</entry>
+              </row>
+              <row>
+                <entry>3</entry>
+                <entry>Nässjö, Eksjö, Vetlanda</entry>
+              </row>
+              <row>
+                <entry>4</entry>
+                <entry>Uppvidinge, Alvesta, Växjo</entry>
+              </row>
+            </tbody>
+          </tgroup>
+        </informaltable>
+
+        <para>
+          With <literal>LIST COLUMNS</literal> partitioning, you can
+          create a table for customer data that assigns a row to any of
+          4 partitions corresponding to these regions based on the name
+          of the city where a customer resides, as shown here:
+        </para>
+
+<programlisting>
 CREATE TABLE customers_1 (
     first_name VARCHAR(25),
     last_name VARCHAR(25),

@@ -1548,24 +2197,25 @@
 );
 </programlisting>
 
-      <para>
-        As with partitioning by <literal>RANGE COLUMNS</literal>, you do
-        not need to use expressions in the <literal>COLUMNS()</literal>
-        clause to convert column values into integers. (In fact, the use
-        of expressions other than column names is not allowed with
-        <literal>COLUMNS()</literal>.)
-      </para>
+        <para>
+          As with partitioning by <literal>RANGE COLUMNS</literal>, you
+          do not need to use expressions in the
+          <literal>COLUMNS()</literal> clause to convert column values
+          into integers. (In fact, the use of expressions other than
+          column names is not allowed with
+          <literal>COLUMNS()</literal>.)
+        </para>
 
-      <para>
-        It is also possible to use <literal role="type">DATE</literal>
-        and <literal role="type">DATETIME</literal> columns, as shown in
-        the following example that uses the same name and columns as the
-        <literal>customers_1</literal> table shown previously, but
-        employs <literal>LIST COLUMNS</literal> partitioning based on
-        the <literal>renewal</literal> column to store rows in one of 4
-        partitions depending on the week in February 2010 the
-        customer&apos;s account is scheduled to renew:
-      </para>
+        <para>
+          It is also possible to use <literal role="type">DATE</literal>
+          and <literal role="type">DATETIME</literal> columns, as shown
+          in the following example that uses the same name and columns
+          as the <literal>customers_1</literal> table shown previously,
+          but employs <literal>LIST COLUMNS</literal> partitioning based
+          on the <literal>renewal</literal> column to store rows in one
+          of 4 partitions depending on the week in February 2010 the
+          customer&apos;s account is scheduled to renew:
+        </para>
 
 <programlisting>
 CREATE TABLE customers_2 (

@@ -1588,13 +2238,13 @@
 );
 </programlisting>
 
-      <para>
-        This works, but becomes cumbersome to define and maintain if the
-        number of dates involved if the number of dates grows very
-        large; in such cases, it is usually more practical to employ
-        <literal>RANGE</literal> or <literal>RANGE COLUMNS</literal>
-        partitioning instead:
-      </para>
+        <para>
+          This works, but becomes cumbersome to define and maintain if
+          the number of dates involved grows very large; in such cases,
+          it is usually more practical to employ
+          <literal>RANGE</literal> or <literal>RANGE COLUMNS</literal>
+          partitioning instead:
+        </para>
 
 <programlisting>
 CREATE TABLE customers_3 (

@@ -1613,18 +2263,24 @@
 );
 </programlisting>
 
-      <para>
-        See <xref linkend="partitioning-range"/>, for more information.
-      </para>
+        <para>
+          See <xref linkend="partitioning-columns-range"/>, for more
+          information.
+        </para>
 
-      <para>
-        In addition (as with <literal>RANGE COLUMNS</literal>
-        partitioning), you can use multiple columns in the
-        <literal>COLUMNS()</literal> clause. See
-        <xref linkend="create-table"/>, for an example illustrating how
-        this can be done.
-      </para>
+        <para>
+          In addition (as with <literal>RANGE COLUMNS</literal>
+          partitioning), you can use multiple columns in the
+          <literal>COLUMNS()</literal> clause.
+        </para>
 
+        <para>
+          See <xref linkend="create-table"/>, for additional information
+          about <literal>PARTITION BY LIST COLUMNS()</literal> syntax.
+        </para>
+
+      </section>
+
     </section>
 
     <section id="partitioning-hash">


Thread
svn commit - mysqldoc@docsrva: r18390 - trunk/refman-5.5jon.stephens2 Jan