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 (numtoyminterval(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 (numtoyminterval(1, 'day'))(partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));