1.表分区语法
1.1 按年创建分区表
create table TEST
(
ID number(20) not null,
NAME varchar2(1000),
CREATE_TIME date
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, ‘year’))
(partition part_t01 values less than(to_date(‘2019-04-01’, ‘yyyy-mm-dd’)));
1.2 按月创建分区表
–按月创建分区表
create table TEST
(
ID number(20) not null,
NAME varchar2(1000),
CREATE_TIME date
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, ‘month’))
(partition part_t01 values less than(to_date(‘2019-04-01’, ‘yyyy-mm-dd’)));
1.3 按天创建分区表
create table TEST
(
ID number(20) not null,
NAME varchar2(1000),
CREATE_TIME date
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL(1, ‘day’))
(partition part_t01 values less than(to_date(‘2019-04-01’, ‘yyyy-mm-dd’)));
1.4 按周创建分区表
create table TEST
(
ID number(20) not null,
NAME varchar2(1000),
CREATE_TIME date
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL (7, ‘day’))
(partition part_t01 values less than(to_date(‘2019-04-01’, ‘yyyy-mm-dd’)));
备注:
partition part_t01 values less than(to_date(‘2019-04-01’, ‘yyyy-mm-dd’)) 表示表示小于 2019-04-01 的都放在 part_t01 分区表中。
2.查看分区表信息
按日期添加数据,oracle 会自动添加分区。
–查询当前表有多少分区
select table_name,partition_name from user_tab_partitions where table_name=‘TEST’;
–查询这个表的某个(part_t01)里的数据
select * from TEST partition(part_t01);