`

oracle将普通表改为分区表【转】

阅读更多
转自http://wenku.baidu.com/view/8591c149e45c3b3566ec8b04.html

Oracle的普通表没有办法通过修改属性的方式直接转化为分区表,必须通过重建的方式进行转变,下面介绍三种效率比较高的方法,并说明它们各自的特点。

方法一:利用原表重建分区表。

步骤:

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已创建6264行。

SQL> COMMIT;

提交完成。

SQL> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)

  2  (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),

  3  PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),

  4  PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),

  5  PARTITION P4 VALUES LESS THAN (MAXVALUE))

  6  AS SELECT ID, TIME FROM T;

表已创建。

SQL> RENAME T TO T_OLD;

表已重命名。

SQL> RENAME T_NEW TO T;

表已重命名。

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)

----------

      6264

SQL> SELECT COUNT(*) FROM T PARTITION (P1);

  COUNT(*)

----------

         0

SQL> SELECT COUNT(*) FROM T PARTITION (P2);

  COUNT(*)

----------

      6246

SQL> SELECT COUNT(*) FROM T PARTITION (P3);

  COUNT(*)

----------

        18

优点:方法简单易用,由于采用DDL语句,不会产生UNDO,且只产生少量REDO,效率相对较高,而且建表完成后数据已经在分布到各个分区中了。

不足:对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性,在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失,如果要保证一致性,需要在执行完语句后对数据进行检查,而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。

适用于修改不频繁的表,在闲时进行操作,表的数据量不宜太大。


方法二:使用交换分区的方法。

步骤:

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已创建6264行。

SQL> COMMIT;

提交完成。

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)

  2  (PARTITION P1 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),

  3  PARTITION P2 VALUES LESS THAN (MAXVALUE));

表已创建。

SQL> ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;

表已更改。

SQL> RENAME T TO T_OLD;

表已重命名。

SQL> RENAME T_NEW TO T;

表已重命名。

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)

----------

      6264

优点:只是对数据字典中分区和表的定义进行了修改,没有数据的修改或复制,效率最高。如果对数据在分区中的分布没有进一步要求的话,实现比较简单。在执行完RENAME操作后,可以检查T_OLD中是否存在数据,如果存在的话,直接将这些数据插入到T中,可以保证对T插入的操作不会丢失。

不足:仍然存在一致性问题,交换分区之后RENAME T_NEW TO T之前,查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中,则需要进行分区的SPLIT操作,会增加操作的复杂度,效率也会降低。

适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。


方法三:Oracle9i以上版本,利用在线重定义功能

步骤:

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已创建6264行。

SQL> COMMIT;

提交完成。

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL 过程已成功完成。

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)

  2  (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),

  3  PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),

  4  PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),

  5  PARTITION P4 VALUES LESS THAN (MAXVALUE));

表已创建。

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_NEW', -

> 'ID ID, TIME TIME', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('YANGTK', 'T', 'T_NEW');

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)

----------

      6264

SQL> SELECT COUNT(*) FROM T PARTITION (P2);

  COUNT(*)

----------

      6246

SQL> SELECT COUNT(*) FROM T PARTITION (P3);

  COUNT(*)

----------

        18

优点:保证数据的一致性,在大部分时间内,表T都可以正常进行DML操作。只在切换的瞬间锁表,具有很高的可用性。这种方法具有很强的灵活性,对各种不同的需要都能满足。而且,可以在切换前进行相应的授权并建立各种约束,可以做到切换完成后不再需要任何额外的管理操作。

不足:实现上比上面两种略显复杂。

适用于各种情况。
分享到:
评论

相关推荐

    oracle普通表转化为分区表的方法

    总的来说,Oracle的在线重定义功能提供了一种高效且低影响的方式将普通表转换为分区表,尤其适用于大型表,它可以显著提升数据管理效率和查询性能。在进行这种转换时,应根据具体的业务需求和数据规模选择合适的方法...

    BLOG_如何将一个普通表转换为分区表.pdf

    而将一个普通的数据库表转换为分区表,可以让数据库管理员对数据进行更精细化的管理。 从提供的文件内容可以看出,本文主要介绍的是如何将一个非分区表转换为分区表,其中涉及了四种不同的方法,并且强调了每种方法...

    oracle数据表分区知识

    #### 六、普通表转换为分区表 如果现有的表不是分区表,可以通过以下步骤将其转换为分区表: 1. **创建临时分区表**:首先创建一个新的分区表结构,但不包含任何数据。 2. **导入数据**:使用 `INSERT INTO ... ...

    Oracle9i分区存储

    通过这种方式,可以有效地将一个普通表转换为分区表,从而提高数据管理效率和查询性能。需要注意的是,在实际操作过程中还需要考虑数据类型、索引以及触发器等因素的影响。此外,对于大型生产环境而言,还需仔细规划...

    Oracle分区表在油田勘探开发数据库中的应用.pdf

    Oracle分区表是数据库管理系统Oracle中一种优化大数据处理的高级特性,尤其在处理大规模数据的油田勘探开发数据库中显得尤为重要。油田开发过程中产生的数据量巨大,随着时间的推移,数据会持续增长,这对查询效率、...

    oracle学习经典教程

    关于分区表,文档详细解释了分区表的理论知识以及如何将普通表转换为分区表。分区表能够将数据分布在不同的分区中,从而优化查询和维护操作。文档提到了多种分区表的操作方法,包括插入、交换分区、使用在线重定义、...

    老白ORACLE面试题

    8. 在将普通表转换为分区表时,应该基于数据的访问模式、数据量的大小、表的分区键数据的分布等因素来选择分区键。 9. 重新设计表的索引时,要分析当前索引的使用情况,包括查询类型、过滤条件、索引的碎片率和选择...

    Oracle Concept 11gR2

    Oracle支持多种类型的表,包括普通表、对象表、临时表和外部表。它们的存储结构和用途各不相同,为不同的应用场景提供了灵活性。 索引用于提升数据检索的速度。Oracle支持多种索引类型,比如B-树索引、位图索引、...

    oracle数据库对象管理笔记.doc

    * 分区表(Partitioned Table):分区表是一种特殊的表类型,它可以将一个大表分成多个小表,每个小表对应一个分区。 * 索引组织表(Index Organized Table):索引组织表是一种特殊的表类型,它的主键列是有序的,...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。  拉里•埃里森  就业前景 从就业与择业的...

    ORACLE闪回技术恢复指南

    通过以上介绍,我们可以看出Oracle闪回技术为用户提供了一种高效且灵活的方式来恢复误删或误改的数据。无论是单个表还是整个数据库,这些工具都大大提高了数据安全性和可用性。希望本文能帮助您更好地理解和应用...

    在裸设备上安装Oracle 10g R2

    - 使用Oracle管理工具,如`sqlplus`或`企业管理器`,验证数据库实例、表空间和数据文件是否正确指向裸设备。 这种在裸设备上安装Oracle的方式虽然可以提高性能,但也需要谨慎操作,因为一旦错误配置,可能会导致...

    Oracle自学(学习)材料 (共18章 偏理论一点)

    11 管理表 目标 11-2 储存用户数据 11-3 Oracle 数据类型 11-5 ROWID 格式 11-8 行的结构 11-10 创建一张表 11-11 创建临时表 11-13 创建表:指南 11-14 修改储存参数 11-15 手工分配片 11-16 重构非分区表 11-17 ...

    ORACLE9i_优化设计与系统调整

    §10.7 哪种类型的表设为cache 方式 116 §10.8 数据表和索引分开原则 116 §10.9 是否采用簇和分区 116 §10.10 表和索引的空间预分配 116 §10.11 确定数据库对象存储大小 117 §10.11.1 非簇表的大小计算 117 §...

    Oracle_11gR2_2-表和表簇

    - **表存储**:表的存储方式对性能有重要影响,可以配置为不同的存储选项,如压缩、分区等。 #### 表簇概述 表簇是一种特殊的表存储技术,用于提高多表查询的性能。 - **索引化表簇概述**:索引化表簇使用索引...

Global site tag (gtag.js) - Google Analytics