Should I partition/subpartition my table?
<p><strong>Case</strong></p>

<ol>
<li>The system has <code>dispositives</code>, basically formed by <code>id, type, name</code>.</li>
<li>I may have N <code>dispositives</code>.</li>
<li>I have a table to store a <code>log</code> of all <code>dispositives</code>. This is the biggest table in the system. (Now counting 100 mi records)</li>
<li>The <code>log</code> table has: <code>id_dispositive, date, status</code>.</li>
</ol>

<p><strong>Problem</strong></p>

<p>Obviously this huge data it's causing performance issues. I must store at least two months log values..</p>

<p>Today I have this <code>dispositives</code> by <code>type</code>:</p>

<pre><code>TYPE     COUNT
---------------
1         78956  
2         125161
3         13213
4         6112
5         25426
6         12314
7         1241
8         622
</code></pre>

<p>I also have some business logic to feed this <code>log</code> table. Anyways, here is my thoughts:</p>

<p><strong>My "solution"</strong></p>

<p>I was thinking on partition this <code>log</code> table. Here is the questions:</p>

<ol>
<li>Does it worth it?</li>
<li>Should I partition by type?</li>
<li>Should I partition by type AND subpartition by DATE?</li>
<li>Can I do it programmatically?</li>
</ol>

<p>As this types are dynamics, users can delete/insert new ones.</p>

<p>So if they delete some type, I must delete all logs of that type.
As if they insert some type, I must register(store) all logs of that type.</p>

<p><strong>EDIT according Francesco Serra's answer</strong></p>

<p>I'm trying to create the table like this:</p>

<pre><code>create table log(
       id_dispositive    number,
       type       number,
       date_verification    date,
       status  number
)
partition by range (date_verification) 
subpartition by list (type)  
subpartition TEMPLATE (
    SUBPARTITION type1 VALUES (1),
    SUBPARTITION type2 VALUES (2),
    SUBPARTITION type3 VALUES (3),
    SUBPARTITION type4 VALUES (4)
)(                           
interval (numtoyminterval(1,'MONTH'))(
   partition p0816 values less than (to_date('01/09/2016','dd/mm/yyyy'))
));
</code></pre>

<p>and i'm getting:</p>

<pre><code>ORA-14004: PARTITION keyword not found.
</code></pre>