<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="partitioning-subpartitions">
      </a>
      26.2.6 Subpartitioning
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045084166368">
  </a>
  <a class="indexterm" name="idm46045084165296">
  </a>
  <p>
   Subpartitioning—also known as
   <span class="firstterm">
    composite
        partitioning
   </span>
   —is the further division of each
        partition in a partitioned table. Consider the following
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa79876509"><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> ts <span class="token punctuation">(</span>id <span class="token datatype">INT</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 keyword">SUBPARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span> <span class="token function">TO_DAYS</span><span class="token punctuation">(</span>purchased<span class="token punctuation">)</span> <span class="token punctuation">)</span>
    <span class="token keyword">SUBPARTITIONS</span> <span class="token number">2</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">2000</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 keyword">MAXVALUE</span>
    <span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Table
   <code class="literal">
    ts
   </code>
   has 3
   <code class="literal">
    RANGE
   </code>
   partitions. Each of these
        partitions—
   <code class="literal">
    p0
   </code>
   ,
   <code class="literal">
    p1
   </code>
   ,
        and
   <code class="literal">
    p2
   </code>
   —is further divided into 2
        subpartitions. In effect, the entire table is divided into
   <code class="literal">
    3 * 2 = 6
   </code>
   partitions. However, due to the
        action of the
   <code class="literal">
    PARTITION BY RANGE
   </code>
   clause, the
        first 2 of these store only those records with a value less than
        1990 in the
   <code class="literal">
    purchased
   </code>
   column.
  </p>
  <a class="indexterm" name="idm46045084154160">
  </a>
  <p>
   It is possible to subpartition tables that are partitioned by
   <code class="literal">
    RANGE
   </code>
   or
   <code class="literal">
    LIST
   </code>
   .
        Subpartitions may use either
   <code class="literal">
    HASH
   </code>
   or
   <code class="literal">
    KEY
   </code>
   partitioning. This is also known as
   <span class="firstterm">
    composite partitioning
   </span>
   .
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    <code class="literal">
     SUBPARTITION BY HASH
    </code>
    and
    <code class="literal">
     SUBPARTITION BY KEY
    </code>
    generally follow the
          same syntax rules as
    <code class="literal">
     PARTITION BY HASH
    </code>
    and
    <code class="literal">
     PARTITION BY KEY
    </code>
    , respectively. An
          exception to this is that
    <code class="literal">
     SUBPARTITION BY
          KEY
    </code>
    (unlike
    <code class="literal">
     PARTITION BY KEY
    </code>
    )
          does not currently support a default column, so the column
          used for this purpose must be specified, even if the table has
          an explicit primary key. This is a known issue which we are
          working to address; see
    <a class="xref" href="partitioning-limitations.html#partitioning-limitations-subpartitions" title="Issues with subpartitions">
     Issues with subpartitions
    </a>
    , for
          more information and an example.
   </p>
  </div>
  <a class="indexterm" name="idm46045084142608">
  </a>
  <p>
   It is also possible to define subpartitions explicitly using
   <code class="literal">
    SUBPARTITION
   </code>
   clauses to specify options for
        individual subpartitions. For example, a more verbose fashion of
        creating the same table
   <code class="literal">
    ts
   </code>
   as shown in the
        previous example would be:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa27693517"><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> ts <span class="token punctuation">(</span>id <span class="token datatype">INT</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 keyword">SUBPARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span> <span class="token function">TO_DAYS</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">SUBPARTITION</span> s0<span class="token punctuation">,</span>
            <span class="token keyword">SUBPARTITION</span> s1
        <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">2000</span><span class="token punctuation">)</span> <span class="token punctuation">(</span>
            <span class="token keyword">SUBPARTITION</span> s2<span class="token punctuation">,</span>
            <span class="token keyword">SUBPARTITION</span> s3
        <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 keyword">MAXVALUE</span> <span class="token punctuation">(</span>
            <span class="token keyword">SUBPARTITION</span> s4<span class="token punctuation">,</span>
            <span class="token keyword">SUBPARTITION</span> s5
        <span class="token punctuation">)</span>
    <span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Some syntactical items of note are listed here:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Each partition must have the same number of subpartitions.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you explicitly define any subpartitions using
      <code class="literal">
       SUBPARTITION
      </code>
      on any partition of a
            partitioned table, you must define them all. In other words,
            the following statement fails:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa29172597"><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> ts <span class="token punctuation">(</span>id <span class="token datatype">INT</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 keyword">SUBPARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span> <span class="token function">TO_DAYS</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">SUBPARTITION</span> s0<span class="token punctuation">,</span>
            <span class="token keyword">SUBPARTITION</span> s1
        <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">2000</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 keyword">MAXVALUE</span> <span class="token punctuation">(</span>
            <span class="token keyword">SUBPARTITION</span> s2<span class="token punctuation">,</span>
            <span class="token keyword">SUBPARTITION</span> s3
        <span class="token punctuation">)</span>
    <span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      This statement would still fail even if it used
      <code class="literal">
       SUBPARTITIONS 2
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Each
      <code class="literal">
       SUBPARTITION
      </code>
      clause must include (at
            a minimum) a name for the subpartition. Otherwise, you may
            set any desired option for the subpartition or allow it to
            assume its default setting for that option.
     </p>
    </li>
    <li class="listitem">
     <p>
      Subpartition names must be unique across the entire table.
            For example, the following
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE
            TABLE
       </code>
      </a>
      statement is valid:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa61991008"><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> ts <span class="token punctuation">(</span>id <span class="token datatype">INT</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 keyword">SUBPARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span> <span class="token function">TO_DAYS</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">SUBPARTITION</span> s0<span class="token punctuation">,</span>
            <span class="token keyword">SUBPARTITION</span> s1
        <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">2000</span><span class="token punctuation">)</span> <span class="token punctuation">(</span>
            <span class="token keyword">SUBPARTITION</span> s2<span class="token punctuation">,</span>
            <span class="token keyword">SUBPARTITION</span> s3
        <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 keyword">MAXVALUE</span> <span class="token punctuation">(</span>
            <span class="token keyword">SUBPARTITION</span> s4<span class="token punctuation">,</span>
            <span class="token keyword">SUBPARTITION</span> s5
        <span class="token punctuation">)</span>
    <span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
