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.3555 05/09/14 13:22:48 jon@stripped +1 -0
RefMan-5.1/sql-syntax.xml:
- Added partitioning-related extensions
for CREATE TABLE and ALTER TABLE.
- Reformat.
refman-5.1/sql-syntax.xml
1.41 05/09/14 13:22:46 jon@stripped +770 -12
Partitioning-related extensions for CREATE TABLE, 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.40/refman-5.1/sql-syntax.xml 2005-09-14 00:27:40 +10:00
+++ 1.41/refman-5.1/sql-syntax.xml 2005-09-14 13:22:46 +10:00
@@ -157,6 +157,10 @@
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| <replaceable>table_options</replaceable>
+ | ADD PARTITION <replaceable>partition_definition</replaceable>
+ | DROP PARTITION <replaceable>partition_names</replaceable>
+ | COALESCE PARTITION <replaceable>number</replaceable>
+ | REORGANISE PARTITION <replaceable>partition_names</replaceable> (<replaceable>partition_definitions</replaceable>)
</programlisting>
<remark>
@@ -761,6 +765,166 @@
</para>
</listitem>
+ <listitem>
+ <para>
+ <literal>ALTER TABLE</literal> can also be used with
+ partitioned tables for adding, dropping, merging, and
+ splitting partitions.
+ </para>
+
+ <para>
+ The <replaceable>partition_definition</replaceable> clause
+ for <literal>ALTER TABLE ADD PARTITION</literal> supports
+ the same options as the clause of the same name does for the
+ <literal>CREATE TABLE</literal> statement clause of the same
+ name. (See <xref linkend="create-table"/> for the syntax and
+ description.) For example, suppose you have the partitioned
+ table created as shown here:
+ </para>
+
+<programlisting>
+CREATE TABLE t1 (
+ id INT,
+ year_col INT
+)
+PARTITION BY RANGE (year_col) (
+ PARTITION p0 VALUES LESS THAN (1991),
+ PARTITION p1 VALUES LESS THAN (1995),
+ PARTITION p2 VALUES LESS THAN (1999)
+);
+</programlisting>
+
+ <para>
+ You can add a new partition <literal>p3</literal> to this
+ table for storing values less then <literal>2002</literal>
+ as follows:
+ </para>
+
+<programlisting>
+ALTER TABLE t1 ADD PARTITION p3 VALUES LESS THAN (2002);
+</programlisting>
+
+ <para>
+ <emphasis role="bold">Note</emphasis>: You cannot use
+ <literal>ALTER TABLE</literal> to add partitions to a table
+ that is not not already partitioned.
+ </para>
+
+ <para>
+ <literal>DROP PARTITION</literal> can be used to drop one or
+ more <literal>RANGE</literal> or <literal>LIST</literal>
+ partitions. This command cannot be used with
+ <literal>HASH</literal> or <literal>KEY</literal>
+ partitions; instead, use <literal>COALESCE
+ PARTITION</literal> (see below). Any data that was stored in
+ the dropped partitions named in the
+ <replaceable>partition_names</replaceable> list is
+ discarded. For example, given the table
+ <literal>t1</literal> defined previously, you can drop the
+ partitions named <literal>p0</literal> and
+ <literal>p1</literal> as shown here:
+ </para>
+
+<programlisting>
+ALTER TABLE DROP PARTITION p0, p1;
+</programlisting>
+
+ <para>
+ <literal>ADD PARTITION</literal> and <literal>DROP
+ PARTITION</literal> do not currently support <literal>IF
+ [NOT] EXISTS</literal>. It is also not possible to rename a
+ partition or a partitioned table. Instead, if you wish to
+ rename a partition, you must drop and re-create the
+ partition; if you wish to rename a partitioned table, you
+ must instead drop all partitions, rename the table, then add
+ back the partitions that were dropped.
+ </para>
+
+ <para>
+ <literal>COALESCE PARTITION</literal> can be used with a
+ table that is partitioned by <literal>HASH</literal> or
+ <literal>KEY</literal> to reduce the number of partitions by
+ <replaceable>number</replaceable>. For example, suppose that
+ you have created table <literal>t2</literal> using the
+ following:
+ </para>
+
+<programlisting>
+CREATE TABLE t2 (
+ name VARCHAR (30),
+ started DATE
+)
+PARTITION BY HASH(YEAR(started))
+PARTITIONS (6);
+</programlisting>
+
+ <para>
+ You can reduce the number of partitions used by
+ <literal>t2</literal> from 6 to 4 using the following
+ command:
+ </para>
+
+<programlisting>
+ALTER TABLE t2 COALESCE PARTITION 2;
+</programlisting>
+
+ <para>
+ The data contained in the last
+ <replaceable>number</replaceable> partitions will be merged
+ into the remaining partitions. In this case, partitions 4
+ and 5 will be merged into the first 4 partitions (the
+ partitions numbered <literal>0</literal>,
+ <literal>1</literal>, <literal>2</literal>, and
+ <literal>3</literal>).
+ </para>
+
+ <para>
+ To change some but not all the partitions used by a
+ partitioned table, you can use <literal>REORGANISE
+ PARTITION</literal>. This command can be used in several
+ ways:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ To merge a set of partitions into a single partition.
+ This can be done by naming several partition in the
+ <replaceable>partition_names</replaceable> list and
+ supplying a single definition for
+ <replaceable>partition_definition</replaceable>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ To split an existing partition into several partitions.
+ You can accomplish this by naming a single partition for
+ <replaceable>partition_names</replaceable> and providing
+ multiple
+ <replaceable>partition_definitions</replaceable>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ To change the ranges for a subset of partitions defined
+ using <literal>VALUES LESS THAN</literal> or the value
+ lists for a subset of partitions defined using
+ <literal>VALUES IN</literal>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ For more detailed information about and examples of
+ <literal>ALTER TABLE REORGANISE PARTITION</literal>
+ commands, see <xref linkend="partitioning-management"/>.
+ </para>
+ </listitem>
+
</itemizedlist>
<para>
@@ -1319,6 +1483,49 @@
<replaceable>select_statement:</replaceable>
[IGNORE | REPLACE] [AS] SELECT ... (<replaceable>Some legal select statement</replaceable>)
+
+<replaceable>partition_options</replaceable>:
+ PARTITION BY
+ [LINEAR] HASH(<replaceable>expr</replaceable>)
+ | [LINEAR] KEY(<replaceable>column_list</replaceable>)
+ | RANGE(<replaceable>expr</replaceable>)
+ | LIST(<replaceable>column_list</replaceable>)
+ [PARTITIONS(<replaceable>num</replaceable>) ]
+ [ SUBPARTITION BY
+ [LINEAR] HASH(<replaceable>func_expr</replaceable>)
+ | [LINEAR] KEY(<replaceable>column_list</replaceable>)
+ [SUBPARTITIONS(<replaceable>num</replaceable>)]
+ ]
+ [(<replaceable>partition_definition</replaceable>), [(<replaceable>partition_definition</replaceable>)], ...]
+
+<replaceable>partition_definition</replaceable>:
+ PARTITION <replaceable>partition_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>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>]
</programlisting>
<remark>
@@ -2511,6 +2718,552 @@
</para>
</listitem>
+ <listitem>
+ <para>
+ <replaceable>partition_options</replaceable> can be used to
+ control partitioning of the table created with
+ <literal>CREATE TABLE</literal>, and if used, must contain
+ at a minimum a <literal>PARTITION BY</literal> clause. This
+ clause contains the function which is used to determine the
+ partition; the function returns an integer value ranging
+ from 1 to <replaceable>num</replaceable>, where
+ <replaceable>num</replaceable> is the number of partitions.
+ The choices available for this function which are available
+ in MySQL ¤t-series; are:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>HASH(<replaceable>func_expr</replaceable>)</literal>:
+ Hashes one or more columns to create a key for placing
+ and locating rows. <replaceable>func_expr</replaceable>
+ is an expression consisting of a user-supplied function,
+ taking as arguments one or more table columns. This can
+ be any legal MySQL expression that yields a single
+ integer value. For example, these are all valid
+ <literal>CREATE TABLE</literal> statements using
+ <literal>PARTITION BY HASH</literal>:
+ </para>
+
+<programlisting>
+CREATE TABLE t1 (col1 INT, col2 CHAR(5))
+ PARTITION BY HASH(col1);
+
+CREATE TABLE t1 (col1 INT, col2 CHAR(5))
+ PARTITION BY HASH( ORD(col2) );
+
+CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME)
+ PARTITION BY HASH ( YEAR(col3) );
+</programlisting>
+
+ <para>
+ When <literal>PARTITION BY HASH</literal> is used, MySQL
+ determines which partition of
+ <replaceable>num</replaceable> partitions to use based
+ on the modulus of the result of the user function. In
+ other words, for a user function
+ <replaceable>F</replaceable>, the partition in which the
+ record is stored is partition number
+ <replaceable>N</replaceable>, where
+ <literal><replaceable>N</replaceable> =
+ mod(<replaceable>F</replaceable>(<replaceable>column_list</replaceable>),
+ <replaceable>num</replaceable>)</literal>. For example,
+ suppose table <literal>t1</literal> is defined as
+ follows, so that it has 4 partitions:
+ </para>
+
+<programlisting>
+CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
+ PARTITION BY HASH( YEAR(col3) )
+ PARTITIONS(4);
+</programlisting>
+
+ <para>
+ If you insert a record into <literal>t1</literal> whose
+ <literal>col3</literal> value is
+ <literal>'2005-09-15'</literal>, then the partition in
+ which it is stored is determined as follows:
+ </para>
+
+<programlisting>
+MOD(YEAR('2005-09-01'),5)
+= MOD(2004,4)
+= 1
+</programlisting>
+
+ <para>
+ The user function is evaluated each time a record is
+ inserted, updated, or deleted. If this function returns
+ a result that does not correspond to the number of an
+ existing partition, then the query fails with an error.
+ </para>
+
+ <para>
+ The <literal>LINEAR</literal> keyword entails a somewhat
+ different algorithm: In this case, the value of
+ <replaceable>expr</replaceable> and the number of
+ partitions plus 1 is <literal>AND</literal>'ed together
+ (binary <literal>AND</literal>). If the result is
+ greater than or equal to the number of partitions, than
+ the result is <literal>AND</literal>'ed together with
+ one-half the number of partitions
+ <replaceable>num</replaceable> (rounded up to the next
+ integer value). In other words, for a user function
+ <replaceable>F</replaceable>, the partition in which the
+ record is stored is partition number
+ <replaceable>N</replaceable>, where
+ <replaceable>N</replaceable> is derived according to the
+ following rule:
+ </para>
+
+<programlisting>
+<replaceable>N</replaceable> = <replaceable>F</replaceable>(<replaceable>column_list</replaceable>) & (<replaceable>num</replaceable> + 1);
+
+if(<replaceable>N</replaceable> >= <replaceable>num</replaceable>)
+ <replaceable>N</replaceable> = <replaceable>N</replaceable> & CEILING(<replaceable>num</replaceable> / 2);
+</programlisting>
+
+ <para>
+ For example, suppose that the table
+ <literal>t1</literal>, using linear hash partitioning
+ and having 4 partitions, is created using this
+ statement:
+ </para>
+
+<programlisting>
+CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
+ PARTITION BY LINEAR HASH( YEAR(col3) )
+ PARTITIONS(4);
+</programlisting>
+
+ <para>
+ Now assume that you want to insert two records into
+ <literal>t1</literal> having the <literal>col3</literal>
+ column values <literal>'2003-10-19'</literal> and
+ <literal>'2004-04-14'</literal>. The partition number
+ for the first of these is determined as follows:
+ </para>
+
+<programlisting>
+<replaceable>N</replaceable> = YEAR('2003-10-19') & (4 + 1)
+ = 2003 & 5
+ = 1
+
+(<emphasis>1 >= 4 is FALSE: record stored in partition #1</emphasis>)
+</programlisting>
+
+ <para>
+ The number of the partition where the second record is
+ stored is calculated as shown here:
+ </para>
+
+<programlisting>
+<replaceable>N</replaceable> = YEAR('2004-04-14') & (4 + 1)
+ = 2004 & 5
+ = 4
+
+(<emphasis>4 >= 4 is TRUE: additional step required</emphasis>)
+
+<replaceable>N</replaceable> = 4 & CEILING(4 / 2)
+ = 4 & 2
+ = 0
+
+(<emphasis>0 >= 4 is FALSE: record stored in partition #0</emphasis>)
+</programlisting>
+
+ <para>
+ Linear hashing makes it less expensive to add, drop,
+ split, or merge partitions; however, the data
+ distribution is uneven compared to that produced by
+ normal hash or key partitioning. For more information,
+ see <xref linkend="partitioning"/>.
+ </para>
+
+ <para>
+ <emphasis role="bold">Note</emphasis>: If the table to
+ be partitioned has a <literal>UNIQUE</literal> key, then
+ any columns supplied as arguments to the
+ <literal>HASH</literal> user function or to the
+ <literal>KEY</literal>'s
+ <replaceable>column_list</replaceable> must be part of
+ that key.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>KEY(<replaceable>column_list</replaceable>)</literal>:
+ This is similar to <literal>HASH</literal>, except that
+ MySQL supplies the hashing function so as to guarantee
+ an even data distribution. (MySQL Cluster uses
+ <literal>MD5()</literal> for this purpose; for tables
+ using other storage engines, the server employs its own
+ internal hashing function.) The
+ <replaceable>column_list</replaceable> argument is
+ simply a list of table columns. This example shows a
+ simple table partitioned by key, with 4 partitions:
+ </para>
+
+<programlisting>
+CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
+ PARTITION BY KEY(col3)
+ PARTITIONS(4);
+</programlisting>
+
+ <para>
+ You can employ linear partitioning with tables which are
+ partitioned by key, by using the
+ <literal>LINEAR</literal> keyword. This has the same
+ effect as with tables that are partitioned by
+ <literal>HASH</literal>; that is, the partition number
+ is found using the <literal>&</literal> operator
+ rather than the modulus as previously described in this
+ section. This example uses linear partitioning by key to
+ distribute data between 5 partitions:
+ </para>
+
+<programlisting>
+CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
+ PARTITION BY LINEAR KEY(col3)
+ PARTITIONS(5);
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>RANGE</literal>: In this case,
+ <replaceable>expr</replaceable> shows a range of values
+ using a set of <literal>VALUES LESS THAN</literal> or
+ <literal>VALUES IN</literal> operators.
+ </para>
+
+ <para>
+ <literal>VALUES LESS THAN</literal> can be used with
+ either a literal value or an expression that evaluates
+ to a single value.
+ </para>
+
+ <para>
+ For example, suppose you have a table that you wish to
+ partition on a column containing year values, according
+ to the following scheme:
+ </para>
+
+ <informaltable pgwide="0" width="50em">
+ <colgroup width="50"/>
+ <colgroup width="50"/>
+ <tbody>
+ <row>
+ <entry><emphasis role="bold">Partition #</emphasis>:</entry>
+ <entry><emphasis role="bold">Years Range</emphasis>:</entry>
+ </row>
+ <row>
+ <entry>0</entry>
+ <entry>1990 and earlier</entry>
+ </row>
+ <row>
+ <entry>1</entry>
+ <entry>1991 - 1994</entry>
+ </row>
+ <row>
+ <entry>2</entry>
+ <entry>1995 - 1998</entry>
+ </row>
+ <row>
+ <entry>3</entry>
+ <entry>1999 - 2002</entry>
+ </row>
+ <row>
+ <entry>4</entry>
+ <entry>2003 - 2005</entry>
+ </row>
+ <row>
+ <entry>5</entry>
+ <entry>2006 and later</entry>
+ </row>
+ </tbody>
+ </informaltable>
+
+ <para>
+ A table implementing such a partitioning scheme can be
+ realised by the <literal>CREATE TABLE</literal>
+ statement shown here:
+ </para>
+
+<programlisting>
+CREATE TABLE t1 (
+ year_col INT,
+ some_data INT
+)
+PARTITION BY RANGE (year_col) (
+ PARTITION p0 VALUES LESS THAN (1991),
+ PARTITION p1 VALUES LESS THAN (1995),
+ PARTITION p2 VALUES LESS THAN (1999),
+ PARTITION p3 VALUES LESS THAN (2002),
+ PARTITION p4 VALUES LESS THAN (2006),
+ PARTITION p5 VALUES LESS THAN (MAX_VALUE)
+);
+</programlisting>
+
+ <para>
+ <literal>PARTITION ... VALUES LESS THAN ...</literal>
+ statements work in a consecutive fashion.
+ <literal>VALUES LESS THAN (MAX_VALUE)</literal> works to
+ specify <quote>leftover</quote> values that are greater
+ than the maximum value otherwise specified.
+ </para>
+
+ <para>
+ Note that <literal>VALUES LESS THAN</literal> clauses
+ work sequentially in a manner similar to that of the
+ <literal>case</literal> portions of a <literal>switch
+ ... case</literal> block (as found in many programming
+ languages such as C, Java, and PHP). That is, the
+ clauses must be arranged in such a way that the upper
+ limit specified in each successive <literal>VALUES LESS
+ THAN</literal> is greater than that of the previous one,
+ with the one referencing <literal>MAX_VALUE</literal>
+ coming last of all in the list.
+ </para>
+
+ <para>
+ <literal>VALUES IN</literal> is used with a list of
+ values to be matched. For instance, you could create a
+ partitioning scheme based on organising the United
+ States into 5 regions as follows:
+ </para>
+
+<programlisting>
+CREATE TABLE client_firms (
+ name VARCHAR(35),
+ state_abbr CHAR(2)
+)
+PARTITION BY RANGE (state_abbrev) (
+ PARTITION r0 VALUES IN ('AL','FL','GA','MD','MS',
+ 'NC','SC','TN','VA','WV'),
+ PARTITION r1 VALUES IN ('AR','AZ','CA','CO','LA',
+ 'NM','NV','OK','TX','UT'),
+ PARTITION r2 VALUES IN ('CT','DC','DE','MA','ME',
+ 'NH','NJ','NY','PA','RI','VT'),
+ PARTITION r3 VALUES IN ('IA','IL','IN','KS','KY',
+ 'MI','MN','MO','OH','WI'),
+ PARTITION r3 VALUES IN ('AK','HI','ID','MT','ND',
+ 'NE','OR','SD','WA','WY')
+);
+</programlisting>
+
+ <para>
+ (Since this table is partitioned using only
+ <literal>VALUES IN</literal> expressions, you could
+ instead use <literal>PARTITION BY LIST</literal>, rather
+ than <literal>PARTITION BY RANGE</literal>. See next
+ item.)
+ </para>
+
+ <para>
+ In either case — <literal>VALUES LESS
+ THAN</literal> or <literal>VALUES IN</literal> —
+ each partition is defined with the syntax
+ <literal>PARTITION
+ <replaceable>name</replaceable></literal>, where
+ <replaceable>name</replaceable> is the identifier for
+ the partition, followed by the <literal>VALUES
+ ...</literal> clause.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>LIST(<replaceable>expr</replaceable>)</literal>:
+ This is useful when assigning partitions based on a
+ table column with a restricted set of possible values,
+ such as a state or country code. In such a case, all
+ records pertaining to a certain state or country can be
+ assigned to a single partition, or a partition can be
+ reserved for a certain set of states or countries. It is
+ similar to <literal>RANGE</literal>, except that only
+ <literal>VALUES IN</literal> may be used to specify
+ allowable values for each partition.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The number of partitions may optionally be specified
+ with a <literal>PARTITIONS</literal> clause. If this is
+ used, it must be equal to the total number of
+ partitions.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A partition may optionally be divided into a number of
+ subpartitions. This can be indicated by using the
+ optional <literal>SUBPARTITION BY</literal> clause.
+ Subpartitioning may be done by <literal>HASH</literal>
+ or <literal>KEY</literal>. Either of these may be
+ <literal>LINEAR</literal>. These work in the same way as
+ previously described for the equivalent partitioning
+ types. (It is not possible to subpartition by
+ <literal>LIST</literal> or <literal>RANGE</literal>.)
+ </para>
+
+ <para>
+ The number of subpartitions can be indicated using the
+ <literal>SUBPARTITIONS</literal> keyword followed by an
+ integer value.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Each partition may be individually defined using a
+ <replaceable>partition_definition</replaceable> clause. The
+ individual parts making up this clause are as follows:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>PARTITION
+ <replaceable>partition_name</replaceable></literal>:
+ This specifies a logical name for the partition.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ An optional <literal>VALUES</literal> clause: This may
+ be either a <literal>VALUES LESS THAN</literal> or a
+ <literal>VALUES IN</literal> clause as discussed above,
+ and is used to determine which rows are to be stored in
+ this partition. See the discussion of partitioning types
+ for syntax examples.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ An optional <literal>COMMENT</literal> clause may be
+ used to describe the partition. The comment must be set
+ off in single quotes. Example:
+ </para>
+
+<programlisting>
+COMMENT = 'Data for the years previous to 1999'
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>DATA DIRECTORY</literal> and <literal>INDEX
+ DIRECTORY</literal> may be used to indicate the
+ directory where, respectively, the data and indexes for
+ this partition are to be stored. Both the
+ <filename><replaceable>data_dir</replaceable></filename>
+ and the
+ <filename><replaceable>index_dir</replaceable></filename>
+ must be absolute system paths. Example:
+ </para>
+
+<programlisting>
+DATA DIRECTORY = '<filename>/var/appdata/1999/data</filename>'
+INDEX DIRECTORY = '<filename>/var/appdata/1999/indexes</filename>'
+</programlisting>
+
+ <para>
+ <literal>DATA DIRECTORY</literal> and <literal>INDEX
+ DIRECTORY</literal> behave in the same way as in the
+ <literal>CREATE TABLE</literal> statement's
+ <replaceable>table_option</replaceable> clause as used
+ for tables under the <literal>MyISAM</literal> table
+ handler.
+ </para>
+
+ <para>
+ One data directory and one index directory may be
+ specified per partition.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>MAX_ROWS</literal> and
+ <literal>MIN_ROWS</literal> may be used to specify,
+ respectively, the maximum and minimum number of rows to
+ be stored in the partition. The values for
+ <replaceable>max_number_of_rows</replaceable> and
+ <replaceable>min_number_of_rows</replaceable> must be
+ positive integers.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The optional <literal>TABLESPACE</literal> clause may be
+ used to designate a tablespace for the partition. Used
+ for MySQL Cluster only.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The optional <literal>[STORAGE] ENGINE</literal> clause
+ causes the tables in this partition to be of the type
+ 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
+ using this option then the engine applying to the table
+ as a whole is used for this partition.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>NODEGROUP</literal> option can be used to
+ make this partition act as part of the node group
+ identified by <replaceable>node_group_id</replaceable>.
+ This option is applicable only to MySQL Cluster.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The partition definition may optionally contain one or
+ more <replaceable>subpartition_definition</replaceable>
+ clauses. Each of these consists at a minimum of the
+ <literal>SUBPARTITION
+ <replaceable>name</replaceable></literal>, where
+ <replaceable>name</replaceable> is an identifier for the
+ subpartition. Except for the replacement of the
+ <literal>PARTITION</literal> keyword with
+ <literal>SUBPARTITION</literal>, the syntax for a
+ subpartition definition is identical to that for a
+ partition definition.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Partitions can be modified, added to tables, and dropped
+ from tables. For more information about this, see
+ <xref linkend="alter-table"/>.
+ </para>
+
+ <para>
+ For a more information including a theoretical description
+ of partitioning in MySQL, see
+ <xref linkend="partitioning"/>.
+ </para>
+ </listitem>
+
</itemizedlist>
<indexterm>
@@ -12895,36 +13648,41 @@
<para>
<literal>ONE_SHOT</literal>
</para>
+
<para>
- This is not a server system variable, but it can be used
- to influence the effect of variables that set the character
+ This is not a server system variable, but it can be used to
+ influence the effect of variables that set the character
set, the collation, and the time zone.
- <literal>ONE_SHOT</literal> is primarily used for
- replication purposes: <command>mysqlbinlog</command> uses
- <literal>SET ONE_SHOT</literal> to temporarily modify the
- values of character set, collation, and timezone variables
+ <literal>ONE_SHOT</literal> is primarily used for
+ replication purposes: <command>mysqlbinlog</command> uses
+ <literal>SET ONE_SHOT</literal> to temporarily modify the
+ values of character set, collation, and timezone variables
to reflect at rollforward what they were originally.
- <literal>ONE_SHOT</literal> is available as of MySQL
- 4.1.3 and 5.0.
+ <literal>ONE_SHOT</literal> is available as of MySQL 4.1.3
+ and 5.0.
</para>
+
<para>
You cannot use <literal>ONE_SHOT</literal> with other than
the allowed set of variables; if you try, you get an error
like this:
+
<programlisting>
mysql> <userinput>SET ONE_SHOT max_allowed_packet = 1;</userinput>
ERROR 1382 (HY000): The 'SET ONE_SHOT' syntax is reserved for purposes internal to the MySQL server
</programlisting>
</para>
+
<para>
- If <literal>ONE_SHOT</literal> is used with the allowed
+ If <literal>ONE_SHOT</literal> is used with the allowed
variables, it changes the variables as requested, but
resets, after the next statement, all character set,
collation, and time zone-related server system variables.
- The only exception when resetting doesn't happen is when
- the next statement is a <literal>SET</literal> statement.
- In other words, resetting takes place after the next
+ The only exception when resetting doesn't happen is when the
+ next statement is a <literal>SET</literal> statement. In
+ other words, resetting takes place after the next
non-<literal>SET</literal> statement. Example:
+
<programlisting>
mysql> <userinput>SET ONE_SHOT character_set_connection = latin5;</userinput>
| Thread |
|---|
| • bk commit - mysqldoc@docsrva tree (jon:1.3555) | jon | 14 Sep |
