3

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

Community
  • 1
  • 1
rcurrie
  • 329
  • 1
  • 3
  • 17
  • According [Logical Database Limits](https://docs.oracle.com/cloud/latest/db121/REFRN/refrn0043.htm#REFRN0043) you can have up to 1'048'575 partitions. You have 290*3'615 = 1'048'350. I **really** recommend you to revise your partitioning plan! How many records do you expect in your table? Trillions? – Wernfried Domscheit Dec 02 '15 at 21:22
  • Thanks Wernfried, ultimately I only expect roughly 2000 Customers, but that is clearly excessive in terms of the partitioning strategy. The database is a multi-tenanted system, and my goal was to find a way to partition each Customers data into segments to help performance, etc.., thus my reason for partitioning at the CUSTOMER_ID level, and then subparition by month/year. – rcurrie Dec 02 '15 at 21:35

1 Answers1

2

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.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Thanks. This is similar to the solution in the link I posted above. However still not something I think will work with the data I have. I'd much rather find a solution that partitions primarily on CUSTOMER_ID and then subpartitions on DAY. Most likely I'll have a subpartition template for many months etc. – rcurrie Dec 02 '15 at 14:18
  • 1
    I don't recommend that, you would loose the great function of INTERVAL partition where Oracle automatically creates new partitions (incl. subpartitions) when they are needed. – Wernfried Domscheit Dec 02 '15 at 18:42
  • I was still planning on using interval partitioning on CUSTOMER_ID with an INTERVAL(1) with subpartition templates. That way every time a new Customer is created, a new partition is created and also subpartitions for the months. I can update the question with this sample... – rcurrie Dec 02 '15 at 20:15
  • Just be careful and test performance, and maintainability, with the partitioning by `CUSTOMER_ID` -- Partitioning does not always mean Faster. – Mark Stewart Nov 07 '19 at 16:32