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

[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.3625 05/09/27 02:22:01 jon@stripped +1 -0
  Refman-5.1/Partitioning:
  [LINEAR] HASH partitioning - initial draft.

  refman-5.1/partitioning.xml
    1.13 05/09/27 02:22:00 jon@stripped +154 -10
    [LINEAR] HASH partitioning - initial draft.

# 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.12/refman-5.1/partitioning.xml	2005-09-24 23:32:14 +10:00
+++ 1.13/refman-5.1/partitioning.xml	2005-09-27 02:22:00 +10:00
@@ -105,18 +105,26 @@
       If you do not see the <literal>have_partition_engine</literal>
       variable with the value <literal>YES</literal> listed as shown
       above in the output of this query, then your version of MySQL does
-      not support partitioning. It should be noted that you can use any
-      storage engine that is supported by your MySQL server for creating
-      partitioned tables; the partitioning engine runs in a separate
-      layer and can interact with any other storage engine used by
-      MySQL.
+      not support partitioning.
     </para>
     
     <para>
-      Note that partitioning applies to all data and indexes of a table;
-      you cannot partition only the data and not the indexes, or
-      <foreignphrase>vice versa</foreignphrase>, nor can you partition
-      only a portion of the table.
+      For creating partitioned tables, you can use any storage engine
+      that is supported by your MySQL server; the MySQL partitioning
+      engine runs in a separate layer and can interact with any other
+      storage engine used by MySQL. In MySQL &current-series;, all
+      partitions of the same partitioned table must use the same storage
+      engine; for example, you cannot use <literal>MyISAM</literal> for
+      one partition and <literal>InnoDB</literal> for another. However,
+      there is nothing preventing you from using different storage
+      for different partitioned tables.
+    </para>
+    
+    <para>
+      <emphasis role="bold">Note</emphasis>: Partitioning applies to all
+      data and indexes of a table; you cannot partition only the data
+      and not the indexes, or <foreignphrase>vice versa</foreignphrase>,
+      nor can you partition only a portion of the table.
     </para>
     
     <para>
@@ -690,7 +698,143 @@
       <title
id="title-partitioning-hash">&title-partitioning-hash;</title>
       
       <para>
-        This section discusses partitioning by hash.
+        Partitioning by <literal>HASH</literal> is used primarily to
+        assure an even distribution of data amongst a predetermined
+        number of partitions. With range or list partitioning, you
+        must specify explicitly into which partition a given column
+        value or set of column values is to be stored; with hash
+        partitioning, MySQL takes care of this for you, and you need
+        only specify a column value or expression based on a column
+        value to be hashed and the number of partitions into which the
+        partitioned table is to be divided.    
+      </para>
+      
+      <para>
+        To partition a table using <literal>HASH</literal> partitioning,
+        it is necessary to append to the <literal>CREATE TABLE</literal>
+        statement a <literal>PARTITION BY HASH
+          (<replaceable>expr</replaceable>)</literal> clause. where
+        <replaceable>expr</replaceable> is an expression that returns an
+        integer. This can simply be the name of a column whose type is
+        one of MySQL's integer types. In addition, you will most
+        likely want to follow this with a
+       
<literal>PARTITIONS(<replaceable>num</replaceable>)</literal>
+        clause, where <replaceable>num</replaceable> is a non-negative
+        integer representing the number of partitions into which the
+        table is to be divided.
+      </para>
+      
+      <para>
+        For example, the following statement creates a table that uses
+        hashing on the <literal>store_id</literal> column and is divided
+        into 4 partitions:  
+      </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,
+    store_id INT
+)
+PARTITION BY HASH(store_id)
+PARTITIONS(4);
+</programlisting>
+      
+      <para>
+        If not specified, the number of partitions defaults to
+        <literal>1</literal>. <emphasis
+          role="bold">Exception</emphasis>: For <literal>NDB
+            Cluster</literal> tables, the default number of partitions
+        is the same as the number of cluster data nodes, possibly 
+        modified to take into account any <literal>MAX_ROWS</literal>
+        setting in order to ensure that all rows can fit into the
+        partitions. (See <xref linkend="ndbcluster"/>.)  
+      </para>
+
+      <para>
+        You can also use an SQL expression that returns an integer for
+        <replaceable>expr</replaceable>. For instance, you might want to
+        partition based on the year in which an employee was hired. This
+        can be done as shown here:
+      </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,
+    store_id INT
+)
+PARTITION BY HASH(YEAR(hired))
+PARTITIONS(4);
+</programlisting>
+      
+      <para>
+        You may use any functional or other expression for
+        <replaceable>expr</replaceable> that is valid in MySQL, so long
+        as it returns a non-constant, non-random integer value. (In
+        other words, it should be varying but deterministic.) However,
+        you should keep in mind that this expression is evaluated each
+        time a row is inserted, updated, or deleted; this means that
+        very complex expressions may give rise to performance issues,
+        particularly when performing operations (such as batch inserts) 
+        that affect a great many rows.  
+      </para>
+      
+      <para>
+        The most efficient hashing function is one which operates upon a
+        single table column and whose value increases or decreases with
+        the column value, as this allows for pruning on ranges of
+        partitions. In theory, such pruning is also possible for
+        expressions involving more than column value, but determining
+        these can be quite difficult and for this reason use of
+        expressions involving multiple columns is not particularly
+        recommended.  
+      </para>
+      
+      <para>
+        MySQL also supports linear hashing, which differs from regular
+        hashing in that linear hashing utilizes a linear powers-of-two
+        algorithm whereas regular hashing employs the modulus of the
+        hashing function's value. (See <xref linkend="create-table"/>
+        for details.)
+      </para>
+      
+      <para>
+        Syntactically, the only difference between linear-hash
+        partitioning and regular hashing is the addition of the
+        <literal>LINEAR</literal> keyword in the <literal>PARTITION
+          BY</literal> clause, as shown here: 
+      </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,
+    store_id INT
+)
+PARTITION BY LINEAR HASH(YEAR(hired))
+PARTITIONS(4);
+</programlisting>
+      
+      <para>
+        The advantage in partitioning by linear hash is that the
+        addition of a new partition by splitting an existing one is made
+        much faster, which can be beneficial whn dealing with tables
+        containing extremely large amounts (terabytes) of data. The
+        disadvantage is that data is not evenly distributed between
+        partitions. 
       </para>
       
     </section>