我用的是ORACLE10G的数据库,现在想建一个分区表,用RANGE分区,按月或者天分区,年份可以忽略,可以建立这种分区表吗?我一直建立不成功说是要完整的时间格式,那位大哥帮帮偶啊,谢谢拉,下面是我今天早上写的,一直出错:
create table test_time
(
id integer not null,
time date,
valuer integer
)
partition by range(time)
(
partition p1 values less than(to_date('01','dd')),
partition p2 values less than(to_date('10','dd')),
partition p3 values less than(to_date('20','dd')),
partition p4 values less than(maxvalue)
);
create table test_time
(
id integer not null,
time date,
valuer integer
)
partition by range(time)
(
partition p1 values less than(to_date('01','dd')),
partition p2 values less than(to_date('10','dd')),
partition p3 values less than(to_date('20','dd')),
partition p4 values less than(maxvalue)
);
create table test_time
(
id integer not null,
time date,
valuer integer
)
partition by range(time)
(
partition p1 values less than(to_date('2008-1-1','yyyy-mm-dd')),
partition p2 values less than(to_date('2008-6-1','yyyy-mm-dd')),
partition p3 values less than(to_date('2008-12-1','yyyy-mm-dd')),
partition p4 values less than(maxvalue)
);
create table test_time
(
id integer not null,
time date,
valuer integer
)
partition by range(time)
(
partition p1 values less than(substr(time,6,2)='01'),
partition p2 values less than(substr(time,6,2)='10'),
partition p3 values less than(substr(time,6,2)='20'),
partition p4 values less than(maxvalue)
);
这个试一下
partition p1 values less than(substr(to_char(time),4,2)='01')
由于range里面只能用字段,不能使用表达式
所以看来lz只能另外添加一个月份字段,再按月份字段分区了
(
id integer not null,
time date,
day number(2) not null, -- 保存的是 to_char(time,'dd')
valuer integer
)
partition by range( day )
(
partition p1 values less than (01),
partition p2 values less than (10),
partition p3 values less than (20),
partition p4 values less than (maxvalue)
)
---按天