oracle数据库表按时间进行分区

• 按年进行分区

create table test_part
(ID NUMBER(20) not null,
REMARK VARCHAR2(1000),
create_time DATE
)
PARTITION BY RANGE(CREATE_TIME)INTERVAL (numtoyminterval(1, 'year'))(partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));

　　

partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')  表示小于2018-11-01放在part_t01分区

• 按月进行分区

create table test_part
(ID NUMBER(20) not null,
REMARK VARCHAR2(1000),
create_time DATE
)
PARTITION BY RANGE(CREATE_TIME)INTERVAL (numtoyminterval(1, 'month'))(partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));

 

• 按周进行分区

create table test_part
(ID NUMBER(20) not null,
REMARK VARCHAR2(1000),
create_time DATE
)
PARTITION BY RANGE(CREATE_TIME)INTERVAL (

numtodsinterval

(7, 'day'))(partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));

 

• 按天进行分区

create table test_part
(ID NUMBER(20) not null,
REMARK VARCHAR2(1000),
create_time DATE
)
PARTITION BY RANGE(CREATE_TIME)INTERVAL (numtodsinterval(1, 'day'))(partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));

参考&感谢https://blog.csdn.net/qq_34745941/article/details/109027781