Oracle 按天自动分区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19299058/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle automatic partitioning by day
提问by user2428207
I'm working with an Oracle 11g DB that has an input of 3-5m rows a day. In the future I would like to use partitioning based on the column Timestamp. My goal is to create a new partition for every day, automatically.
我正在使用每天输入 3-500 万行的 Oracle 11g 数据库。将来我想使用基于列的分区Timestamp。我的目标是每天自动创建一个新分区。
I just found ways to create a given range of days i.e. 1-20 but not for a unlimited time (01.01.2014 to mm.dd.yyyy).
我刚刚找到了创建给定天数范围的方法，即 1-20 天，但不是无限时间（01.01.2014 到 mm.dd.yyyy）。
回答by Harry Lime
For daily ranges you can do it like this:
对于每日范围，您可以这样做：
create table ...
...
interval(numtodsinterval(1, 'DAY'))
(
partition log_data_p1 values less than (to_date('22-04-2015', 'DD-MM-YYYY')),
partition log_data_p2 values less than (to_date('23-04-2015', 'DD-MM-YYYY'))
);
Important to use numtodsintervalinstead of numtoyminterval
重要的是使用numtodsinterval而不是numtoyminterval
回答by Lokesh
Oracle 11g does offer automatic partition creation, you just need to create table with proper syntax like this one:
Oracle 11g 确实提供了自动分区创建功能，您只需要使用正确的语法创建表，如下所示：
create table
pos_data (
start_date DATE,
store_id NUMBER,
inventory_id NUMBER(6),
qty_sold NUMBER(3)
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
);
Here, two partitions have been defined and an interval of one month has been specified. If date goes beyond the max date specified in partition then oracle automatically creates new partition.
这里定义了两个分区，并指定了一个月的间隔。如果日期超出分区中指定的最大日期，则 oracle 会自动创建新分区。
Similarly you can specify partition for day range and oracle will take care of rest.
同样，您可以为日期范围指定分区，oracle 将负责休息。
