bk commit - mysqldoc@docsrva tree (jon:1.3651)

[jon]

Below is the list of changes that have just been committed into a local
mysqldoc repository of jon. When jon does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html

ChangeSet
  1.3651 05/09/30 21:05:32 jon@stripped +1 -0
  Partitioning: Managing RANGE and LIST 
  Partitions - 1st draft (partial).

  refman-5.1/partitioning.xml
    1.20 05/09/30 21:05:30 jon@stripped +175 -4
    Managing RANGE and LIST Partitions - 1st draft (partial).

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	jon
# Host:	ghidora.site
# Root:	/home/jon/bk/mysqldoc

--- 1.19/refman-5.1/partitioning.xml	2005-09-29 16:36:33 +10:00
+++ 1.20/refman-5.1/partitioning.xml	2005-09-30 21:05:30 +10:00
@@ -1273,8 +1273,8 @@
 </programlisting>
           
           <para>
-            This would still be true even the table creation statement
-            included a <literal>SUBPARTITIONS 2</literal> clause.
+            This statement would still fail even if it included a
+            <literal>SUBPARTITIONS 2</literal> clause. 
           </para>
         </listitem>
         
@@ -1530,16 +1530,187 @@
       <title id="title-partitioning-management-range-list">&title-partitioning-management-range-list;</title>
 
       <para>
-        Discussion of managing range and list partitions...
+        Range and list partitions are very similar with regard to how
+        the adding and dropping of partitions are handled. For this
+        reason we discuss the management of both sorts of partitioning
+        in this section. For information about working with tables that
+        are partitioned by hash or key, see <xref
+          linkend="partitioning-management-hash-key"/>. Dropping a
+        <literal>RANGE</literal> or <literal>LIST</literal> partition is
+        more straightforward than adding one, so we discuss this first. 
       </para>
+      
+      <para>
+        Dropping a partition from a table that is partitioned by either 
+        <literal>RANGE</literal> or by <literal>LIST</literal> can be
+        accomplished using the <literal>ALTER TABLE</literal> command
+        with a <literal>DROP PARTITION</literal> clause. Here is a very
+        basic example, which supposes that you have already created a
+        table which is partitioned by range and then populated with 10
+        records using the following <literal>CREATE TABLE</literal> and
+        <literal>INSERT</literal> statements:   
+      </para>
+      
+<programlisting>
+mysql&gt; <userinput>CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)</userinput>
+    -&gt;     <userinput>PARTITION BY RANGE(YEAR(purchased))</userinput>
+    -&gt;     <userinput>(</userinput>
+    -&gt;         <userinput>PARTITION p0 VALUES LESS THAN (1990),</userinput>
+    -&gt;         <userinput>PARTITION p1 VALUES LESS THAN (1995),</userinput>
+    -&gt;         <userinput>PARTITION p2 VALUES LESS THAN (2000),</userinput>
+    -&gt;         <userinput>PARTITION p3 VALUES LESS THAN (2005)</userinput>
+    -&gt;     <userinput>);</userinput>
+Query OK, 0 rows affected (0.01 sec)
 
+mysql&gt; <userinput>INSERT INTO tr VALUES</userinput>
+    -&gt;     <userinput>(1, 'desk organiser', '2003-10-15'),</userinput>
+    -&gt;     <userinput>(2, 'CD player', '1993-11-05'),</userinput>
+    -&gt;     <userinput>(3, 'TV set', '1996-03-10'),</userinput>
+    -&gt;     <userinput>(4, 'bookcase', '1982-01-10'),</userinput>
+    -&gt;     <userinput>(5, 'exercise bike', '2004-05-09'),</userinput>
+    -&gt;     <userinput>(6, 'sofa', '1987-06-05'),</userinput>
+    -&gt;     <userinput>(7, 'popcorn maker', '2001-11-22'),</userinput>
+    -&gt;     <userinput>(8, 'aquarium', '1992-08-04'),</userinput>
+    -&gt;     <userinput>(9, 'study desk', '1984-09-16'),</userinput>
+    -&gt;     <userinput>(10, 'lava lamp', '1998-12-25');</userinput>
+Query OK, 10 rows affected (0.01 sec)                  
+</programlisting>
+      
+      <para>
+        You can see which items should have been inserted into partition
+        <literal>p2</literal> as shown here:
+      </para>
+      
+<programlisting>
+mysql&gt; <userinput>SELECT * FROM tr</userinput>
+    -&gt; <userinput>WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';</userinput>
++------+-----------+------------+
+| id   | name      | purchased  |
++------+-----------+------------+
+|    3 | TV set    | 1996-03-10 |
+|   10 | lava lamp | 1998-12-25 |
++------+-----------+------------+
+2 rows in set (0.00 sec)
+</programlisting>
+      
+      <para>
+        To drop the partition named <literal>p2</literal>, execute the
+        following command:
+      </para>
+      
+<programlisting>
+mysql&gt; <userinput>ALTER TABLE tr DROP PARTITION p2;</userinput>
+Query OK, 0 rows affected (0.03 sec)
+</programlisting>
+      
+      <para>
+        It is very important to remember that, <emphasis>when you drop a
+          partition, you also delete all the data that was stored in
+          that partition</emphasis>. You can see that this is the case
+        by re-running the previous <literal>SELECT</literal> query:
+      </para>
+      
+<programlisting>
+mysql&gt; <userinput>SELECT * FROM tr WHERE purchased</userinput> 
+    -&gt; <userinput>BETWEEN '1995-01-01' AND '1999-12-31';</userinput>
+Empty set (0.00 sec)
+</programlisting>
+      
+      <para>
+        If you intend to change the partitioning of a table
+        <emphasis>without</emphasis> losing data, use <literal>ALTER
+          TABLE ... REORGANISE PARTITION</literal> instead. See below or
+        in <xref linkend="alter-table"/> for information about
+        <literal>REORGANISE PARTITION</literal>.   
+      </para>
+      
+      <para>
+        If you now execute a <literal>SHOW CREATE TABLE</literal>
+        command, you can see how the partitioning makeup of the table
+        has been changed:
+      </para>
+
+<programlisting>
+mysql&gt; <userinput>SHOW CREATE TABLE tr\G</userinput>
+*************************** 1. row ***************************
+       Table: tr
+Create Table: CREATE TABLE `tr` (
+  `id` int(11) default NULL,
+  `name` varchar(50) default NULL,
+  `purchased` date default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 
+PARTITION BY RANGE (YEAR(purchased)) (
+  PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM, 
+  PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM, 
+  PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
+)
+1 row in set (0.01 sec)
+</programlisting>
+      
+      <para>
+        When you insert new rows into the changed table with
+        <literal>purchased</literal> column values between
+        <literal>'1995-01-01'</literal> and
+        <literal>'2004-12-31'</literal> inclusive, those rows will be
+        stored in partition <literal>p3</literal>. You can verify this
+        as follows:
+      </para>
+      
+<programlisting>
+mysql&gt; <userinput>INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');</userinput>
+Query OK, 1 row affected (0.00 sec)
+
+mysql&gt; <userinput>SELECT * FROM tr WHERE purchased</userinput> 
+    -&gt; <userinput>BETWEEN '1995-01-01' AND '2004-12-31';</userinput>
++------+----------------+------------+
+| id   | name           | purchased  |
++------+----------------+------------+
+|   11 | pencil holder  | 1995-07-12 |
+|    1 | desk organiser | 2003-10-15 |
+|    5 | exercise bike  | 2004-05-09 |
+|    7 | popcorn maker  | 2001-11-22 |
++------+----------------+------------+
+4 rows in set (0.00 sec)
+
+mysql&gt; <userinput>ALTER TABLE tr DROP PARTITION p3;</userinput>
+Query OK, 0 rows affected (0.03 sec)
+
+mysql&gt; <userinput>SELECT * FROM tr WHERE purchased</userinput> 
+    -&gt; <userinput>BETWEEN '1995-01-01' AND '2004-12-31';</userinput>
+Empty set (0.00 sec)
+</programlisting>
+      
+      <para>
+        Note that the number of rows dropped from the table as a result
+        of <literal>ALTER TABLE ... DROP PARTITION</literal> is
+        not reported by the server as it would be by the equivalent
+        <literal>DELETE</literal> query.
+      </para>
+      
+      <para>
+        Dropping <literal>LIST</literal> partitions uses exactly the
+        same <literal>ALTER TABLE ... DROP PARTITION</literal> syntax as
+        use for dropping <literal>RANGE</literal> partitions. However,
+        there is one important difference in the effect this has on your
+        use of the table afterwards: You can no longer insert into the
+        table any rows having any of the values that were included in
+        the value list defining the deleted partition.
+      </para>
+      
     </section>
     
     <section id="partitioning-management-hash-key">
       <title id="title-partitioning-management-hash-key">&title-partitioning-management-hash-key;</title>
       
       <para>
-        Discussion of managing hash and key partitions...
+        Tables which are partitioned by hash or by key are very similar
+        to one another with regard to making changes in a partitioning
+        setup, and both differ in a number of ways from tables which
+        have been partitioned by range or list. For that reason, this
+        section addresses the modification of tables partitioned by hash
+        or by key only. For a discussion of adding and dropping of
+        partitions of tables that are partitioned by range or list, see
+        <xref linkend="partitioning-management-range-list"/>.
       </para>
       
     </section>