<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="partitioning-maintenance">
      </a>
      26.3.4 Maintenance of Partitions
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045083488768">
  </a>
  <a class="indexterm" name="idm46045083487280">
  </a>
  <a class="indexterm" name="idm46045083485792">
  </a>
  <a class="indexterm" name="idm46045083484304">
  </a>
  <a class="indexterm" name="idm46045083482816">
  </a>
  <a class="indexterm" name="idm46045083481328">
  </a>
  <a class="indexterm" name="idm46045083479840">
  </a>
  <a class="indexterm" name="idm46045083478352">
  </a>
  <p>
   A number of table and partition maintenance tasks can be carried
        out on partitioned tables using SQL statements intended for such
        purposes.
  </p>
  <p>
   Table maintenance of partitioned tables can be accomplished
        using the statements
   <a class="link" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
    <code class="literal">
     CHECK TABLE
    </code>
   </a>
   ,
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE TABLE
    </code>
   </a>
   ,
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE TABLE
    </code>
   </a>
   , and
   <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    <code class="literal">
     REPAIR TABLE
    </code>
   </a>
   , which are supported
        for partitioned tables.
  </p>
  <p>
   You can use a number of extensions to
   <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>
   for performing operations of this type on one or
        more partitions directly, as described in the following list:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <b>
       Rebuilding partitions.
      </b>
      Rebuilds the partition; this has the same effect as
              dropping all records stored in the partition, then
              reinserting them. This can be useful for purposes of
              defragmentation.
     </p>
     <p>
      Example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa20479541"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">REBUILD</span> <span class="token keyword">PARTITION</span> p0<span class="token punctuation">,</span> p1<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      <b>
       Optimizing partitions.
      </b>
      If you have deleted a large number of rows from a
              partition or if you have made many changes to a
              partitioned table with variable-length rows (that is,
              having
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      ,
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      , or
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      columns), 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 ... OPTIMIZE PARTITION
       </code>
      </a>
      to reclaim any
              unused space and to defragment the partition data file.
     </p>
     <p>
      Example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa66451909"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">OPTIMIZE</span> <span class="token keyword">PARTITION</span> p0<span class="token punctuation">,</span> p1<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Using
      <code class="literal">
       OPTIMIZE PARTITION
      </code>
      on a given
            partition is equivalent to running
      <code class="literal">
       CHECK
            PARTITION
      </code>
      ,
      <code class="literal">
       ANALYZE PARTITION
      </code>
      ,
            and
      <code class="literal">
       REPAIR PARTITION
      </code>
      on that partition.
     </p>
     <p>
      Some MySQL storage engines, including
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      , do not support
            per-partition optimization; in these cases,
      <a class="link" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
       <code class="literal">
        ALTER
            TABLE ... OPTIMIZE PARTITION
       </code>
      </a>
      analyzes and rebuilds
            the entire table, and causes an appropriate warning to be
            issued. (Bug #11751825, Bug #42822) Use
      <code class="literal">
       ALTER TABLE
            ... REBUILD PARTITION
      </code>
      and
      <code class="literal">
       ALTER TABLE ...
            ANALYZE PARTITION
      </code>
      instead, to avoid this issue.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Analyzing partitions.
      </b>
      This reads and stores the key distributions for
              partitions.
     </p>
     <p>
      Example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa78820281"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ANALYZE</span> <span class="token keyword">PARTITION</span> p3<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      <b>
       Repairing partitions.
      </b>
      This repairs corrupted partitions.
     </p>
     <p>
      Example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa13725463"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">REPAIR</span> <span class="token keyword">PARTITION</span> p0<span class="token punctuation">,</span>p1<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Normally,
      <code class="literal">
       REPAIR PARTITION
      </code>
      fails when the
            partition contains duplicate key errors. 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
            IGNORE TABLE
       </code>
      </a>
      with this option, in which case all
            rows that cannot be moved due to the presence of duplicate
            keys are removed from the partition (Bug #16900947).
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Checking partitions.
      </b>
      You can check partitions for errors in much the same way
              that you can use
      <code class="literal">
       CHECK TABLE
      </code>
      with
              nonpartitioned tables.
     </p>
     <p>
      Example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa37180981"><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">CHECK</span> <span class="token keyword">PARTITION</span> p1<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      This statement tells you whether the data or indexes in
            partition
      <code class="literal">
       p1
      </code>
      of table
      <code class="literal">
       t1
      </code>
      are corrupted. If this is the case,
            use
      <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>
      to repair the
            partition.
     </p>
     <p>
      Normally,
      <code class="literal">
       CHECK PARTITION
      </code>
      fails when the
            partition contains duplicate key errors. 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
            IGNORE TABLE
       </code>
      </a>
      with this option, in which case the
            statement returns the contents of each row in the partition
            where a duplicate key violation is found. Only the values
            for the columns in the partitioning expression for the table
            are reported. (Bug #16900947)
     </p>
    </li>
   </ul>
  </div>
  <p>
   Each of the statements in the list just shown also supports the
        keyword
   <code class="literal">
    ALL
   </code>
   in place of the list of partition
        names. Using
   <code class="literal">
    ALL
   </code>
   causes the statement to act
        on all partitions in the table.
  </p>
  <p>
   You can also truncate 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>
   . This statement can be used to
        delete all rows from one or more partitions in much the same way
        that
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE TABLE
    </code>
   </a>
   deletes all
        rows from a table.
  </p>
  <p>
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE ...
        TRUNCATE PARTITION ALL
    </code>
   </a>
   truncates all partitions in the
        table.
  </p>
 </div>
 <br/>
</div>
