<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="partitioning-management-hash-key">
      </a>
      26.3.2 Management of HASH and KEY Partitions
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045083734704">
  </a>
  <a class="indexterm" name="idm46045083733216">
  </a>
  <a class="indexterm" name="idm46045083731728">
  </a>
  <a class="indexterm" name="idm46045083730240">
  </a>
  <p>
   Tables which are partitioned by hash or by key are very similar
        to one another with regard to making changes in a partitioning
        setup, and both differ in a number of ways from tables which
        have been partitioned by range or list. For that reason, this
        section addresses the modification of tables partitioned by hash
        or by key only. For a discussion of adding and dropping of
        partitions of tables that are partitioned by range or list, 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>
  <p>
   You cannot drop partitions from tables that are partitioned by
   <code class="literal">
    HASH
   </code>
   or
   <code class="literal">
    KEY
   </code>
   in the same
        way that you can from tables that are partitioned by
   <code class="literal">
    RANGE
   </code>
   or
   <code class="literal">
    LIST
   </code>
   . However,
        you can merge
   <code class="literal">
    HASH
   </code>
   or
   <code class="literal">
    KEY
   </code>
   partitions using
   <code class="literal">
    ALTER TABLE ... COALESCE
        PARTITION
   </code>
   . Suppose that a
   <code class="literal">
    clients
   </code>
   table containing data about clients is divided into 12
        partitions, created as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa79414721"><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> clients <span class="token punctuation">(</span>
    id <span class="token datatype">INT</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">signed</span> <span class="token datatype">DATE</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span> <span class="token function">MONTH</span><span class="token punctuation">(</span><span class="token keyword">signed</span><span class="token punctuation">)</span> <span class="token punctuation">)</span>
<span class="token keyword">PARTITIONS</span> <span class="token number">12</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To reduce the number of partitions from 12 to 8, execute 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:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa47927222"><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> clients <span class="token keyword">COALESCE</span> <span class="token keyword">PARTITION</span> <span class="token number">4</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.02 sec)</span></code></pre>
  </div>
  <p>
   <code class="literal">
    COALESCE
   </code>
   works equally well with tables that
        are partitioned by
   <code class="literal">
    HASH
   </code>
   ,
   <code class="literal">
    KEY
   </code>
   ,
   <code class="literal">
    LINEAR HASH
   </code>
   , or
   <code class="literal">
    LINEAR KEY
   </code>
   . Here is an example similar to the
        previous one, differing only in that the table is partitioned by
   <code class="literal">
    LINEAR KEY
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa81483131"><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> clients_lk <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     id <span class="token datatype">INT</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 punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">signed</span> <span class="token datatype">DATE</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">LINEAR</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span><span class="token keyword">signed</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">PARTITIONS</span> <span class="token number">12</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.03 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> clients_lk <span class="token keyword">COALESCE</span> <span class="token keyword">PARTITION</span> <span class="token number">4</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.06 sec)</span>
<span class="token output">Records: 0  Duplicates: 0  Warnings: 0</span></code></pre>
  </div>
  <p>
   The number following
   <code class="literal">
    COALESCE PARTITION
   </code>
   is
        the number of partitions to merge into the remainder—in
        other words, it is the number of partitions to remove from the
        table.
  </p>
  <p>
   Attempting to remove more partitions than are in the table
        results in an error like this one:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14288683"><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> clients <span class="token keyword">COALESCE</span> <span class="token keyword">PARTITION</span> <span class="token number">18</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1478 (HY000)<span class="token punctuation">:</span> Cannot remove all partitions, use DROP TABLE instead</span></code></pre>
  </div>
  <p>
   To increase the number of partitions for the
   <code class="literal">
    clients
   </code>
   table from 12 to 18, use
   <code class="literal">
    ALTER TABLE ... ADD PARTITION
   </code>
   as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa48382134"><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> clients <span class="token keyword">ADD</span> <span class="token keyword">PARTITION</span> <span class="token keyword">PARTITIONS</span> <span class="token number">6</span><span class="token punctuation">;</span></code></pre>
  </div>
 </div>
 <br/>
</div>
