创建分区:
/*****创建分区表*****/
create table sales
(
dopetno number,
shopid varchar2(20),
shopName varchar2(20),
tDate date
)
partition by range(tDate)
(
partition P_2007_MIN values less than (TO_DATE(' 2007-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
,
partition P_2008 values less than (TO_DATE(' 2008-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
,
partition P_2009 values less than (TO_DATE(' 2009-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
,
partition P_2010 values less than (TO_DATE(' 2010-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
,
partition P_2011 values less than (TO_DATE(' 2011-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
,
partition P_2012 values less than (TO_DATE(' 2012-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
,
partition P_2013 values less than (TO_DATE(' 2013-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
,
partition P_2014_MAX values less than (MAXVALUE)
);
/***单查找分区表**/
select * from sales partition(P_2007_MIN);
select * from sales partition(P_2008);
select * from sales partition(P_2009);
select * from sales partition(P_2010);
select * from sales partition(P_2011);
select * from sales partition(P_2012);
select * from sales partition(P_2013);
select * from sales partition(P_2014);
select * from sales partition(P_2015);
select * from sales partition(P_2016_MAX);
删除分区:
/***扩展分区表****/
--创建临时放数据的表
create table sales_tmp as select * from sales where 1=2;
--把最后一个分区数据,加入到临时表中
insert into sales_tmp select * from sales partition(P_2014_MAX);
--删除最后一个分区
alter table sales drop partition P_2014_MAX;
--新增新的分区1
ALTER TABLE sales
ADD PARTITION P_2014
VALUES LESS THAN (TO_DATE(' 2014-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
--新增新的分区2
ALTER TABLE sales
ADD PARTITION P_2015
VALUES LESS THAN (TO_DATE(' 2015-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
--新增新的分区3
ALTER TABLE sales
ADD PARTITION P_2016_MAX
VALUES LESS THAN (maxvalue);
--把临时表的数据,重新插入到新的表
insert into sales select * from sales_tmp
drop table sales_tmp;
/**the end **/
其它操作:
select * from sales where to_char(tdate,'yyyy') >='2007' and to_char(tdate,'yyyy') <= '2009'
delete from sales where to_char(tdate,'yyyy') >='2007'
和其它普通表操作差不多.没有很大的区别.
要注意.分区表分为几类,以上是range分区的操作.其它操作都有不同的规则.