<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="partitioning-management">
      </a>
      26.3 Partition Management
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="partitioning-management-range-list.html">
       26.3.1 Management of RANGE and LIST Partitions
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="partitioning-management-hash-key.html">
       26.3.2 Management of HASH and KEY Partitions
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="partitioning-management-exchange.html">
       26.3.3 Exchanging Partitions and Subpartitions with Tables
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="partitioning-maintenance.html">
       26.3.4 Maintenance of Partitions
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="partitioning-info.html">
       26.3.5 Obtaining Information About Partitions
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045083969888">
  </a>
  <a class="indexterm" name="idm46045083968816">
  </a>
  <a class="indexterm" name="idm46045083967328">
  </a>
  <a class="indexterm" name="idm46045083965840">
  </a>
  <a class="indexterm" name="idm46045083964352">
  </a>
  <a class="indexterm" name="idm46045083962864">
  </a>
  <p>
   There are a number of ways using SQL statements to modify
      partitioned tables; it is possible to add, drop, redefine, merge,
      or split existing partitions using the partitioning extensions 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
    </code>
   </a>
   statement. There are also ways to obtain
      information about partitioned tables and partitions. We discuss
      these topics in the sections that follow.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For information about partition management in tables
          partitioned by
      <code class="literal">
       RANGE
      </code>
      or
      <code class="literal">
       LIST
      </code>
      , see
      <a class="xref" href="partitioning-management-range-list.html" title="26.3.1 Management of RANGE and LIST Partitions">
       Section 26.3.1, “Management of RANGE and LIST Partitions”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      For a discussion of managing
      <code class="literal">
       HASH
      </code>
      and
      <code class="literal">
       KEY
      </code>
      partitions, see
      <a class="xref" href="partitioning-management-hash-key.html" title="26.3.2 Management of HASH and KEY Partitions">
       Section 26.3.2, “Management of HASH and KEY Partitions”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      See
      <a class="xref" href="partitioning-info.html" title="26.3.5 Obtaining Information About Partitions">
       Section 26.3.5, “Obtaining Information About Partitions”
      </a>
      , for a discussion of
          mechanisms provided in MySQL 8.4 for obtaining
          information about partitioned tables and partitions.
     </p>
    </li>
    <li class="listitem">
     <p>
      For a discussion of performing maintenance operations on
          partitions, see
      <a class="xref" href="partitioning-maintenance.html" title="26.3.4 Maintenance of Partitions">
       Section 26.3.4, “Maintenance of Partitions”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    All partitions of a partitioned table must have the same number
        of subpartitions; it is not possible to change the
        subpartitioning once the table has been created.
   </p>
  </div>
  <p>
   To change a table's partitioning scheme, it is necessary only
      to use 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 with a
   <em class="replaceable">
    <code>
     partition_options
    </code>
   </em>
   option, which has the
      same syntax as that as used with
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE
      TABLE
    </code>
   </a>
   for creating a partitioned table; this option
      (also) always begins with the keywords
   <code class="literal">
    PARTITION
      BY
   </code>
   . Suppose that the following
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement was used to
      create a table that is partitioned by range:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa30420102"><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> trb3 <span class="token punctuation">(</span>id <span class="token datatype">INT</span><span class="token punctuation">,</span> <span class="token keyword">name</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">,</span> purchased <span class="token datatype">DATE</span><span class="token punctuation">)</span>
    <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">RANGE</span><span class="token punctuation">(</span> <span class="token function">YEAR</span><span class="token punctuation">(</span>purchased<span class="token punctuation">)</span> <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">1990</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
        <span class="token keyword">PARTITION</span> p1 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1995</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
        <span class="token keyword">PARTITION</span> p2 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
        <span class="token keyword">PARTITION</span> p3 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2005</span><span class="token punctuation">)</span>
    <span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To repartition this table so that it is partitioned by key into
      two partitions using the
   <code class="literal">
    id
   </code>
   column value as the
      basis for the key, you can use this statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa85425551"><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> trb3 <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span> <span class="token keyword">PARTITIONS</span> <span class="token number">2</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   This has the same effect on the structure of the table as dropping
      the table and re-creating it using
   <code class="literal">
    CREATE TABLE trb3
      PARTITION BY KEY(id) PARTITIONS 2;
   </code>
   .
  </p>
  <p>
   <code class="literal">
    ALTER TABLE ... ENGINE [=] ...
   </code>
   changes only the
      storage engine used by the table, and leaves the table's
      partitioning scheme intact. The statement succeeds only if the
      target storage engine provides partitioning support. You can use
   <code class="literal">
    ALTER TABLE ... REMOVE PARTITIONING
   </code>
   to remove a
      table's partitioning; see
   <a class="xref" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    Section 15.1.9, “ALTER TABLE Statement”
   </a>
   .
  </p>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    Only a single
    <code class="literal">
     PARTITION BY
    </code>
    ,
    <code class="literal">
     ADD
        PARTITION
    </code>
    ,
    <code class="literal">
     DROP PARTITION
    </code>
    ,
    <code class="literal">
     REORGANIZE PARTITION
    </code>
    , or
    <code class="literal">
     COALESCE
        PARTITION
    </code>
    clause can be used in a given
    <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. If you (for example) wish to drop a
        partition and reorganize a table's remaining partitions,
        you must do so in two separate
    <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>
    statements (one using
    <code class="literal">
     DROP
        PARTITION
    </code>
    and then a second one using
    <code class="literal">
     REORGANIZE PARTITION
    </code>
    ).
   </p>
  </div>
  <p>
   You can delete all rows from one or more selected partitions using
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE ...
      TRUNCATE PARTITION
    </code>
   </a>
   .
  </p>
 </div>
 <br/>
</div>
