title: Oracle Partition by ID and subpartition by DATE with interval
body: <p>The schema I'm working on has a small amount of customers, with lots of data per customer.</p>
  
  <p>In determining a partitioning strategy, my first thought was to partition by customer_id and then subpartition by range with a day interval. However you cannot use interval in subpartitions.</p>
  
  <p>Ultimately I would like a way to automatically create partitions for new customers as they are created, and also have automatic daily subpartitions created for the customers' data. All application queries are at the customer_id level with various date ranges specified.</p>
  
  <p>This <a href="https://stackoverflow.com/questions/28563445/oracle-partition-by-group-id-and-subpartition-monthly">post</a> is nearly identical, but the answer involves reversing the partitioning strategy, and I would still like to find a way to accomplish range-range interval partitioning. One way could potentially be to have a monthly database job to create subpartitions for the days/months ahead, but that doesn't feel right.</p>
  
  <p>Perhaps I'm wrong on my assumptions that the current data structure would benefit more from a range-range interval partitioning strategy. We have a few customers whose data dwarfs other customers, so I was thinking of ways to isolate customer data.</p>
  
  <p>Any thoughts/suggestions on a better approach?</p>
  
  <p>Thank you again!</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Here is an example of what I was proposing:</p>
  
  <pre><code>    CREATE TABLE PART_TEST(
              CUSTOMER_ID NUMBER,
              LAST_MODIFIED_DATE DATE
          )
          PARTITION BY RANGE (CUSTOMER_ID) 
          INTERVAL (1) 
          SUBPARTITION BY RANGE (LAST_MODIFIED_DATE)
          SUBPARTITION TEMPLATE
           (
              SUBPARTITION subpart_1206_min values LESS THAN (TO_DATE('12/2006','MM/YYYY')),
              SUBPARTITION subpart_0107 values LESS THAN (TO_DATE('01/2007','MM/YYYY')),
              SUBPARTITION subpart_0207 values LESS THAN (TO_DATE('02/2007','MM/YYYY')),
              ...
              ...
              ...
              SUBPARTITION subpart_max values LESS THAN (MAXVALUE)
           )
           (
           PARTITION part_1 VALUES LESS THAN (1)
           )
  </code></pre>
  
  <p>I currently have 290 subpartitions in the template. This appears to be working except for one snag. In my tests I'm finding that any record with a CUSTOMER_ID greater than 3615 fails with ORA-14400: inserted partition key does not map to any partition</p>