注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

间隔分区间的分区交换  

2013-10-19 10:59:18|  分类: 分区表 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

间隔分区间的分区交换

情形:一分区表使用了间隔分区,由于该分区表越来越庞大,需要把6个月前的数据转移到另外一张分区表上。(好像写的是一个间隔分区跟一个范围分区的分区交换,但两个间隔分区的分区交换步骤跟这一样,所以标题不改了)

模拟测试如下:

1.使用了间隔分区的分区表t;
create table t
(sale_id number,sale_name varchar2(20),sale_date date)
partition by range(sale_date)
interval(numtoyminterval (1,'month'))(
partition p_2013_08 values less than(to_date('2013-09-01','YYYY-MM-DD')),
partition p_2013_09 values less than(to_date('2013-10-01','YYYY-MM-DD')),
partition p_2013_10 values less than(to_date('2013-11-01','YYYY-MM-DD')),
partition p_2013_11 values less than(to_date('2013-12-01','YYYY-MM-DD')),
partition p_2013_12 values less than(to_date('2014-01-01','YYYY-MM-DD')));
 
2.存储间隔分区历史数据的分区表t2
create table t2
partition by range(sale_date)(
partition p_2010_06 values less than(to_date('2010-07-01','YYYY-MM-DD')))
as select * from t where 1=2;

3.插入模拟数据
insert into t values (1001,'source',sysdate-30);
insert into t values (1002,'source',sysdate);
commit;

create index idx_his1 on t(SALE_ID);
create index idx_his2 on t2(SALE_ID);
create index idx2_his2 on t2 trunc(sale_date) local;

4.分区交换时用到的临时表
create table t_temp as select * from t where 1=2;

5.查看原始数据
select * from t partition (P_2012_11);
select * from t2;
select * from t_temp;

6.两次分区交换
--alter table t exchange partition p_2013_10 with table t_temp update indexes;
--alter table t exchange partition p_2013_10 with table t_temp including indexes with validation update indexes;

--select * from t partition (p_2013_10);
--select * from t2 partition (t_2013_10);
--select * from t_temp;

--alter table t2 exchange partition t_2013_10 with table t_temp including indexes with validation update indexes;

7.验证是否交换成功
select count(1) from t partition (p_2011_07);
select count(1) from t2 partition (t_2011_07);
select count(1) from t_temp;


8.用程序实现第6步自动分区交换
set serveroutput on
--只能从低月份到高月份依次转移,不能跨过中间一个月转移
declare
v_sql1 varchar2(1000);
v_sql2 varchar2(1000);
v_sql3 varchar2(1000);
v_sql4 varchar2(1000);
begin
for rec in (select partition_name,high_value from user_tab_partitions where table_name='T') loop
--把6个月前的数据交换到历史表
if substr(rec.high_value,11,7)=to_char(sysdate-6*30,'yyyy-mm') then
--to_char(sysdate-1)为某条记录中operate_time列的时间,可以根据某条记录查某个历史分区,也可以查两个天前是哪个分区
dbms_output.put_line(rec.partition_name);
dbms_output.put_line(substr(rec.high_value,11,10));
--t2添加分区
v_sql1:='alter table t2 add partition '||rec.partition_name||' values less than (to_date('''||substr(rec.high_value,11,10)||''',''yyyy-mm-dd'')) update indexes';
--分区交换,t的分区与t_temp交换,t_temp再与t2的分区交换
v_sql2:='alter table t exchange partition '||rec.partition_name||' with table t_temp update indexes';
v_sql3:='alter table t2 exchange partition '||rec.partition_name||' with table t_temp update indexes';
--删除t的已交换分区
v_sql4:='alter table t drop partition '||rec.partition_name||' update indexes';
--分区索引的话可能还需要重建分区索引alter index IDX2_HIS2 rebuild partition p_2013_09;
dbms_output.put_line(v_sql1);
dbms_output.put_line(v_sql2);
dbms_output.put_line(v_sql3);
dbms_output.put_line(v_sql4);
--备份需要转移的数据
execute immediate 'drop table t_temp purge';
execute immediate 'create table t_temp as select * from t partition ('||rec.partition_name||')';
execute immediate v_sql1;
execute immediate v_sql2;
execute immediate v_sql3;
execute immediate v_sql4;
end if;
end loop;
end;
/


--drop table t purge;
--drop table t2 purge;
--drop table t_temp purge;

  评论这张
 
阅读(237)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018