<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="partitioning-management-exchange">
      </a>
      26.3.3 Exchanging Partitions and Subpartitions with Tables
     </h3>
    </div>
   </div>
  </div>
  <p>
   In MySQL 8.4, it is possible to exchange a table
        partition or subpartition with a table using
   <code class="literal">
    ALTER
        TABLE
    <em class="replaceable">
     <code>
      pt
     </code>
    </em>
    EXCHANGE PARTITION
    <em class="replaceable">
     <code>
      p
     </code>
    </em>
    WITH TABLE
    <em class="replaceable">
     <code>
      nt
     </code>
    </em>
   </code>
   , where
   <em class="replaceable">
    <code>
     pt
    </code>
   </em>
   is the partitioned table and
   <em class="replaceable">
    <code>
     p
    </code>
   </em>
   is the partition or subpartition of
   <em class="replaceable">
    <code>
     pt
    </code>
   </em>
   to be exchanged with unpartitioned
        table
   <em class="replaceable">
    <code>
     nt
    </code>
   </em>
   , provided that the following
        statements are true:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Table
      <em class="replaceable">
       <code>
        nt
       </code>
      </em>
      is not itself
            partitioned.
     </p>
    </li>
    <li class="listitem">
     <p>
      Table
      <em class="replaceable">
       <code>
        nt
       </code>
      </em>
      is not a temporary
            table.
     </p>
    </li>
    <li class="listitem">
     <p>
      The structures of tables
      <em class="replaceable">
       <code>
        pt
       </code>
      </em>
      and
      <em class="replaceable">
       <code>
        nt
       </code>
      </em>
      are otherwise identical.
     </p>
    </li>
    <li class="listitem">
     <p>
      Table
      <code class="literal">
       nt
      </code>
      contains no foreign key
            references, and no other table has any foreign keys that
            refer to
      <code class="literal">
       nt
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      There are no rows in
      <em class="replaceable">
       <code>
        nt
       </code>
      </em>
      that lie
            outside the boundaries of the partition definition for
      <em class="replaceable">
       <code>
        p
       </code>
      </em>
      . This condition does not apply
            if
      <code class="literal">
       WITHOUT VALIDATION
      </code>
      is used.
     </p>
    </li>
    <li class="listitem">
     <p>
      Both tables must use the same character set and collation.
     </p>
    </li>
    <li class="listitem">
     <p>
      For
      <code class="literal">
       InnoDB
      </code>
      tables, both tables must use
            the same row format. To determine the row format of an
      <code class="literal">
       InnoDB
      </code>
      table, query
      <a class="link" href="information-schema-innodb-tables-table.html" title="28.4.23 The INFORMATION_SCHEMA INNODB_TABLES Table">
       <code class="literal">
        INFORMATION_SCHEMA.INNODB_TABLES
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Any partition-level
      <code class="literal">
       MAX_ROWS
      </code>
      setting for
      <code class="literal">
       p
      </code>
      must be the same as the table-level
      <code class="literal">
       MAX_ROWS
      </code>
      value set for
      <code class="literal">
       nt
      </code>
      . The setting for any partition-level
      <code class="literal">
       MIN_ROWS
      </code>
      setting for
      <code class="literal">
       p
      </code>
      must also be the same any table-level
      <code class="literal">
       MIN_ROWS
      </code>
      value set for
      <code class="literal">
       nt
      </code>
      .
     </p>
     <p>
      This is true in either case whether not
      <code class="literal">
       pt
      </code>
      has an explicit table-level
      <code class="literal">
       MAX_ROWS
      </code>
      or
      <code class="literal">
       MIN_ROWS
      </code>
      option in effect.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       AVG_ROW_LENGTH
      </code>
      cannot differ between
            the two tables
      <code class="literal">
       pt
      </code>
      and
      <code class="literal">
       nt
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       INDEX DIRECTORY
      </code>
      cannot differ between the
            table and the partition to be exchanged with it.
     </p>
    </li>
    <li class="listitem">
     <p>
      No table or partition
      <code class="literal">
       TABLESPACE
      </code>
      options
            can be used in either of the tables.
     </p>
    </li>
   </ol>
  </div>
  <p>
   In addition to the
   <a class="link" href="privileges-provided.html#priv_alter">
    <code class="literal">
     ALTER
    </code>
   </a>
   ,
   <a class="link" href="privileges-provided.html#priv_insert">
    <code class="literal">
     INSERT
    </code>
   </a>
   , and
   <a class="link" href="privileges-provided.html#priv_create">
    <code class="literal">
     CREATE
    </code>
   </a>
   privileges usually
        required for
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statements, you must have the
   <a class="link" href="privileges-provided.html#priv_drop">
    <code class="literal">
     DROP
    </code>
   </a>
   privilege to perform
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE ...
        EXCHANGE PARTITION
    </code>
   </a>
   .
  </p>
  <p>
   You should also be aware of the following effects of
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE ...
        EXCHANGE PARTITION
    </code>
   </a>
   :
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Executing
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER
            TABLE ... EXCHANGE PARTITION
       </code>
      </a>
      does not invoke any
            triggers on either the partitioned table or the table to be
            exchanged.
     </p>
    </li>
    <li class="listitem">
     <p>
      Any
      <code class="literal">
       AUTO_INCREMENT
      </code>
      columns in the
            exchanged table are reset.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       IGNORE
      </code>
      keyword has no effect when
            used with
      <code class="literal">
       ALTER TABLE ... EXCHANGE
            PARTITION
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   The syntax for
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE ...
        EXCHANGE PARTITION
    </code>
   </a>
   is shown here, where
   <em class="replaceable">
    <code>
     pt
    </code>
   </em>
   is the partitioned table,
   <em class="replaceable">
    <code>
     p
    </code>
   </em>
   is the partition (or subpartition)
        to be exchanged, and
   <em class="replaceable">
    <code>
     nt
    </code>
   </em>
   is the
        nonpartitioned table to be exchanged with
   <em class="replaceable">
    <code>
     p
    </code>
   </em>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa96494875"><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> <em class="replaceable">pt</em>
    <span class="token keyword">EXCHANGE</span> <span class="token keyword">PARTITION</span> <em class="replaceable">p</em>
    <span class="token keyword">WITH</span> <span class="token keyword">TABLE</span> <em class="replaceable">nt</em><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Optionally, you can append
   <code class="literal">
    WITH VALIDATION
   </code>
   or
   <code class="literal">
    WITHOUT VALIDATION
   </code>
   . When
   <code class="literal">
    WITHOUT
        VALIDATION
   </code>
   is specified, the
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE ...
        EXCHANGE PARTITION
    </code>
   </a>
   operation does not perform any
        row-by-row validation when exchanging a partition a
        nonpartitioned table, allowing database administrators to assume
        responsibility for ensuring that rows are within the boundaries
        of the partition definition.
   <code class="literal">
    WITH VALIDATION
   </code>
   is the default.
  </p>
  <p>
   One and only one partition or subpartition may be exchanged with
        one and only one nonpartitioned table in a single
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
        EXCHANGE PARTITION
    </code>
   </a>
   statement. To exchange multiple
        partitions or subpartitions, use multiple
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
        EXCHANGE PARTITION
    </code>
   </a>
   statements.
   <code class="literal">
    EXCHANGE
        PARTITION
   </code>
   may not 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. The
        partitioning and (if applicable) subpartitioning used by the
        partitioned table may be of any type or types supported in MySQL
        8.4.
  </p>
  <h4>
   <a name="idm46045083630256">
   </a>
   Exchanging a Partition with a Nonpartitioned Table
  </h4>
  <p>
   Suppose that a partitioned table
   <code class="literal">
    e
   </code>
   has been
        created and populated using the following SQL statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa73585392"><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> e <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    fname <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>
    lname <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>
    <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">RANGE</span> <span class="token punctuation">(</span>id<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">50</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">100</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">150</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 keyword">MAXVALUE</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> e <span class="token keyword">VALUES</span>
    <span class="token punctuation">(</span><span class="token number">1669</span><span class="token punctuation">,</span> <span class="token string">"Jim"</span><span class="token punctuation">,</span> <span class="token string">"Smith"</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token number">337</span><span class="token punctuation">,</span> <span class="token string">"Mary"</span><span class="token punctuation">,</span> <span class="token string">"Jones"</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token number">16</span><span class="token punctuation">,</span> <span class="token string">"Frank"</span><span class="token punctuation">,</span> <span class="token string">"White"</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token number">2005</span><span class="token punctuation">,</span> <span class="token string">"Linda"</span><span class="token punctuation">,</span> <span class="token string">"Black"</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Now we create a nonpartitioned copy of
   <code class="literal">
    e
   </code>
   named
   <code class="literal">
    e2
   </code>
   . This can be done using the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   client as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa58190941"><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 prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> e2 <span class="token operator">LIKE</span> e<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.04 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> e2 <span class="token keyword">REMOVE</span> <span class="token keyword">PARTITIONING</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.07 sec)</span>
<span class="token output">Records: 0  Duplicates: 0  Warnings: 0</span></code></pre>
  </div>
  <p>
   You can see which partitions in table
   <code class="literal">
    e
   </code>
   contain rows by querying the Information Schema
   <a class="link" href="information-schema-partitions-table.html" title="28.3.21 The INFORMATION_SCHEMA PARTITIONS Table">
    <code class="literal">
     PARTITIONS
    </code>
   </a>
   table, like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15024532"><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 prompt">mysql&gt;</span> <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">'e'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> PARTITION_NAME <span class="token punctuation">|</span> TABLE_ROWS <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> p0             <span class="token punctuation">|</span>          1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p1             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p2             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p3             <span class="token punctuation">|</span>          3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span></code></pre>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    For partitioned
    <code class="literal">
     InnoDB
    </code>
    tables, the row
          count given in the
    <code class="literal">
     TABLE_ROWS
    </code>
    column of the
          Information Schema
    <a class="link" href="information-schema-partitions-table.html" title="28.3.21 The INFORMATION_SCHEMA PARTITIONS Table">
     <code class="literal">
      PARTITIONS
     </code>
    </a>
    table is only an estimated value used in SQL optimization, and
          is not always exact.
   </p>
  </div>
  <p>
   To exchange partition
   <code class="literal">
    p0
   </code>
   in table
   <code class="literal">
    e
   </code>
   with table
   <code class="literal">
    e2
   </code>
   , you can
        use
   <a class="link" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
    <code class="literal">
     ALTER
        TABLE
    </code>
   </a>
   , as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63138188"><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 prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> e <span class="token keyword">EXCHANGE</span> <span class="token keyword">PARTITION</span> p0 <span class="token keyword">WITH</span> <span class="token keyword">TABLE</span> e2<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.04 sec)</span></code></pre>
  </div>
  <p>
   More precisely, the statement just issued causes any rows found
        in the partition to be swapped with those found in the table.
        You can observe how this has happened by querying the
        Information Schema
   <a class="link" href="information-schema-partitions-table.html" title="28.3.21 The INFORMATION_SCHEMA PARTITIONS Table">
    <code class="literal">
     PARTITIONS
    </code>
   </a>
   table, as before. The table row that was previously found in
        partition
   <code class="literal">
    p0
   </code>
   is no longer present:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13202485"><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 prompt">mysql&gt;</span> <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">'e'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> PARTITION_NAME <span class="token punctuation">|</span> TABLE_ROWS <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> p0             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p1             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p2             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p3             <span class="token punctuation">|</span>          3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">4 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   If you query table
   <code class="literal">
    e2
   </code>
   , you can see that the
   <span class="quote">
    “
    <span class="quote">
     missing
    </span>
    ”
   </span>
   row can now be found there:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63809957"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> e2<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> fname <span class="token punctuation">|</span> lname <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 16 <span class="token punctuation">|</span> Frank <span class="token punctuation">|</span> White <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   The table to be exchanged with the partition does not
        necessarily have to be empty. To demonstrate this, we first
        insert a new row into table
   <code class="literal">
    e
   </code>
   , making sure
        that this row is stored in partition
   <code class="literal">
    p0
   </code>
   by
        choosing an
   <code class="literal">
    id
   </code>
   column value that is less than
        50, and verifying this afterward by querying the
   <a class="link" href="information-schema-partitions-table.html" title="28.3.21 The INFORMATION_SCHEMA PARTITIONS Table">
    <code class="literal">
     PARTITIONS
    </code>
   </a>
   table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa70809248"><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 prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> e <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">41</span><span class="token punctuation">,</span> <span class="token string">"Michael"</span><span class="token punctuation">,</span> <span class="token string">"Green"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.05 sec)</span>

<span class="token prompt">mysql&gt;</span> <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">'e'</span><span class="token punctuation">;</span>            
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> PARTITION_NAME <span class="token punctuation">|</span> TABLE_ROWS <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> p0             <span class="token punctuation">|</span>          1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p1             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p2             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p3             <span class="token punctuation">|</span>          3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">4 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   Now we once again exchange partition
   <code class="literal">
    p0
   </code>
   with
        table
   <code class="literal">
    e2
   </code>
   using the same
   <a class="link" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
    <code class="literal">
     ALTER
        TABLE
    </code>
   </a>
   statement as previously:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa70205268"><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 prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> e <span class="token keyword">EXCHANGE</span> <span class="token keyword">PARTITION</span> p0 <span class="token keyword">WITH</span> <span class="token keyword">TABLE</span> e2<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.28 sec)</span></code></pre>
  </div>
  <p>
   The output of the following queries shows that the table row
        that was stored in partition
   <code class="literal">
    p0
   </code>
   and the table
        row that was stored in table
   <code class="literal">
    e2
   </code>
   , prior to
        issuing the
   <a class="link" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
    <code class="literal">
     ALTER
        TABLE
    </code>
   </a>
   statement, have now switched places:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28756420"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> e<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id   <span class="token punctuation">|</span> fname <span class="token punctuation">|</span> lname <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>   16 <span class="token punctuation">|</span> Frank <span class="token punctuation">|</span> White <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 1669 <span class="token punctuation">|</span> Jim   <span class="token punctuation">|</span> Smith <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  337 <span class="token punctuation">|</span> Mary  <span class="token punctuation">|</span> Jones <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2005 <span class="token punctuation">|</span> Linda <span class="token punctuation">|</span> Black <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">4 rows in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <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">'e'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> PARTITION_NAME <span class="token punctuation">|</span> TABLE_ROWS <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> p0             <span class="token punctuation">|</span>          1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p1             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p2             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p3             <span class="token punctuation">|</span>          3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">4 rows in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> e2<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> fname   <span class="token punctuation">|</span> lname <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 41 <span class="token punctuation">|</span> Michael <span class="token punctuation">|</span> Green <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <h4>
   <a name="idm46045083575072">
   </a>
   Nonmatching Rows
  </h4>
  <p>
   You should keep in mind that any rows found in the
        nonpartitioned table prior to issuing the
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE ...
        EXCHANGE PARTITION
    </code>
   </a>
   statement must meet the conditions
        required for them to be stored in the target partition;
        otherwise, the statement fails. To see how this occurs, first
        insert a row into
   <code class="literal">
    e2
   </code>
   that is outside the
        boundaries of the partition definition for partition
   <code class="literal">
    p0
   </code>
   of table
   <code class="literal">
    e
   </code>
   . For
        example, insert a row with an
   <code class="literal">
    id
   </code>
   column value
        that is too large; then, try to exchange the table with the
        partition again:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56607812"><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 prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> e2 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">51</span><span class="token punctuation">,</span> <span class="token string">"Ellen"</span><span class="token punctuation">,</span> <span class="token string">"McDonald"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.08 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> e <span class="token keyword">EXCHANGE</span> <span class="token keyword">PARTITION</span> p0 <span class="token keyword">WITH</span> <span class="token keyword">TABLE</span> e2<span class="token punctuation">;</span>
<span class="token output">ERROR 1707 (HY000)<span class="token punctuation">:</span> Found row that does not match the partition</span></code></pre>
  </div>
  <p>
   Only the
   <code class="literal">
    WITHOUT VALIDATION
   </code>
   option would
        permit this operation to succeed:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa73145168"><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 prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> e <span class="token keyword">EXCHANGE</span> <span class="token keyword">PARTITION</span> p0 <span class="token keyword">WITH</span> <span class="token keyword">TABLE</span> e2 <span class="token keyword">WITHOUT</span> <span class="token keyword">VALIDATION</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.02 sec)</span></code></pre>
  </div>
  <p>
   When a partition is exchanged with a table that contains rows
        that do not match the partition definition, it is the
        responsibility of the database administrator to fix the
        non-matching rows, which can be performed using
   <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    <code class="literal">
     REPAIR TABLE
    </code>
   </a>
   or
   <a class="link" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
    <code class="literal">
     ALTER
        TABLE ... REPAIR PARTITION
    </code>
   </a>
   .
  </p>
  <h4>
   <a name="idm46045083560400">
   </a>
   Exchanging Partitions Without Row-By-Row Validation
  </h4>
  <p>
   To avoid time consuming validation when exchanging a partition
        with a table that has many rows, it is possible to skip the
        row-by-row validation step by appending
   <code class="literal">
    WITHOUT
        VALIDATION
   </code>
   to the
   <a class="link" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
    <code class="literal">
     ALTER
        TABLE ... EXCHANGE PARTITION
    </code>
   </a>
   statement.
  </p>
  <p>
   The following example compares the difference between execution
        times when exchanging a partition with a nonpartitioned table,
        with and without validation. The partitioned table (table
   <code class="literal">
    e
   </code>
   ) contains two partitions of 1 million rows
        each. The rows in p0 of table e are removed and p0 is exchanged
        with a nonpartitioned table of 1 million rows. The
   <code class="literal">
    WITH
        VALIDATION
   </code>
   operation takes 0.74 seconds. By
        comparison, the
   <code class="literal">
    WITHOUT VALIDATION
   </code>
   operation
        takes 0.01 seconds.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa24552466"><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 comment" spellcheck="true"># Create a partitioned table with 1 million rows in each partition</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> e <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    fname <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>
    lname <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>
    <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">RANGE</span> <span class="token punctuation">(</span>id<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">1000001</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">2000001</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> e<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">|</span> COUNT(*) <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>  2000000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.27 sec)</span>

<span class="token comment" spellcheck="true"># View the rows in each partition</span>

<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">'e'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> PARTITION_NAME <span class="token punctuation">|</span> TABLE_ROWS  <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> p0             <span class="token punctuation">|</span>     1000000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p1             <span class="token punctuation">|</span>     1000000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span>

<span class="token comment" spellcheck="true"># Create a nonpartitioned table of the same structure and populate it with 1 million rows</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> e2 <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    fname <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>
    lname <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><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> e2<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> COUNT(*) <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>  1000000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.24 sec)</span>

<span class="token comment" spellcheck="true"># Create another nonpartitioned table of the same structure and populate it with 1 million rows</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> e3 <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    fname <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>
    lname <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><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> e3<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> COUNT(*) <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>  1000000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.25 sec)</span>

<span class="token comment" spellcheck="true"># Drop the rows from p0 of table e</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> e <span class="token keyword">WHERE</span> id <span class="token operator">&lt;</span> <span class="token number">1000001</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1000000 rows affected (5.55 sec)</span>

<span class="token comment" spellcheck="true"># Confirm that there are no rows in partition p0</span>

<span class="token prompt">mysql&gt;</span> <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">'e'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> PARTITION_NAME <span class="token punctuation">|</span> TABLE_ROWS <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> p0             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p1             <span class="token punctuation">|</span>    1000000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span>

<span class="token comment" spellcheck="true"># Exchange partition p0 of table e with the table e2 'WITH VALIDATION'</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> e <span class="token keyword">EXCHANGE</span> <span class="token keyword">PARTITION</span> p0 <span class="token keyword">WITH</span> <span class="token keyword">TABLE</span> e2 <span class="token keyword">WITH</span> <span class="token keyword">VALIDATION</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.74 sec)</span>

<span class="token comment" spellcheck="true"># Confirm that the partition was exchanged with table e2</span>

<span class="token prompt">mysql&gt;</span> <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">'e'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> PARTITION_NAME <span class="token punctuation">|</span> TABLE_ROWS <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> p0             <span class="token punctuation">|</span>    1000000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p1             <span class="token punctuation">|</span>    1000000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span>

<span class="token comment" spellcheck="true"># Once again, drop the rows from p0 of table e</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> e <span class="token keyword">WHERE</span> id <span class="token operator">&lt;</span> <span class="token number">1000001</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1000000 rows affected (5.55 sec)</span>

<span class="token comment" spellcheck="true"># Confirm that there are no rows in partition p0</span>

<span class="token prompt">mysql&gt;</span> <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">'e'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> PARTITION_NAME <span class="token punctuation">|</span> TABLE_ROWS <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> p0             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p1             <span class="token punctuation">|</span>    1000000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span>

<span class="token comment" spellcheck="true"># Exchange partition p0 of table e with the table e3 'WITHOUT VALIDATION'</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> e <span class="token keyword">EXCHANGE</span> <span class="token keyword">PARTITION</span> p0 <span class="token keyword">WITH</span> <span class="token keyword">TABLE</span> e3 <span class="token keyword">WITHOUT</span> <span class="token keyword">VALIDATION</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.01 sec)</span>

<span class="token comment" spellcheck="true"># Confirm that the partition was exchanged with table e3</span>

<span class="token prompt">mysql&gt;</span> <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">'e'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> PARTITION_NAME <span class="token punctuation">|</span> TABLE_ROWS <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> p0             <span class="token punctuation">|</span>    1000000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p1             <span class="token punctuation">|</span>    1000000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   If a partition is exchanged with a table that contains rows that
        do not match the partition definition, it is the responsibility
        of the database administrator to fix the non-matching rows,
        which can be performed using
   <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    <code class="literal">
     REPAIR
        TABLE
    </code>
   </a>
   or
   <a class="link" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
    <code class="literal">
     ALTER
        TABLE ... REPAIR PARTITION
    </code>
   </a>
   .
  </p>
  <h4>
   <a name="idm46045083544320">
   </a>
   Exchanging a Subpartition with a Nonpartitioned Table
  </h4>
  <p>
   You can also exchange a subpartition of a subpartitioned table
        (see
   <a class="xref" href="partitioning-subpartitions.html" title="26.2.6 Subpartitioning">
    Section 26.2.6, “Subpartitioning”
   </a>
   ) with a
        nonpartitioned table using an
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE ...
        EXCHANGE PARTITION
    </code>
   </a>
   statement. In the following
        example, we first create a table
   <code class="literal">
    es
   </code>
   that is
        partitioned by
   <code class="literal">
    RANGE
   </code>
   and subpartitioned by
   <code class="literal">
    KEY
   </code>
   , populate this table as we did table
   <code class="literal">
    e
   </code>
   , and then create an empty, nonpartitioned
        copy
   <code class="literal">
    es2
   </code>
   of the table, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71794143"><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 prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> es <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     fname <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>
<span class="token prompt">    -&gt;</span>     lname <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 prompt">    -&gt;</span> <span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">RANGE</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">SUBPARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>lname<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">SUBPARTITIONS</span> <span class="token number">2</span> <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</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">50</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</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">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</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">150</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</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 keyword">MAXVALUE</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (2.76 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> es <span class="token keyword">VALUES</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token number">1669</span><span class="token punctuation">,</span> <span class="token string">"Jim"</span><span class="token punctuation">,</span> <span class="token string">"Smith"</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token number">337</span><span class="token punctuation">,</span> <span class="token string">"Mary"</span><span class="token punctuation">,</span> <span class="token string">"Jones"</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token number">16</span><span class="token punctuation">,</span> <span class="token string">"Frank"</span><span class="token punctuation">,</span> <span class="token string">"White"</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token number">2005</span><span class="token punctuation">,</span> <span class="token string">"Linda"</span><span class="token punctuation">,</span> <span class="token string">"Black"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 4 rows affected (0.04 sec)</span>
<span class="token output">Records: 4  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> es2 <span class="token operator">LIKE</span> es<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (1.27 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> es2 <span class="token keyword">REMOVE</span> <span class="token keyword">PARTITIONING</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.70 sec)</span>
<span class="token output">Records: 0  Duplicates: 0  Warnings: 0</span></code></pre>
  </div>
  <p>
   Although we did not explicitly name any of the subpartitions
        when creating table
   <code class="literal">
    es
   </code>
   , we can obtain
        generated names for these by including the
   <code class="literal">
    SUBPARTITION_NAME
   </code>
   column of the
   <a class="link" href="information-schema-partitions-table.html" title="28.3.21 The INFORMATION_SCHEMA PARTITIONS Table">
    <code class="literal">
     PARTITIONS
    </code>
   </a>
   table from
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   when selecting from that
        table, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa46790338"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> PARTITION_NAME<span class="token punctuation">,</span> SUBPARTITION_NAME<span class="token punctuation">,</span> TABLE_ROWS
<span class="token prompt">    -&gt;</span>     <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">PARTITIONS</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> <span class="token keyword">TABLE_NAME</span> <span class="token operator">=</span> <span class="token string">'es'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> PARTITION_NAME <span class="token punctuation">|</span> SUBPARTITION_NAME <span class="token punctuation">|</span> TABLE_ROWS <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> p0             <span class="token punctuation">|</span> p0sp0             <span class="token punctuation">|</span>          1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p0             <span class="token punctuation">|</span> p0sp1             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p1             <span class="token punctuation">|</span> p1sp0             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p1             <span class="token punctuation">|</span> p1sp1             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p2             <span class="token punctuation">|</span> p2sp0             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p2             <span class="token punctuation">|</span> p2sp1             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p3             <span class="token punctuation">|</span> p3sp0             <span class="token punctuation">|</span>          3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p3             <span class="token punctuation">|</span> p3sp1             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">8 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   The following
   <a class="link" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
    <code class="literal">
     ALTER
        TABLE
    </code>
   </a>
   statement exchanges subpartition
   <code class="literal">
    p3sp0
   </code>
   in table
   <code class="literal">
    es
   </code>
   with the
        nonpartitioned table
   <code class="literal">
    es2
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa18801498"><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 prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> es <span class="token keyword">EXCHANGE</span> <span class="token keyword">PARTITION</span> p3sp0 <span class="token keyword">WITH</span> <span class="token keyword">TABLE</span> es2<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.29 sec)</span></code></pre>
  </div>
  <p>
   You can verify that the rows were exchanged by issuing the
        following queries:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa36223974"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> PARTITION_NAME<span class="token punctuation">,</span> SUBPARTITION_NAME<span class="token punctuation">,</span> TABLE_ROWS
<span class="token prompt">    -&gt;</span>     <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">PARTITIONS</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> <span class="token keyword">TABLE_NAME</span> <span class="token operator">=</span> <span class="token string">'es'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> PARTITION_NAME <span class="token punctuation">|</span> SUBPARTITION_NAME <span class="token punctuation">|</span> TABLE_ROWS <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> p0             <span class="token punctuation">|</span> p0sp0             <span class="token punctuation">|</span>          1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p0             <span class="token punctuation">|</span> p0sp1             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p1             <span class="token punctuation">|</span> p1sp0             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p1             <span class="token punctuation">|</span> p1sp1             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p2             <span class="token punctuation">|</span> p2sp0             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p2             <span class="token punctuation">|</span> p2sp1             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p3             <span class="token punctuation">|</span> p3sp0             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p3             <span class="token punctuation">|</span> p3sp1             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">8 rows in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> es2<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> id   <span class="token punctuation">|</span> fname <span class="token punctuation">|</span> lname <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 1669 <span class="token punctuation">|</span> Jim   <span class="token punctuation">|</span> Smith <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  337 <span class="token punctuation">|</span> Mary  <span class="token punctuation">|</span> Jones <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2005 <span class="token punctuation">|</span> Linda <span class="token punctuation">|</span> Black <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">3 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   If a table is subpartitioned, you can exchange only a
        subpartition of the table—not an entire
        partition—with an unpartitioned table, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3224736"><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 prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> es <span class="token keyword">EXCHANGE</span> <span class="token keyword">PARTITION</span> p3 <span class="token keyword">WITH</span> <span class="token keyword">TABLE</span> es2<span class="token punctuation">;</span>
<span class="token output">ERROR 1704 (HY000)<span class="token punctuation">:</span> Subpartitioned table, use subpartition instead of partition</span></code></pre>
  </div>
  <p>
   Table structures are compared in a strict fashion; the number,
        order, names, and types of columns and indexes of the
        partitioned table and the nonpartitioned table must match
        exactly. In addition, both tables must use the same storage
        engine:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa62489758"><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 prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> es3 <span class="token operator">LIKE</span> e<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (1.31 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> es3 <span class="token keyword">REMOVE</span> <span class="token keyword">PARTITIONING</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.53 sec)</span>
<span class="token output">Records: 0  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> es3\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
       Table<span class="token punctuation">:</span> es3
Create Table<span class="token punctuation">:</span> CREATE TABLE `es3` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
</span><span class="token output">1 row in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> es3 <span class="token keyword">ENGINE</span> <span class="token operator">=</span> MyISAM<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.15 sec)</span>
<span class="token output">Records: 0  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> es <span class="token keyword">EXCHANGE</span> <span class="token keyword">PARTITION</span> p3sp0 <span class="token keyword">WITH</span> <span class="token keyword">TABLE</span> es3<span class="token punctuation">;</span>
<span class="token output">ERROR 1497 (HY000)<span class="token punctuation">:</span> The mix of handlers in the partitions is not allowed in this version of MySQL</span></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    ALTER TABLE ... ENGINE ...
   </code>
   statement in
        this example works because the previous
   <code class="literal">
    ALTER
        TABLE
   </code>
   removed the partitioning from table
   <code class="literal">
    es3
   </code>
   .
  </p>
 </div>
 <br/>
</div>
