<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="partitioning-linear-hash">
      </a>
      26.2.4.1 LINEAR HASH Partitioning
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045084270000">
  </a>
  <a class="indexterm" name="idm46045084268512">
  </a>
  <p>
   MySQL also supports linear hashing, which differs from regular
          hashing in that linear hashing utilizes a linear powers-of-two
          algorithm whereas regular hashing employs the modulus of the
          hashing function's value.
  </p>
  <p>
   Syntactically, the only difference between linear-hash
          partitioning and regular hashing is the addition of the
   <code class="literal">
    LINEAR
   </code>
   keyword in the
   <code class="literal">
    PARTITION
          BY
   </code>
   clause, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa5402999"><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> employees <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>
    hired <span class="token datatype">DATE</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'1970-01-01'</span><span class="token punctuation">,</span>
    separated <span class="token datatype">DATE</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'9999-12-31'</span><span class="token punctuation">,</span>
    job_code <span class="token datatype">INT</span><span class="token punctuation">,</span>
    store_id <span class="token datatype">INT</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">LINEAR</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span> <span class="token function">YEAR</span><span class="token punctuation">(</span>hired<span class="token punctuation">)</span> <span class="token punctuation">)</span>
<span class="token keyword">PARTITIONS</span> <span class="token number">4</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Given an expression
   <em class="replaceable">
    <code>
     expr
    </code>
   </em>
   , the
          partition in which the record is stored when linear hashing is
          used is partition number
   <em class="replaceable">
    <code>
     N
    </code>
   </em>
   from
          among
   <em class="replaceable">
    <code>
     num
    </code>
   </em>
   partitions, where
   <em class="replaceable">
    <code>
     N
    </code>
   </em>
   is derived according to the
          following algorithm:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Find the next power of 2 greater than
      <em class="replaceable">
       <code>
        num
       </code>
      </em>
      . We call this value
      <em class="replaceable">
       <code>
        V
       </code>
      </em>
      ; it can be calculated as:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-simple"><div class="docs-select-all right" id="sa36401969"><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-simple"><em class="replaceable">V</em> <span class="token operator">=</span> POWER<span class="token punctuation">(</span>2<span class="token punctuation">,</span> CEILING<span class="token punctuation">(</span>LOG<span class="token punctuation">(</span>2<span class="token punctuation">,</span> <em class="replaceable">num</em><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span></code></pre>
     </div>
     <p>
      (Suppose that
      <em class="replaceable">
       <code>
        num
       </code>
      </em>
      is 13. Then
      <a class="link" href="mathematical-functions.html#function_log">
       <code class="literal">
        LOG(2,13)
       </code>
      </a>
      is
              3.7004397181411.
      <a class="link" href="mathematical-functions.html#function_ceiling">
       <code class="literal">
        CEILING(3.7004397181411)
       </code>
      </a>
      is
              4, and
      <em class="replaceable">
       <code>
        V
       </code>
      </em>
      =
      <a class="link" href="mathematical-functions.html#function_power">
       <code class="literal">
        POWER(2,4)
       </code>
      </a>
      , which is 16.)
     </p>
    </li>
    <li class="listitem">
     <p>
      Set
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      =
      <em class="replaceable">
       <code>
        F
       </code>
      </em>
      (
      <em class="replaceable">
       <code>
        column_list
       </code>
      </em>
      )
              &amp; (
      <em class="replaceable">
       <code>
        V
       </code>
      </em>
      - 1).
     </p>
    </li>
    <li class="listitem">
     <p>
      While
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      &gt;=
      <em class="replaceable">
       <code>
        num
       </code>
      </em>
      :
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: disc; ">
       <li class="listitem">
        <p>
         Set
         <em class="replaceable">
          <code>
           V
          </code>
         </em>
         =
         <em class="replaceable">
          <code>
           V
          </code>
         </em>
         / 2
        </p>
       </li>
       <li class="listitem">
        <p>
         Set
         <em class="replaceable">
          <code>
           N
          </code>
         </em>
         =
         <em class="replaceable">
          <code>
           N
          </code>
         </em>
         &amp;
                  (
         <em class="replaceable">
          <code>
           V
          </code>
         </em>
         - 1)
        </p>
       </li>
      </ul>
     </div>
    </li>
   </ol>
  </div>
  <p>
   Suppose that the table
   <code class="literal">
    t1
   </code>
   , using linear
          hash partitioning and having 6 partitions, is created using
          this statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa88938317"><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-simple">CREATE TABLE t1 <span class="token punctuation">(</span>col1 INT<span class="token punctuation">,</span> col2 CHAR<span class="token punctuation">(</span>5<span class="token punctuation">)</span><span class="token punctuation">,</span> col3 DATE<span class="token punctuation">)</span>
    PARTITION BY LINEAR HASH<span class="token punctuation">(</span> YEAR<span class="token punctuation">(</span>col3<span class="token punctuation">)</span> <span class="token punctuation">)</span>
    PARTITIONS 6<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Now assume that you want to insert two records into
   <code class="literal">
    t1
   </code>
   having the
   <code class="literal">
    col3
   </code>
   column values
   <code class="literal">
    '2003-04-14'
   </code>
   and
   <code class="literal">
    '1998-10-19'
   </code>
   . The partition number for the
          first of these is determined as follows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa15960152"><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-simple"><em class="replaceable">V</em> <span class="token operator">=</span> POWER<span class="token punctuation">(</span>2<span class="token punctuation">,</span> CEILING<span class="token punctuation">(</span> LOG<span class="token punctuation">(</span>2<span class="token punctuation">,</span>6<span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">=</span> 8
<em class="replaceable">N</em> <span class="token operator">=</span> YEAR<span class="token punctuation">(</span>'2003<span class="token operator">-</span>04<span class="token operator">-</span>14'<span class="token punctuation">)</span> <span class="token operator">&amp;</span> <span class="token punctuation">(</span>8 <span class="token operator">-</span> 1<span class="token punctuation">)</span>
   <span class="token operator">=</span> 2003 <span class="token operator">&amp;</span> 7
   <span class="token operator">=</span> 3

<span class="token punctuation">(</span><em>3 <span class="token operator">&gt;=</span> 6 is FALSE<span class="token operator">:</span> record stored in partition <span class="token comment" spellcheck="true">#3</span></em><span class="token comment" spellcheck="true">)</span></code></pre>
  </div>
  <p>
   The number of the partition where the second record is stored
          is calculated as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa44788251"><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-simple"><em class="replaceable">V</em> <span class="token operator">=</span> 8
<em class="replaceable">N</em> <span class="token operator">=</span> YEAR<span class="token punctuation">(</span>'1998<span class="token operator">-</span>10<span class="token operator">-</span>19'<span class="token punctuation">)</span> <span class="token operator">&amp;</span> <span class="token punctuation">(</span>8 <span class="token operator">-</span> 1<span class="token punctuation">)</span>
  <span class="token operator">=</span> 1998 <span class="token operator">&amp;</span> 7
  <span class="token operator">=</span> 6

<span class="token punctuation">(</span><em>6 <span class="token operator">&gt;=</span> 6 is TRUE<span class="token operator">:</span> additional step required</em><span class="token punctuation">)</span>

<em class="replaceable">N</em> <span class="token operator">=</span> 6 <span class="token operator">&amp;</span> <span class="token punctuation">(</span><span class="token punctuation">(</span>8 <span class="token operator">/</span> 2<span class="token punctuation">)</span> <span class="token operator">-</span> 1<span class="token punctuation">)</span>
  <span class="token operator">=</span> 6 <span class="token operator">&amp;</span> 3
  <span class="token operator">=</span> 2

<span class="token punctuation">(</span><em>2 <span class="token operator">&gt;=</span> 6 is FALSE<span class="token operator">:</span> record stored in partition <span class="token comment" spellcheck="true">#2</span></em><span class="token comment" spellcheck="true">)</span></code></pre>
  </div>
  <p>
   The advantage in partitioning by linear hash is that the
          adding, dropping, merging, and splitting of partitions is made
          much faster, which can be beneficial when dealing with tables
          containing extremely large amounts (terabytes) of data. The
          disadvantage is that data is less likely to be evenly
          distributed between partitions as compared with the
          distribution obtained using regular hash partitioning.
  </p>
 </div>
 <br/>
</div>
