如何按日期列对Oracle表进行分区? [英] How to partition an oracle table by a date column?

问题描述

我在oracle中有一个表:

I have a table in oracle:

CREATE TABLE transaction (
    id INT NOT NULL,
    accountnumber VARCHAR NOT NULL,
    stmtvaluedate DATE NOT NULL,
    ...
)

我想按stmtvaluedate列对该表进行分区.我的目标是在一个月后创建一个新分区.

And I want to partition this table by the stmtvaluedate column. My goal is to create a new partition after a month have passed.

有没有好的脚本?还是我必须创建静态数量的分区?

Is there any good script, for it? Or I have to create static numbers of partitions?

最好是:如果一个月过去了，将自动创建一个新分区.

The best would be: if a month have passed, a new partition will be created automatically.

谁能给我一个关于如何在每个月后按日期列对表进行分区的示例吗?如果无法进行自动分区，那么我需要一个示例，该示例通过日期列(大约每个月)从现在开始创建到一年的分区.

Can anyone give me an example about how to partition a table by a date column after every month? If the automatically partitioning is impossible, than I would need an example, which creates partitions to a year from now by a date column, about every month.

谢谢！

推荐答案

您完全可以做.这应该做到:

What you want to do is completely possible. This should do it:

CREATE TABLE transaction (
    id INT NOT NULL,
    accountnumber VARCHAR2(30) NOT NULL,
    stmtvaluedate DATE NOT NULL
)
PARTITION BY RANGE (stmtvaluedate)
INTERVAL (NUMTOYMINTERVAL (1,'MONTH')) 
    ( partition transaction_old values less than (to_date('01-JAN-2000','DD-MON-YYYY') ));

这篇关于如何按日期列对Oracle表进行分区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！