Oracle表分区
一、(根据数据日期分区存储)列表分区(list)
创建新表fqtime
create table FQTIME
( id VARCHAR2(5) not null,
chn VARCHAR2(3),
atm NUMBER(20,2),
pdate VARCHAR2(8),
primary key(id)
)
partition by list (PDATE)
(
partition P_20170401 values ('20170401')
);
commit;
1)、含有分区的数据表创建成功,当要插入新的数据日期时:
alter table FQTIME add partition P_20170102 values('20170102');
commit;
insert into FQTIME(chn,atm,Pdate) values('03','400','20170102');
commit;
2)、删除某一个分区时:
alter table FQTIME drop partition P_20170103;
commit;
二、范围分区(range)
创建表scored:
create table SCORED
(
id VARCHAR2(5) not null,
name VARCHAR2(20),
sex VARCHAR2(2),
scord NUMBER(10),
primary key(id)
)
partition by range (SCORD)
(
partition SCORED_1 values less than (60),
partition SCORED_2 values less than (80),
partition SCORED_3 values less than (MAXVALUE)
);
commit;
查询语句:
select * from scored partition(SCORED_1);