Oracle Partition by ID and subpartition by DATE with interval

Question

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

Answer 1

You can make a RANGE INTERVAL partition on date and then LIST or RANGE subpartition on it. Would be like this:

CREATE TABLE MY_PART_TABLE
(
  CUSTOMER_ID                      NUMBER             NOT NULL,
  THE_DATE                 TIMESTAMP(0) NOT NULL,
  OTHER_COLUMNS NUMBER
)
PARTITION BY RANGE (THE_DATE) INTERVAL (INTERVAL '1' MONTH)
    SUBPARTITION BY RANGE (CUSTOMER_ID)
        SUBPARTITION TEMPLATE (
        SUBPARTITION CUSTOMER_GROUP_1 VALUES LESS THAN (10),
        SUBPARTITION CUSTOMER_GROUP_2 VALUES LESS THAN (20),
        SUBPARTITION CUSTOMER_GROUP_3 VALUES LESS THAN (30),
        SUBPARTITION CUSTOMER_GROUP_4 VALUES LESS THAN (40),
        SUBPARTITION CUSTOMER_GROUP_5 VALUES LESS THAN (MAXVALUE)
        )
(PARTITION VALUES LESS THAN ( TIMESTAMP '2015-01-01 00:00:00') );



CREATE TABLE MY_PART_TABLE
(
  CUSTOMER_ID                      NUMBER             NOT NULL,
  THE_DATE                 TIMESTAMP(0) NOT NULL,
  OTHER_COLUMNS NUMBER
)
PARTITION BY RANGE (THE_DATE) INTERVAL (INTERVAL '1' MONTH)
    SUBPARTITION BY LIST (CUSTOMER_ID)
        SUBPARTITION TEMPLATE (
        SUBPARTITION CUSTOMER_1 VALUES (1),
        SUBPARTITION CUSTOMER_2 VALUES (2),
        SUBPARTITION CUSTOMER_3_to_6 VALUES (3,4,5,6),
        SUBPARTITION CUSTOMER_7 VALUES (7)
        )
(PARTITION VALUES LESS THAN ( TIMESTAMP '2015-01-01 00:00:00') );

Note, for the second solution the number (i.e. ID's) of customer is fix. If you get new customers you have to alter the table and modify the SUBPARTITION TEMPLATE accordingly.

Monthly partitions will be created automatically by Oracle whenever new values are inserted or updated.