Oracle ID按分区，DATE按分区进行间隔 [英] Oracle Partition by ID and subpartition by DATE with interval

问题描述

我正在处理的模式的客户数量很少，每个客户的数据很多。

The schema I'm working on has a small amount of customers, with lots of data per customer.

在确定分区策略时，我的第一个想法是按customer_id进行分区，然后按天间隔按范围进行分区。但是，您不能在子分区中使用间隔。

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.

最终，我想要一种为新客户自动创建分区的方法，并为新客户创建每日自动分区客户数据。所有应用程序查询均处于customer_id级别，并指定了不同的日期范围。

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.

此 post 几乎相同，但是答案涉及逆转分区策略，我仍然想找到一种方法来完成范围-范围间隔分区。一种方法可能是每月进行一次数据库工作来为未来的天/月创建子分区，但这并不对。

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?

再次感谢您！

更新

以下是我提议的示例：

    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)
         )

我目前在模板中有290个子分区。除了一个障碍，这似乎是可行的。在我的测试中，我发现CUSTOMER_ID大于3615的任何记录都会因ORA-14400而失败：插入的分区键不会映射到任何分区

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

推荐答案

您可以在日期上创建 RANGE INTERVAL 分区，然后创建 LIST 或 RANGE 子分区就可以了。将会是这样：

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') );

请注意，第二种解决方案的客户数量（即ID）是固定的。如果您有新客户，则必须更改表并相应地修改SUBPARTITION模板。

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.

每当插入或更新新值时，Oracle都会自动创建每月分区。

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

这篇关于Oracle ID按分区，DATE按分区进行间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！