普通表转换为分区表

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操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。
不足：实现上比上面两种略显复杂。

时间： 2024-12-22 10:05:35

普通表转换为分区表的相关文章

【分区】如何将一个普通表转换为分区表

[分区]如何将一个普通表转换为分区表   1.1  BLOG文档结构图   1.2  前言部分   1.2.1  导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ① 将一个普通表转换为分区表的常用方法(重点) ② 在线重定义的使用 ③ ctas和insert的优化 ④ DML语句如何开启并行操作,如何查看DML是否开启了并行     Tips:        ① 若文章代码格式有错乱,推荐使用QQ.搜狗或360浏览器,也

MySQL普通表转换为分区表（MySQL-5.6.25,MySQL-5.7.7-rc）

考虑到设计人员没有规划好表的容量,某些业务表体积过大以后,需要将以前的表转换为分区表的需求,简单记录一下MySQL的分区表转换功能,测试环境:MySQL-5.6.25和MySQL-5.7.7-rc本着学习的原则,先把百度方法中的,中间表->锁表->导数据->rename,放在一边,先试着能否直接用alter来转换表结构,类似于oracle的直接转换 先在MySQL-5.7.7-rc环境下建表 插入数据 试一下如下语句:ALTER TABLE students PARTITION BY K

使用exchange方式切换普通表到分区表

      随着数据库数据量的不断增长,有些表需要由普通的堆表转换为分区表的模式.有几种不同的方法来对此进行操作,诸如导出表数据,然后创建分区表再导入数据到分区表:使用EXCHANGE PARTITION方式来转换为分区表以及使用DBMS_REDEFINITION来在线重定义分区表.本文描述的是使用EXCHANGE PARTITION方式来实现,下面是具体的操作示例.       有关具体的dbms_redefinition在线重定义表的原理及步骤可参考:基于 dbms_redefinition

使用DBMS_REDEFINITION在线切换普通表到分区表

      随着数据库数据量的不断增长,有些表需要由普通的堆表转换为分区表的模式.有几种不同的方法来对此进行操作,诸如导出表数据,然后创建分区表再导入数据到分区表:使用EXCHANGE PARTITION方式来转换为分区表以及使用DBMS_REDEFINITION来在线重定义分区表.本文描述的是使用DBMS_REDEFINITION来实现,下面是具体的操作示例.       有关具体的dbms_redefinition在线重定义表的原理及步骤可参考:基于 dbms_redefinition 在线

Oracle 表在线重定义之普通表到分区表

对于一个7*24的在线生产系统来说,修改表定义(DDL)时一件非常痛苦的时,因为如果直接使用 alter语句,将会在表上安放一个排他锁,也就是说在这期间所有的DML和select都无法操作,如果是一 个大表,alter的时间将很长,在这期间应用会受到很大的影响. 幸好,从9i开始,Oracle提供了在线表重定义功能,在修改表定义的同时几乎不影响DML和select语 句,因为排他锁只会在表上出现很短时间.但是在线表重定义需要额外的空间(大致等于原表空间大小 ). 在线表重定义具有如下功能: 修改

php+mysql如何将数据表转换为Excel输出

php教程 mysql教程如何将数据表转换为Excel输出 $xlsname="倒出信息"; header("Content-type:application/vnd.ms-excel"); header("Content-Disposition:filename=$xlsname.xls");  ?> <table>   <tr>   <td ><h3>序号</h3></t

[20120926]普通表变成分区表.txt

[20120926]普通表变成分区表.txt 生产系统有1个表很大,占到系统40%空间.我的想法是改成分区表,一些不修改的数据变成只读,仅仅备份一次,以后不再备份, 这样减少备份时间.这里做一个测试,看看这样操作是否可行. SQL> select * from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g

Oracle 分区表的新增、修改、删除、合并。普通表转分区表方法

一. 分区表理论知识 Oracle提供了分区技术以支持VLDB(Very Large DataBase).分区表通过对分区列的判断,把分区列不同的记录,放到不同的分区中.分区完全对应用透明. Oracle的分区表可以包括多个分区,每个分区都是一个独立的段(SEGMENT),可以存放到不同的表空间中.查询时可以通过查询表来访问各个分区中的数据,也可以通过在查询时直接指定分区的方法来进行查询. When to Partition a Table什么时候需要分区表,官网的2个建议如下: Tables

Oracle中创建临时表、外部表和分区表实例

临时表 在Oracle中,临时表是"静态"的,它与普通的数据表一样只需要一次创建,其结构从创建到删除的整个期间都是有效的.相对于其他类型的表,临时表只有在用户实际向表中添加数据时,才会为其分配空间,并且分配的空间来自临时表空间.这就避免了与永久对象的数据争用存储空间. 创建临时表的语法如下: CREATE GLOBAL TEMPORARY TABLE table_name( column_name data_type,[column_name data_type,...] )ON CO