The schema I'm working on has a small amount of customers, with lots of data per customer.
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.
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.
This post 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.
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.
Any thoughts/suggestions on a better approach?
Thank you again!
UPDATE
Here is an example of what I was proposing:
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)
)
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