<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="alter-table-partition-operations">
      </a>
      15.1.9.1 ALTER TABLE Partition Operations
     </h4>
    </div>
   </div>
  </div>
  <p>
   Partitioning-related clauses for
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER
        TABLE
    </code>
   </a>
   can be used with partitioned tables for
        repartitioning, to add, drop, discard, import, merge, and split
        partitions, and to perform partitioning maintenance.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Simply using a
      <em class="replaceable">
       <code>
        partition_options
       </code>
      </em>
      clause with
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      on a
            partitioned table repartitions the table according to the
            partitioning scheme defined by the
      <em class="replaceable">
       <code>
        partition_options
       </code>
      </em>
      . This clause
            always begins with
      <code class="literal">
       PARTITION BY
      </code>
      , and
            follows the same syntax and other rules as apply to the
      <em class="replaceable">
       <code>
        partition_options
       </code>
      </em>
      clause for
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      (for more
            detailed information, see
      <a class="xref" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       Section 15.1.20, “CREATE TABLE Statement”
      </a>
      ),
            and can also be used to partition an existing table that is
            not already partitioned. For example, consider a
            (nonpartitioned) table defined as shown here:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa59230589"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span><span class="token punctuation">,</span>
    year_col <span class="token datatype">INT</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      This table can be partitioned by
      <code class="literal">
       HASH
      </code>
      ,
            using the
      <code class="literal">
       id
      </code>
      column as the partitioning
            key, into 8 partitions by means of this statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa5204747"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1
    <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span>
    <span class="token keyword">PARTITIONS</span> <span class="token number">8</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      MySQL supports an
      <code class="literal">
       ALGORITHM
      </code>
      option with
      <code class="literal">
       [SUB]PARTITION BY [LINEAR] KEY
      </code>
      .
      <code class="literal">
       ALGORITHM=1
      </code>
      causes the server to use the
            same key-hashing functions as MySQL 5.1 when computing the
            placement of rows in partitions;
      <code class="literal">
       ALGORITHM=2
      </code>
      means that the server employs
            the key-hashing functions implemented and used by default
            for new
      <code class="literal">
       KEY
      </code>
      partitioned tables in MySQL
            5.5 and later. (Partitioned tables created with the
            key-hashing functions employed in MySQL 5.5 and later cannot
            be used by a MySQL 5.1 server.) Not specifying the option
            has the same effect as using
      <code class="literal">
       ALGORITHM=2
      </code>
      .
            This option is intended for use chiefly when upgrading or
            downgrading
      <code class="literal">
       [LINEAR] KEY
      </code>
      partitioned
            tables between MySQL 5.1 and later MySQL versions, or for
            creating tables partitioned by
      <code class="literal">
       KEY
      </code>
      or
      <code class="literal">
       LINEAR KEY
      </code>
      on a MySQL 5.5 or later server
            which can be used on a MySQL 5.1 server.
     </p>
     <p>
      The table that results from using an
      <code class="literal">
       ALTER TABLE
            ... PARTITION BY
      </code>
      statement must follow the same
            rules as one created using
      <code class="literal">
       CREATE TABLE ...
            PARTITION BY
      </code>
      . This includes the rules governing
            the relationship between any unique keys (including any
            primary key) that the table might have, and the column or
            columns used in the partitioning expression, as discussed in
      <a class="xref" href="partitioning-limitations-partitioning-keys-unique-keys.html" title="26.6.1 Partitioning Keys, Primary Keys, and Unique Keys">
       Section 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”
      </a>
      .
            The
      <code class="literal">
       CREATE TABLE ... PARTITION BY
      </code>
      rules
            for specifying the number of partitions also apply to
      <code class="literal">
       ALTER TABLE ... PARTITION BY
      </code>
      .
     </p>
     <p>
      The
      <em class="replaceable">
       <code>
        partition_definition
       </code>
      </em>
      clause
            for
      <code class="literal">
       ALTER TABLE ADD PARTITION
      </code>
      supports
            the same options as the clause of the same name for the
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statement. (See
      <a class="xref" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       Section 15.1.20, “CREATE TABLE Statement”
      </a>
      , for the syntax and
            description.) Suppose that you have the partitioned table
            created as shown here:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa59440673"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span><span class="token punctuation">,</span>
    year_col <span class="token datatype">INT</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">RANGE</span> <span class="token punctuation">(</span>year_col<span class="token punctuation">)</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> p0 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1991</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p1 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1995</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p2 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1999</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      You can add a new partition
      <code class="literal">
       p3
      </code>
      to this
            table for storing values less than
      <code class="literal">
       2002
      </code>
      as follows:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa67205087"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ADD</span> <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span><span class="token keyword">PARTITION</span> p3 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2002</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      <code class="literal">
       DROP PARTITION
      </code>
      can be used to drop one or
            more
      <code class="literal">
       RANGE
      </code>
      or
      <code class="literal">
       LIST
      </code>
      partitions. This statement cannot be used with
      <code class="literal">
       HASH
      </code>
      or
      <code class="literal">
       KEY
      </code>
      partitions; instead, use
      <code class="literal">
       COALESCE
            PARTITION
      </code>
      (see later in this section). Any data
            that was stored in the dropped partitions named in the
      <em class="replaceable">
       <code>
        partition_names
       </code>
      </em>
      list is
            discarded. For example, given the table
      <code class="literal">
       t1
      </code>
      defined previously, you can drop the
            partitions named
      <code class="literal">
       p0
      </code>
      and
      <code class="literal">
       p1
      </code>
      as shown here:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa84302425"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">DROP</span> <span class="token keyword">PARTITION</span> p0<span class="token punctuation">,</span> p1<span class="token punctuation">;</span></code></pre>
     </div>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       <code class="literal">
        DROP PARTITION
       </code>
       does not work with
              tables that use the
       <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
        <code class="literal">
         NDB
        </code>
       </a>
       storage engine. See
       <a class="xref" href="partitioning-management-range-list.html" title="26.3.1 Management of RANGE and LIST Partitions">
        Section 26.3.1, “Management of RANGE and LIST Partitions”
       </a>
       , and
       <a class="xref" href="mysql-cluster-limitations.html" title="25.2.7 Known Limitations of NDB Cluster">
        Section 25.2.7, “Known Limitations of NDB Cluster”
       </a>
       .
      </p>
     </div>
     <p>
      <code class="literal">
       ADD PARTITION
      </code>
      and
      <code class="literal">
       DROP
            PARTITION
      </code>
      do not currently support
      <code class="literal">
       IF
            [NOT] EXISTS
      </code>
      .
     </p>
     <p>
      The
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        DISCARD
            PARTITION ... TABLESPACE
       </code>
      </a>
      and
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        IMPORT
            PARTITION ... TABLESPACE
       </code>
      </a>
      options extend the
      <a class="link" href="glossary.html#glos_transportable_tablespace" title="transportable tablespace">
       Transportable
            Tablespace
      </a>
      feature to individual
      <code class="literal">
       InnoDB
      </code>
      table partitions. Each
      <code class="literal">
       InnoDB
      </code>
      table partition has its own
            tablespace file (
      <code class="filename">
       .ibd
      </code>
      file). The
      <a class="link" href="glossary.html#glos_transportable_tablespace" title="transportable tablespace">
       Transportable
            Tablespace
      </a>
      feature makes it easy to copy the
            tablespaces from a running MySQL server instance to another
            running instance, or to perform a restore on the same
            instance. Both options take a comma-separated list of one or
            more partition names. For example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa38932456"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">DISCARD</span> <span class="token keyword">PARTITION</span> p2<span class="token punctuation">,</span> p3 <span class="token keyword">TABLESPACE</span><span class="token punctuation">;</span></code></pre>
     </div>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa52806786"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">IMPORT</span> <span class="token keyword">PARTITION</span> p2<span class="token punctuation">,</span> p3 <span class="token keyword">TABLESPACE</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      When running
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        DISCARD
            PARTITION ... TABLESPACE
       </code>
      </a>
      and
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        IMPORT
            PARTITION ... TABLESPACE
       </code>
      </a>
      on subpartitioned tables,
            both partition and subpartition names are allowed. When a
            partition name is specified, subpartitions of that partition
            are included.
     </p>
     <p>
      The
      <a class="link" href="glossary.html#glos_transportable_tablespace" title="transportable tablespace">
       Transportable
            Tablespace
      </a>
      feature also supports copying or restoring
            partitioned
      <code class="literal">
       InnoDB
      </code>
      tables. For more
            information, see
      <a class="xref" href="innodb-table-import.html" title="17.6.1.3 Importing InnoDB Tables">
       Section 17.6.1.3, “Importing InnoDB Tables”
      </a>
      .
     </p>
     <p>
      Renames of partitioned tables are supported. You can rename
            individual partitions indirectly using
      <code class="literal">
       ALTER TABLE
            ... REORGANIZE PARTITION
      </code>
      ; however, this operation
            copies the partition's data.
     </p>
     <p>
      To delete rows from selected partitions, use the
      <code class="literal">
       TRUNCATE PARTITION
      </code>
      option. This option
            takes a list of one or more comma-separated partition names.
            Consider the table
      <code class="literal">
       t1
      </code>
      created by this
            statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa87845934"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span><span class="token punctuation">,</span>
    year_col <span class="token datatype">INT</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">RANGE</span> <span class="token punctuation">(</span>year_col<span class="token punctuation">)</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> p0 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1991</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p1 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1995</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p2 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1999</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p3 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2003</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p4 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2007</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      To delete all rows from partition
      <code class="literal">
       p0
      </code>
      , use
            the following statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa6022614"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">TRUNCATE</span> <span class="token keyword">PARTITION</span> p0<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The statement just shown has the same effect as the
            following
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa66951934"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> year_col <span class="token operator">&lt;</span> <span class="token number">1991</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      When truncating multiple partitions, the partitions do not
            have to be contiguous: This can greatly simplify delete
            operations on partitioned tables that would otherwise
            require very complex
      <code class="literal">
       WHERE
      </code>
      conditions if
            done with
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      statements.
            For example, this statement deletes all rows from partitions
      <code class="literal">
       p1
      </code>
      and
      <code class="literal">
       p3
      </code>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa71732654"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">TRUNCATE</span> <span class="token keyword">PARTITION</span> p1<span class="token punctuation">,</span> p3<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      An equivalent
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      statement is shown here:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa95004165"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span>
    <span class="token punctuation">(</span>year_col <span class="token operator">&gt;=</span> <span class="token number">1991</span> <span class="token operator">AND</span> year_col <span class="token operator">&lt;</span> <span class="token number">1995</span><span class="token punctuation">)</span>
    <span class="token operator">OR</span>
    <span class="token punctuation">(</span>year_col <span class="token operator">&gt;=</span> <span class="token number">2003</span> <span class="token operator">AND</span> year_col <span class="token operator">&lt;</span> <span class="token number">2007</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      If you use the
      <code class="literal">
       ALL
      </code>
      keyword in place of
            the list of partition names, the statement acts on all table
            partitions.
     </p>
     <p>
      <code class="literal">
       TRUNCATE PARTITION
      </code>
      merely deletes rows;
            it does not alter the definition of the table itself, or of
            any of its partitions.
     </p>
     <p>
      To verify that the rows were dropped, check the
      <code class="literal">
       INFORMATION_SCHEMA.PARTITIONS
      </code>
      table,
            using a query such as this one:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa6889229"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> PARTITION_NAME<span class="token punctuation">,</span> TABLE_ROWS
    <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">PARTITIONS</span>
    <span class="token keyword">WHERE</span> <span class="token keyword">TABLE_NAME</span> <span class="token operator">=</span> <span class="token string">'t1'</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      <code class="literal">
       COALESCE PARTITION
      </code>
      can be used with a
            table that is partitioned by
      <code class="literal">
       HASH
      </code>
      or
      <code class="literal">
       KEY
      </code>
      to reduce the number of partitions by
      <em class="replaceable">
       <code>
        number
       </code>
      </em>
      . Suppose that you have
            created table
      <code class="literal">
       t2
      </code>
      as follows:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa45118315"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>
    <span class="token keyword">name</span> <span class="token datatype">VARCHAR</span> <span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    started <span class="token datatype">DATE</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span> <span class="token function">YEAR</span><span class="token punctuation">(</span>started<span class="token punctuation">)</span> <span class="token punctuation">)</span>
<span class="token keyword">PARTITIONS</span> <span class="token number">6</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      To reduce the number of partitions used by
      <code class="literal">
       t2
      </code>
      from 6 to 4, use the following
            statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa37004939"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">COALESCE</span> <span class="token keyword">PARTITION</span> <span class="token number">2</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The data contained in the last
      <em class="replaceable">
       <code>
        number
       </code>
      </em>
      partitions is merged into
            the remaining partitions. In this case, partitions 4 and 5
            are merged into the first 4 partitions (the partitions
            numbered 0, 1, 2, and 3).
     </p>
     <p>
      To change some but not all the partitions used by a
            partitioned table, you can use
      <code class="literal">
       REORGANIZE
            PARTITION
      </code>
      . This statement can be used in several
            ways:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         To merge a set of partitions into a single partition.
                This is done by naming several partitions in the
         <em class="replaceable">
          <code>
           partition_names
          </code>
         </em>
         list and
                supplying a single definition for
         <em class="replaceable">
          <code>
           partition_definition
          </code>
         </em>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         To split an existing partition into several partitions.
                Accomplish this by naming a single partition for
         <em class="replaceable">
          <code>
           partition_names
          </code>
         </em>
         and providing
                multiple
         <em class="replaceable">
          <code>
           partition_definitions
          </code>
         </em>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         To change the ranges for a subset of partitions defined
                using
         <code class="literal">
          VALUES LESS THAN
         </code>
         or the value
                lists for a subset of partitions defined using
         <code class="literal">
          VALUES IN
         </code>
         .
        </p>
       </li>
      </ul>
     </div>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       For partitions that have not been explicitly named, MySQL
              automatically provides the default names
       <code class="literal">
        p0
       </code>
       ,
       <code class="literal">
        p1
       </code>
       ,
       <code class="literal">
        p2
       </code>
       , and so on. The same is true with
              regard to subpartitions.
      </p>
     </div>
     <p>
      For more detailed information about and examples of
      <code class="literal">
       ALTER TABLE ... REORGANIZE PARTITION
      </code>
      statements, see
      <a class="xref" href="partitioning-management-range-list.html" title="26.3.1 Management of RANGE and LIST Partitions">
       Section 26.3.1, “Management of RANGE and LIST Partitions”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      To exchange a table partition or subpartition with a table,
            use the
      <code class="literal">
       ALTER TABLE ... EXCHANGE
            PARTITION
      </code>
      statement—that is, to move any
            existing rows in the partition or subpartition to the
            nonpartitioned table, and any existing rows in the
            nonpartitioned table to the table partition or subpartition.
     </p>
     <p>
      Once one or more columns have been added to a partitioned
            table using
      <code class="literal">
       ALGORITHM=INSTANT
      </code>
      , it is no
            longer possible to exchange partitions with that table.
     </p>
     <p>
      For usage information and examples, see
      <a class="xref" href="partitioning-management-exchange.html" title="26.3.3 Exchanging Partitions and Subpartitions with Tables">
       Section 26.3.3, “Exchanging Partitions and Subpartitions with Tables”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Several options provide partition maintenance and repair
            functionality analogous to that implemented for
            nonpartitioned tables by statements such as
      <a class="link" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
       <code class="literal">
        CHECK TABLE
       </code>
      </a>
      and
      <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
       <code class="literal">
        REPAIR TABLE
       </code>
      </a>
      (which are also
            supported for partitioned tables; for more information, see
      <a class="xref" href="table-maintenance-statements.html" title="15.7.3 Table Maintenance Statements">
       Section 15.7.3, “Table Maintenance Statements”
      </a>
      ). These
            include
      <code class="literal">
       ANALYZE PARTITION
      </code>
      ,
      <code class="literal">
       CHECK
            PARTITION
      </code>
      ,
      <code class="literal">
       OPTIMIZE PARTITION
      </code>
      ,
      <code class="literal">
       REBUILD PARTITION
      </code>
      , and
      <code class="literal">
       REPAIR
            PARTITION
      </code>
      . Each of these options takes a
      <em class="replaceable">
       <code>
        partition_names
       </code>
      </em>
      clause consisting
            of one or more names of partitions, separated by commas. The
            partitions must already exist in the target table. You can
            also use the
      <code class="literal">
       ALL
      </code>
      keyword in place of
      <em class="replaceable">
       <code>
        partition_names
       </code>
      </em>
      , in which case
            the statement acts on all table partitions. For more
            information and examples, see
      <a class="xref" href="partitioning-maintenance.html" title="26.3.4 Maintenance of Partitions">
       Section 26.3.4, “Maintenance of Partitions”
      </a>
      .
     </p>
     <p>
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      does not currently
            support per-partition optimization;
      <code class="literal">
       ALTER TABLE ...
            OPTIMIZE PARTITION
      </code>
      causes the entire table to
            rebuilt and analyzed, and an appropriate warning to be
            issued. (Bug #11751825, Bug #42822) To work around this
            problem, use
      <code class="literal">
       ALTER TABLE ... REBUILD
            PARTITION
      </code>
      and
      <code class="literal">
       ALTER TABLE ... ANALYZE
            PARTITION
      </code>
      instead.
     </p>
     <p>
      The
      <code class="literal">
       ANALYZE PARTITION
      </code>
      ,
      <code class="literal">
       CHECK
            PARTITION
      </code>
      ,
      <code class="literal">
       OPTIMIZE PARTITION
      </code>
      ,
            and
      <code class="literal">
       REPAIR PARTITION
      </code>
      options are not
            supported for tables which are not partitioned.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       REMOVE PARTITIONING
      </code>
      enables you to remove
            a table's partitioning without otherwise affecting the
            table or its data. This option can be combined with other
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      options such as
            those used to add, drop, or rename columns or indexes.
     </p>
    </li>
    <li class="listitem">
     <p>
      Using the
      <code class="literal">
       ENGINE
      </code>
      option with
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      changes the
            storage engine used by the table without affecting the
            partitioning. The target storage engine must provide its own
            partitioning handler. Only the
      <code class="literal">
       InnoDB
      </code>
      and
      <code class="literal">
       NDB
      </code>
      storage engines have native
            partitioning handlers.
     </p>
    </li>
   </ul>
  </div>
  <p>
   It is possible for an
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement to contain a
   <code class="literal">
    PARTITION BY
   </code>
   or
   <code class="literal">
    REMOVE PARTITIONING
   </code>
   clause in an addition to
        other alter specifications, but the
   <code class="literal">
    PARTITION
        BY
   </code>
   or
   <code class="literal">
    REMOVE PARTITIONING
   </code>
   clause
        must be specified last after any other specifications.
  </p>
  <p>
   The
   <code class="literal">
    ADD PARTITION
   </code>
   ,
   <code class="literal">
    DROP
        PARTITION
   </code>
   ,
   <code class="literal">
    COALESCE PARTITION
   </code>
   ,
   <code class="literal">
    REORGANIZE PARTITION
   </code>
   ,
   <code class="literal">
    ANALYZE
        PARTITION
   </code>
   ,
   <code class="literal">
    CHECK PARTITION
   </code>
   , and
   <code class="literal">
    REPAIR PARTITION
   </code>
   options cannot be combined
        with other alter specifications in a single
   <code class="literal">
    ALTER
        TABLE
   </code>
   , since the options just listed act on individual
        partitions. For more information, see
   <a class="xref" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
    Section 15.1.9.1, “ALTER TABLE Partition Operations”
   </a>
   .
  </p>
  <p>
   Only a single instance of any one of the following options can
        be used in a given
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement:
   <code class="literal">
    PARTITION BY
   </code>
   ,
   <code class="literal">
    ADD
        PARTITION
   </code>
   ,
   <code class="literal">
    DROP PARTITION
   </code>
   ,
   <code class="literal">
    TRUNCATE PARTITION
   </code>
   ,
   <code class="literal">
    EXCHANGE
        PARTITION
   </code>
   ,
   <code class="literal">
    REORGANIZE PARTITION
   </code>
   , or
   <code class="literal">
    COALESCE PARTITION
   </code>
   ,
   <code class="literal">
    ANALYZE
        PARTITION
   </code>
   ,
   <code class="literal">
    CHECK PARTITION
   </code>
   ,
   <code class="literal">
    OPTIMIZE PARTITION
   </code>
   ,
   <code class="literal">
    REBUILD
        PARTITION
   </code>
   ,
   <code class="literal">
    REMOVE PARTITIONING
   </code>
   .
  </p>
  <p>
   For example, the following two statements are invalid:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa95450996"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ANALYZE</span> <span class="token keyword">PARTITION</span> p1<span class="token punctuation">,</span> <span class="token keyword">ANALYZE</span> <span class="token keyword">PARTITION</span> p2<span class="token punctuation">;</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ANALYZE</span> <span class="token keyword">PARTITION</span> p1<span class="token punctuation">,</span> <span class="token keyword">CHECK</span> <span class="token keyword">PARTITION</span> p2<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In the first case, you can analyze partitions
   <code class="literal">
    p1
   </code>
   and
   <code class="literal">
    p2
   </code>
   of table
   <code class="literal">
    t1
   </code>
   concurrently using a single statement with
        a single
   <code class="literal">
    ANALYZE PARTITION
   </code>
   option that lists
        both of the partitions to be analyzed, like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa31259787"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ANALYZE</span> <span class="token keyword">PARTITION</span> p1<span class="token punctuation">,</span> p2<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In the second case, it is not possible to perform
   <code class="literal">
    ANALYZE
   </code>
   and
   <code class="literal">
    CHECK
   </code>
   operations on different partitions of the same table
        concurrently. Instead, you must issue two separate statements,
        like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13883955"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ANALYZE</span> <span class="token keyword">PARTITION</span> p1<span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">CHECK</span> <span class="token keyword">PARTITION</span> p2<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <code class="literal">
    REBUILD
   </code>
   operations are currently unsupported
        for subpartitions. The
   <code class="literal">
    REBUILD
   </code>
   keyword is
        expressly disallowed with subpartitions, and causes
   <code class="literal">
    ALTER TABLE
   </code>
   to fail with an error if so used.
  </p>
  <p>
   <code class="literal">
    CHECK PARTITION
   </code>
   and
   <code class="literal">
    REPAIR
        PARTITION
   </code>
   operations fail when the partition to be
        checked or repaired contains any duplicate key errors.
  </p>
  <p>
   For more information about these statements, see
   <a class="xref" href="partitioning-maintenance.html" title="26.3.4 Maintenance of Partitions">
    Section 26.3.4, “Maintenance of Partitions”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
