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.3639 05/09/29 11:39:49 jon@stripped +3 -0
RefMan 5.1/Partitioning chapter:
- Rough draft of Subpartitioning section.
- Added new section for combining/reorganise
partitions (to cover new COALESCE and REORGANISE
options of ALTER TABLE).
RefMan-5.1/SQL Syntax: Corrections to partitioning
options for CREATE TABLE
Added title for new Partition Management subsection
to common titles file.
refman-common/titles.en.ent
1.51 05/09/29 11:39:47 jon@stripped +1 -0
Added title of new Partition section (Combining & Reorganising).
refman-5.1/sql-syntax.xml
1.55 05/09/29 11:39:47 jon@stripped +1 -6
Cut [STORAGE] ENGINE clauses in CREATE TABLE
syntax for partitioning (not yet supported).
Removed VALUES clauses for subpartitions
(not valid for subpartitions).
refman-5.1/partitioning.xml
1.18 05/09/29 11:39:47 jon@stripped +322 -2
Rough/partial draft of Subpartitioning section.
Added new section for combining/reorganise
partitions (to cover new COALESCE and REORGANISE
options of ALTER TABLE).
# 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: gigan.
# Root: /home/jon/bk/mysqldoc2
--- 1.17/refman-5.1/partitioning.xml 2005-09-28 16:11:53 +10:00
+++ 1.18/refman-5.1/partitioning.xml 2005-09-29 11:39:47 +10:00
@@ -1167,9 +1167,319 @@
<title id="title-partitioning-subpartitions">&title-partitioning-subpartitions;</title>
<para>
- This section discusses subpartitioning.
+ Subpartitioning is the further division of each partition in a
+ partitioned table. For example, consider the following
+ <literal>CREATE TABLE</literal> statement:
</para>
+<programlisting>
+CREATE TABLE ts (id INT, purchased DATE)
+ PARTITION BY RANGE(YEAR(purchased))
+ SUBPARTITION BY HASH(TO_DAYS(purchased))
+ SUBPARTITIONS 2
+ (
+ PARTITION p0 VALUES LESS THAN (1990),
+ PARTITION p1 VALUES LESS THAN (2000),
+ PARTITION p2 VALUES LESS THAN (MAX_VALUE)
+ );
+</programlisting>
+
+ <para>
+ Table <literal>ts</literal> has 3 <literal>RANGE</literal>
+ partitions. Each of these partitions —
+ <literal>p0</literal>, <literal>p1</literal>, and
+ <literal>p2</literal> — is further divided into 2
+ subpartitions. In effect, the entire table is divided into
+ <literal>3 * 2 = 6</literal> partitions. However, due to the
+ action of the <literal>PARTITION BY RANGE</literal> clause, the
+ first 2 of these store only those records with a value less than
+ 1990 in the <literal>purchased</literal> column.
+ </para>
+
+ <para>
+ In MySQL ¤t-series;, it is possible to subpartition tables
+ that are partitioned by <literal>RANGE</literal> or
+ <literal>LIST</literal>. Subpartitions may use either
+ <literal>HASH</literal> or <literal>KEY</literal> partitioning.
+ </para>
+
+ <para>
+ It is also possible to define subpartitions explicitly using
+ <literal>SUBPARTITION</literal> clauses in order to specify
+ options for individual subpartitions. For example, a more
+ verbose fashion of creating the same table <literal>ts</literal>
+ as shown in the previous example would be:
+ </para>
+
+<programlisting>
+CREATE TABLE ts (id INT, purchased DATE)
+ PARTITION BY RANGE(YEAR(purchased))
+ SUBPARTITION BY HASH(TO_DAYS(purchased))
+ (
+ PARTITION p0 VALUES LESS THAN (1990)
+ (
+ SUBPARTITION s0,
+ SUBPARTITION s1
+ ),
+ PARTITION p1 VALUES LESS THAN (2000)
+ (
+ SUBPARTITION s2,
+ SUBPARTITION s3
+ ),
+ PARTITION p2 VALUES LESS THAN (MAX_VALUE)
+ (
+ SUBPARTITION s4,
+ SUBPARTITION s5
+ )
+ );
+</programlisting>
+
+ <para>
+ Some syntactical items of note:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ Each partition must have the same number of subpartitions.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you explicitly define any subpartitions using
+ <literal>SUBPARTITION</literal> on any partition of a
+ parttioned table, you must define them all. In other words,
+ the following statement will fail:
+ </para>
+
+<programlisting>
+CREATE TABLE ts (id INT, purchased DATE)
+ PARTITION BY RANGE(YEAR(purchased))
+ SUBPARTITION BY HASH(TO_DAYS(purchased))
+ (
+ PARTITION p0 VALUES LESS THAN (1990)
+ (
+ SUBPARTITION s0,
+ SUBPARTITION s1
+ ),
+ PARTITION p1 VALUES LESS THAN (2000),
+ PARTITION p2 VALUES LESS THAN (MAX_VALUE)
+ (
+ SUBPARTITION s2,
+ SUBPARTITION s3
+ )
+ );
+</programlisting>
+
+ <para>
+ This would still be true even the table creation statement
+ included a <literal>SUBPARTITIONS 2</literal> clause.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Each <literal>SUBPARTITION</literal> clause must include (at
+ a minimum) a name for the subpartition. Otherwise, you may
+ set any desired option for the subpartition or allow it to
+ assume its default setting for that option.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Names of subpartitions must be unique within each partition,
+ but do not have to be unique within the table as a whole. For
+ example, the following <literal>CREATE TABLE</literal>
+ statement is valid:
+ </para>
+
+<programlisting>
+CREATE TABLE ts (id INT, purchased DATE)
+ PARTITION BY RANGE(YEAR(purchased))
+ SUBPARTITION BY HASH(TO_DAYS(purchased))
+ (
+ PARTITION p0 VALUES LESS THAN (1990)
+ (
+ SUBPARTITION s0,
+ SUBPARTITION s1
+ ),
+ PARTITION p1 VALUES LESS THAN (2000)
+ (
+ SUBPARTITION s0,
+ SUBPARTITION s1
+ ),
+ PARTITION p2 VALUES LESS THAN (MAX_VALUE)
+ (
+ SUBPARTITION s0,
+ SUBPARTITION s1
+ )
+ );
+</programlisting>
+ </listitem>
+ </itemizedlist>
+
+ <para>
+ Subpartitions can be used with especially large tables to
+ distribute data and indexes across many disks. Suppose that you
+ have 6 disks mounted as <filename>/disk0</filename>,
+ <filename>/disk1</filename>, <filename>/disk2</filename>, and so
+ on, and then consider the following example:
+ </para>
+
+<programlisting>
+CREATE TABLE ts (id INT, purchased DATE)
+ PARTITION BY RANGE(YEAR(purchased))
+ SUBPARTITION BY HASH(TO_DAYS(purchased))
+ (
+ PARTITION p0 VALUES LESS THAN (1990)
+ (
+ SUBPARTITION s0 DATA DIRECTORY = '/disk0/data' INDEX DIRECTORY = '/disk0/idx',
+ SUBPARTITION s1 DATA DIRECTORY = '/disk1/data' INDEX DIRECTORY = '/disk1/idx'
+ ),
+ PARTITION p1 VALUES LESS THAN (2000)
+ (
+ SUBPARTITION s0 DATA DIRECTORY = '/disk2/data' INDEX DIRECTORY = '/disk2/idx',
+ SUBPARTITION s1 DATA DIRECTORY = '/disk3/data' INDEX DIRECTORY = '/disk3/idx'
+ ),
+ PARTITION p2 VALUES LESS THAN (MAX_VALUE)
+ (
+ SUBPARTITION s0 DATA DIRECTORY = '/disk4/data' INDEX DIRECTORY = '/disk4/idx',
+ SUBPARTITION s1 DATA DIRECTORY = '/disk5/data' INDEX DIRECTORY = '/disk5/idx'
+ )
+ );
+</programlisting>
+
+ <para>
+ In this case, a separate disk is used for the data and for the
+ indexes of each <literal>RANGE</literal>. Many other variations
+ are possible; another example might be:
+ </para>
+
+<programlisting>
+CREATE TABLE ts (id INT, purchased DATE)
+ PARTITION BY RANGE(YEAR(purchased))
+ SUBPARTITION BY HASH(TO_DAYS(purchased))
+ (
+ PARTITION p0 VALUES LESS THAN (1990)
+ (
+ SUBPARTITION s0a DATA DIRECTORY = '/disk0' INDEX DIRECTORY = '/disk1',
+ SUBPARTITION s0b DATA DIRECTORY = '/disk2' INDEX DIRECTORY = '/disk3'
+ ),
+ PARTITION p1 VALUES LESS THAN (2000)
+ (
+ SUBPARTITION s1a DATA DIRECTORY = '/disk4/data' INDEX DIRECTORY = '/disk4/idx',
+ SUBPARTITION s1b DATA DIRECTORY = '/disk5/data' INDEX DIRECTORY = '/disk5/idx'
+ ),
+ PARTITION p2 VALUES LESS THAN (MAX_VALUE)
+ (
+ SUBPARTITION s2a,
+ SUBPARTITION s2b
+ )
+ );
+</programlisting>
+
+ <para>
+ Here, the storage is as follows:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ Records dating from before 1990 take up a vast amount of
+ space, and so are split up 4 ways, with a separate disk
+ dedicated to the data and to the indexes for each of the two
+ subpartitions (<literal>s0a</literal> and
+ <literal>s0b</literal>) making up partition
+ <literal>p0</literal>. In other words:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ The data for subpartition <literal>s0a</literal> is
+ stored on <filename>/disk0</filename>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The indexes for subpartition <literal>s0a</literal> are
+ stored on <filename>/disk1</filename>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The data for subpartition <literal>s0b</literal> is
+ stored on <filename>/disk2</filename>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The indexes for subpartition <literal>s0b</literal> are
+ stored on <filename>/disk3</filename>.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ Records dating from 1990 to 1999 (partition
+ <literal>p1</literal>) do not require as much room as the
+ pre-1990 records. These are split between 2 disks
+ (<filename>/disk4</filename> and
+ <filename>/disk5</filename>) rather than 4 disks as with the
+ legacy records stored in <literal>p0</literal>:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ Data and indexes belonging to <literal>p1</literal>'s
+ first subpartition (<literal>s1a</literal>) are stored
+ on <filename>/disk4</filename> — the data in
+ <filename>/disk4/data</filename>, and the indexes in
+ <filename>/disk4/idx</filename>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Data and indexes belonging to <literal>p1</literal>'s
+ second subpartition (<literal>s1b</literal>) are stored
+ on <filename>/disk5</filename> — the data in
+ <filename>/disk5/data</filename>, and the indexes in
+ <filename>/disk5/idx</filename>.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ Records dating from the year 2000 to the present (partition
+ <literal>p2</literal>) do not take up as much space as
+ required by either of the two previous ranges. Currently, it
+ is sufficient to store all of these in the default location.
+ </para>
+
+ <para>
+ In future, when the number of purchase records for the
+ decade beginning with the year 2000 grows to a point where
+ the default location no longer provides sufficient space,
+ they can be moved using an <literal>ALTER TABLE ...
+ REORGANISE PARTITION</literal> statement. See <xref
+ linkend="partitioning-add-drop"/> for an explanation of
+ how this can be done.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+
</section>
</section>
@@ -1192,7 +1502,17 @@
</para>
</section>
-
+
+ <section id="partitioning-coalesce-reorganise">
+ <title id="title-partitioning-coalesce-reorganise">&title-partitioning-coalesce-reorganise;</title>
+
+ <para>
+ Discussion of combining and reorganising partitions goes here.
+ </para>
+
+ </section>
+
+
<section id="partitioning-info">
<title id="title-partitioning-info">&title-partitioning-info;</title>
--- 1.54/refman-5.1/sql-syntax.xml 2005-09-28 02:35:01 +10:00
+++ 1.55/refman-5.1/sql-syntax.xml 2005-09-29 11:39:47 +10:00
@@ -1506,22 +1506,17 @@
[MAX_ROWS [=] <replaceable>max_number_of_rows</replaceable>]
[MIN_ROWS [=] <replaceable>min_number_of_rows</replaceable>]
[TABLESPACE [=] (<replaceable>tablespace_name</replaceable>)]
- [[STORAGE] ENGINE [=] <replaceable>storage_engine_name</replaceable>]
[NODEGROUP [=] <replaceable>node_group_id</replaceable>]
[(<replaceable>subpartition_definition</replaceable>), [(<replaceable>subpartition_definition</replaceable>)], ...]
<replaceable>subpartition_definition</replaceable>:
SUBPARTITION <replaceable>logical_name</replaceable>
- [VALUES {
- LESS THAN (<replaceable>expr</replaceable> | <literal>MAX_VALUE</literal>)
- | IN (<replaceable>value_list</replaceable>) }]
[COMMENT [=] <replaceable>'comment_text'</replaceable> ]
[DATA DIRECTORY [=] '<filename><replaceable>data_dir</replaceable></filename>']
[INDEX DIRECTORY [=] '<filename><replaceable>index_dir</replaceable></filename>']
[MAX_ROWS [=] <replaceable>max_number_of_rows</replaceable>]
[MIN_ROWS [=] <replaceable>min_number_of_rows</replaceable>]
[TABLESPACE [=] (<replaceable>tablespace_name</replaceable>)]
- [[STORAGE] ENGINE [=] <replaceable>storage_engine_name</replaceable>]
[NODEGROUP [=] <replaceable>node_group_id</replaceable>]
<replaceable>select_statement:</replaceable>
@@ -3145,7 +3140,7 @@
specified, which may be of any type supported by this
MySQL server. Both the <literal>STORAGE</literal>
keyword and the equals sign (<literal>=</literal>) are
- optional. If no partition-spceific storage engine is set
+ optional. If no partition-specific storage engine is set
using this option then the engine applying to the table
as a whole is used for this partition.
</para>
--- 1.50/refman-common/titles.en.ent 2005-09-28 02:35:01 +10:00
+++ 1.51/refman-common/titles.en.ent 2005-09-29 11:39:47 +10:00
@@ -1236,6 +1236,7 @@
<!ENTITY title-partitioning-subpartitions "Subpartitioning">
<!ENTITY title-partitioning-management "Partition Management">
<!ENTITY title-partitioning-add-drop "Adding and Dropping Partitions">
+<!ENTITY title-partitioning-coalesce-reorganise "Combining and Reorganising Partitions">
<!ENTITY title-partitioning-info "Obtaining Information About Partitions">
<!ENTITY title-password-hashing "Password Hashing in MySQL 4.1">
<!ENTITY title-password-hashing-4-1-0 "Password Hashing in MySQL 4.1.0">
| Thread |
|---|
| • bk commit - mysqldoc@docsrva tree (jon:1.3639) | jon | 29 Sep |
