巧妙使用exchange partition的一个案例(r6笔记第1天)

前几天写过一篇文章讨论过分区表的在线重定义,其实就是另外一个分区表和现有的分区表做数据字典信息的交换 http://blog.itpub.net/23718752/viewspace-1734195/ 当然了除此之外还是需要一些相应的权限,在这个过程中会在内部做类似物化视图一样的数据刷新,保持数据表的可访问性。 在不同的场景中还是会有不同的取舍,比如现在的场景,情况发生了一些变化,分区存在问题,要重新分区是肯定的,除此之外,开发希望把一些旧数据做一些清理,比如根据时间来分区,可能对于开发来说,最近一段时间的数据保留就可以了,之前很旧的数据就只是需要做一个备份,直接清掉,基本评估下来,可能90%以上的数据都需要做清理。 所以这个时候还是使用在线重定义就有一些弊端了。 首先是时间的问题,在线重定义的过程中,其实内部还是在做数据的复制工作。相当于把数据从一个源流动到另外一个源。内部是这样的数据刷新,对外保持始终可以访问,所以对于上亿条记录来说这个时间就比较长了。 其次就是效率问题,因为重新分区后,可能大部分数据都不需要了,这个时候做在线重定义还会做数据复制,然后在一定的时间之后还是需要再次做数据清理。这个时候相当于做了重复工作。效率会大大折扣。 这个时候根据目前的问题情况,考虑通过以下的方式来实现。 首先是数据内部的复制,为了减少这种开销,可以考虑把分区表的默认分区和一个普通表做交换,这个过程是一个数据字典级的变更,所以速度还是很快的。 然后可以交换后的分区做split partition的操作。这个时候split操作时间会持续极短。影响相对来说很小。 然后就是数据的清理,这个时候清理工作就会变为一种选择性的数据导入,因为需要导入的数据量还是很小的。所以只需要把需要时间范围内的数据导入即可。 我们来简单试一试。 还是之前所用的分区表,现在只有一个默认分区。

CREATE  TABLE tab_part  
   (  
   col1 varchar2(30),  
   col2 DATE  
   )   
   partition BY range(col2)  
   (  
     partition tab_part_maxvalue  values less than (maxvalue)
   ); 

我们希望达到的效果是下面的分区形式。

CREATE TABLE  tab_part  
   (  
   col1 varchar2(30),  
   col2 DATE  
   )   
   partition BY range(col2)  
   (  
   partition tab_part_2014 VALUES  less than (to_date('2014-08-01','yyyy-mm-dd')),  
   partition tab_part_2015  VALUES less than (to_date('2015-08-01','yyyy-mm-dd')),  
   partition  tab_part_maxvalue values less than (maxvalue)
    );

然后给分区表创建分区索引,然后我们简单插入几条数据。 create index inx_tab_part on tab_part(col1) local; INSERT INTO tab_part VALUES(1,SYSDATE-400); INSERT INTO tab_part VALUES(11,SYSDATE-600); commit; 数据情况如下: COL1 COL2 ------------------------------ --------- 1 09-APR-15 11 05-MAR-14 这个时候我们创建一个表test_tab01来做为中间过渡的一个表。

CREATE TABLE TEST_TAB01( col1 varchar2(30), col2 DATE); 这个时候就开始使用exchange partition来把分区的数据做个交换。

ALTER TABLE tab_part EXCHANGE PARTITION tab_part_maxvalue WITH TABLE TEST_TAB01 ; 交换的速度很快,来看看操作之后的数据情况,可以看到数据都到了test_tab01里面。

SQL> select count(*)from tab_part;

COUNT(*) ---------- 0

SQL> select count(*)from test_tab01;

COUNT(*) ---------- 2 这个时候分区还是没变,但是数据给交换出来了。 SQL> select table_name,partition_name from user_tab_partitions where table_name='TAB_PART';

TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ TAB_PART TAB_PART_MAXVALUE 我们接下来要做split partition的工作。

SQL> alter table tab_part SPLIT PARTITION tab_part_maxvalue at (to_date('2014-08-01','yyyy-mm-dd')) INTO ( PARTITION tab_part_2014 , PARTITION tab_part_maxvalue);

Table altered.

SQL> alter table tab_part SPLIT PARTITION tab_part_maxvalue at (to_date('2015-08-01','yyyy-mm-dd')) INTO ( PARTITION tab_part_2015 , PARTITION tab_part_maxvalue);

Table altered. 这个时候再来看分区的情况,就达到了我们预期的要求。

SQL> select table_name,partition_name from user_tab_partitions where table_name='TAB_PART';

TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ TAB_PART TAB_PART_2014 TAB_PART TAB_PART_2015 TAB_PART TAB_PART_MAXVALUE 如果对exchange partition还是有一定的疑虑,想索引会不会受到影响,如果是本地索引是没有问题的,如果是global全局索引,就需要rebuild

SQL> select index_name,partition_name,status from user_ind_partitions where index_name='INX_TAB_PART';

INDEX_NAME PARTITION_NAME STATUS ------------------------------ ------------------------------ -------- INX_TAB_PART TAB_PART_2014 USABLE INX_TAB_PART TAB_PART_2015 USABLE INX_TAB_PART TAB_PART_MAXVALUE USABLE

这个时候重新分区完成了,要做的事情就是导入数据了,选择性的导入数据,根据时间戳即可。 当然了好几亿条记录,不建个索引根本说不过去。 create index inx_test_tab01 on test_tab01(col); 我们就可以使用insert的方式导入数据即可。

insert into tab_part select *from test_tab01 where col>sysdate-10;

最后为了把exchange partiton的一个精髓突出出来和在线重定义区分开了,可以做一个很简单的小例子。 我们还是创建原来的表tab_part

CREATE TABLE tab_part ( col1 varchar2(30), col2 DATE ) partition BY range(col2) ( partition tab_part_maxvalue values less than (maxvalue) );

create index inx_tab_part on tab_part(col1) local; 我们接着创建一个表test_tab01,这个时候唯一的不同之处就是字段名不同,但是数据类型相同。

CREATE TABLE TEST_TAB01( a varchar2(30), b DATE); 然后尝试去做exchange partition的时候是没有问题的。

ALTER TABLE tab_part EXCHANGE PARTITION tab_part_maxvalue WITH TABLE TEST_TAB01 ; 我们看看交换分区之后的表结构变化情况

SQL> desc tab_part Name Null? Type ----------------------------------------- -------- ---------------------------- COL1 VARCHAR2(30) COL2 DATE

SQL> desc test_tab01 Name Null? Type ----------------------------------------- -------- ---------------------------- A VARCHAR2(30) B DATE

可以看到字段名没有任何变化,还是原来的字段名,但是在内部做了数据字典信息的交换,把相应的段信息做了交换而已。这也就exchange partition的主要思想。 通过这个案例可以看到,exchange partition还是大有可为,而且在很多场景下可以达到很满意的效果,在线重定义也不错,不过在选择的时候还是需要综合评定,没有最好的方法,最有最适合的方法。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-07-18

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏后端技术探索

一次非常有意思的sql优化经历

发现没有用到索引,type全是ALL,那么首先想到的就是建立一个索引,建立索引的字段当然是在where条件的字段。

8920
来自专栏沃趣科技

统计信息查询视图|全方位认识 sys 系统库

在上一篇《会话和锁信息查询视图|全方位认识 sys 系统库》中,我们介绍了如何使用 sys 系统库总的视图来查询会话状态信息以及锁等待信息,本期的内容先给大家介...

18400
来自专栏杨建荣的学习笔记

Oracle和MySQL竟然可以这么写这样的SQL?(r12笔记第99天)

今天看到Franck Pachot‏ 发了一个Twitter,意思是Oracle里的SQL还能这么写。猛一看确实让人有些意外。 ? 禁不住诱惑,自己也尝试了一番...

29950
来自专栏杨建荣的学习笔记

通过shell脚本来统计段大小(r5笔记第14天)

今天到公司之后,就收到客户的邮件,他们提供了一个列表,希望我们能够们配合提供一份比较详细的报告,得到某些表在生产环境中所占的空间大小,他们需要根据这些信息来分析...

42370
来自专栏技术博文

从MyISAM转到InnoDB需要注意什么

转自 MySql中文网 http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=200910426&idx=1...

375140
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第八篇(r3笔记第24天)

生产环境中的sql语句执行时间是很关键的性能指标,如果某个sql语句执行几个小时,优化以后几分钟,几十秒的话。会有很大的成就感,同时如果某个sql语句执行10秒...

28570
来自专栏北京马哥教育

五分钟 SQL Server 学习入门——基本篇

? 作者:My_heart_ 来源:http://blog.csdn.net/my_heart_/article/details/62425140 首先相信...

41540
来自专栏匠心独运的博客

大型分布式业务平台数据库优化方法(上)

文章摘要:一个小小的MySQL数据库B-Tree索引可能会带来意想不到的性能优化提升……

17240
来自专栏技术博客

Oracle初级索引学习总结

   索引是常见的数据库对象,建立索引的目的是为了提高记录的检索速度。它的设置好坏,使用是否得当,极大地影响数据库应用程序和Database的性能。虽然有许多资...

10620
来自专栏happyJared

爬虫进阶:Scrapy抓取科技平台Zealer

  这次的目标网站也是本人一直以来有在关注的科技平台:Zealer,爬取的信息包括全部的科技资讯以及相应的评论。默认配置下运行,大概跑了半个多小时,最终抓取了5...

15220

扫码关注云+社区

领取腾讯云代金券