ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 普通表转换为分区表

普通表转换为分区表

原创 Linux操作系统 作者:杨奇龙 时间:2011-03-21 23:18:48 0 删除 编辑
yang@ORACL> create table yangtmp ( id number, time date );
表已创建。
yang@ORACL> insert into yangtmp select rownum id ,sysdate-dbms_random.value(1,500) time
  2  from dual
  3  connect by level <=1e5;

已创建100000行。
yang@ORACL> select count(1) from yangtmp;
  COUNT(1)
----------
    100000
yang@ORACL> commit;
提交完成。

yang@ORACL> create table yang_part (
  2             id number,
  3             time date
  4  )
  5  partition by range (time) (
  6             partition p2010 values less than (to_date('20110101','yyyymmdd')),
  7             partition p201101 values less than (to_date('20110401','yyyymmdd')),
  8             partition p201102 values less than (to_date('20110701','yyyymmdd')),
  9             partition p201103 values less than (to_date('20111001','yyyymmdd')),
 10             partition p201104 values less than (to_date('20120101','yyyymmdd'))
 11  );

表已创建。
yang@ORACL> rename yangtmp to yang_old;
表已重命名。
yang@ORACL> rename yang_part to yang_tmp;
表已重命名。
yang@ORACL> select count(1) from yang_old;
  COUNT(1)
----------
    100000
yang@ORACL> select count(1) from yang_tmp;
  COUNT(1)
----------
         0
yang@ORACL> rename yang_tmp to yang_part;
表已重命名。

yang@ORACL> insert into yang_part  select id,time from  yang_old;
已创建100000行。
yang@ORACL> rename yang_part to yang_tmp;
表已重命名。
yang@ORACL> select count(1) from yang_old;
  COUNT(1)
----------
    100000
yang@ORACL> select count(1) from yang_tmp;
  COUNT(1)
----------
    100000

yang@ORACL> select count(1) from yang_tmp partition (p2010);
  COUNT(1)
----------
     85142
yang@ORACL> select count(1) from yang_tmp partition (p201101);
  COUNT(1)
----------
     14858
yang@ORACL> select count(1) from yang_tmp partition (p201102);
  COUNT(1)
----------
         0

yang@ORACL> select count(1) from yang_tmp partition (p201103);
  COUNT(1)
----------
         0

yang@ORACL> select count(1) from yang_tmp partition (p201104);
  COUNT(1)
----------
         0


优点:方法简单易用,由于采用DDL语句,不会产生UNDO,且只产生少量REDO,效率相对较高,而且建表完成后数据已经在分布到各个分区中了。
不足:对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性,在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失,如果要保证一致性,需要在执行完语句后对数据进行检查,而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。
适用于修改不频繁的表,在闲时进行操作,表的数据量不宜太大。
yang@rac1>create table yangtmp (id  number  primary key ,time date);
表已创建。
已用时间:  00: 00: 00.44
yang@rac1>insert into yangtmp select rownum id,sysdate -dbms_random.value(2,500) time
  2  from dual
  3  connect by level <=1e4;
已创建10000行。
已用时间:  00: 00: 00.29
yang@rac1>commit;
提交完成。
已用时间:  00: 00: 00.02
yang@rac1>create table yang_part (
  2                 id number,
  3                 time date
  4      )
  5      partition by range (time) (
  6                 partition p2010 values less than (to_date('20110101','yyyymmdd')),
  7                 partition p201101 values less than (to_date('20110401','yyyymmdd')),
  8                 partition p201102 values less than (to_date('20110701','yyyymmdd')),
  9                 partition p201103 values less than (to_date('20111001','yyyymmdd')),
 10                 partition p201104 values less than (to_date('20120101','yyyymmdd')) );

表已创建。
已用时间:  00: 00: 00.09
yang@rac1>select count(*) from yang_part;
  COUNT(*)
----------
         0
已用时间:  00: 00: 00.01
yang@rac1>alter table yang_part exchange partition p201104 with table yangtmp;
alter table yang_part exchange partition p201104 with table yangtmp
                                                            *
第 1 行出现错误:
ORA-14099: 未对指定分区限定表中的所有行
yang@rac1>alter table yang_part exchange partition p201104 with table yangtmp without validation;
表已更改。
已用时间:  00: 00: 00.11
yang@rac1>select count(1) from yang_part;
  COUNT(1)
----------
     10000
已用时间:  00: 00: 00.00
yang@rac1>select count(1) from yang_part partition (p201104);
  COUNT(1)
----------
     10000
已用时间:  00: 00: 00.01
yang@rac1>select count(1) from yang_part partition (p201103);

  COUNT(1)
----------
         0
已用时间:  00: 00: 00.00
yang@rac1>rename yangtmp to yangtmp2;
表已重命名。
已用时间:  00: 00: 00.16
yang@rac1>rename yang_part to yangtmp;
表已重命名。
已用时间:  00: 00: 00.00
yang@rac1>select count(1) from yangtmp;
  COUNT(1)
----------
     10000
已用时间:  00: 00: 00.01
yang@rac1>select count(1) from yangtmp2;
  COUNT(1)
----------
         0
已用时间:  00: 00: 00.00
优点:只是对数据字典中分区和表的定义进行了修改,没有数据的修改或复制,效率最高。如果对数据在分区中的分布没有进一步要求的话,实现比较简单。在执行完RENAME操作后,可以检查T_OLD中是否存在数据,如果存在的话,直接将这些数据插入到T中,可以保证对T插入的操作不会丢失。
不足:仍然存在一致性问题,交换分区之后RENAME T_NEW TO T之前,查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中,则需要进行分区的SPLIT操作,会增加操作的复杂度,效率也会降低。
适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。
yang@rac1>create table tabyang ( id number primary key ,time date);
表已创建。
已用时间:  00: 00: 00.01
yang@rac1>insert into tabyang select rownum id,sysdate-dbms_random.value(2,500) time
  2  from dual
  3  connect by level <=1e4;
已创建10000行。
已用时间:  00: 00: 00.11
yang@rac1>commit;
提交完成。
已用时间:  00: 00: 00.02
yang@rac1>EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'TABYANG', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.21
yang@rac1>create table yangpart (
  2                 id number,
  3                 time date
  4      )
  5      partition by range (time) (
  6                 partition p2010 values less than (to_date('20110101','yyyymmdd')),
  7                 partition p201101 values less than (to_date('20110401','yyyymmdd')),
  8                 partition p201102 values less than (to_date('20110701','yyyymmdd')),
  9                 partition p201103 values less than (to_date('20111001','yyyymmdd')),
 10                 partition p201104 values less than (to_date('20120101','yyyymmdd')) );

表已创建。

已用时间:  00: 00: 00.02
yang@rac1>EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'TABYANG', 'YANGPART', 'ID ID, TIME TIME', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL 过程已成功完成。
已用时间:  00: 00: 02.60
yang@rac1>EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'TABYANG', 'YANGPART');
PL/SQL 过程已成功完成。
已用时间:  00: 00: 01.10
yang@rac1>SELECT COUNT(*) FROM YANGPART;
  COUNT(*)
----------
     10000
已用时间:  00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM TABYANG;
  COUNT(*)
----------
     10000
已用时间:  00: 00: 00.28
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (P20110401);
SELECT COUNT(*) FROM YANGPART PARTITION (P20110401)
                     *
第 1 行出现错误:
ORA-14501: 对象未分区


已用时间:  00: 00: 00.18
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (P201104);
SELECT COUNT(*) FROM YANGPART PARTITION (P201104)
                     *
第 1 行出现错误:
ORA-14501: 对象未分区


已用时间:  00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (P201103);
SELECT COUNT(*) FROM YANGPART PARTITION (P201103)
                     *
第 1 行出现错误:
ORA-14501: 对象未分区


已用时间:  00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (P201101);
SELECT COUNT(*) FROM YANGPART PARTITION (P201101)
                     *
第 1 行出现错误:
ORA-14501: 对象未分区


已用时间:  00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM YANGPART PARTITION (p201101);
SELECT COUNT(*) FROM YANGPART PARTITION (p201101)
                     *
第 1 行出现错误:
ORA-14501: 对象未分区


已用时间:  00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p201101);

  COUNT(*)
----------
      1580
已用时间:  00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p201102);
  COUNT(*)
----------
         0
已用时间:  00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p201103);
  COUNT(*)
----------
         0

已用时间:  00: 00: 00.00
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p201104);
  COUNT(*)
----------
         0
已用时间:  00: 00: 00.01
yang@rac1>SELECT COUNT(*) FROM TABYANG PARTITION (p2010);
  COUNT(*)
----------
      8420
已用时间:  00: 00: 00.01
优点:保证数据的一致性,在大部分时间内,表TABYANG都可以正常进行DML操作。只在切换的瞬间锁表,具有很高的可用性。这种方法具有很强的灵活性,对各种不同的需要都能满足。而且,可以在切换前进行相应的授权并建立各种约束,可以做到切换完成后不再需要任何额外的管理操作。
不足:实现上比上面两种略显复杂。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22664653/viewspace-690114/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7698287