【DB笔试面试474】普通表转换为分区表有哪些办法？

AiDBA宝典

发布于 2019-09-30 16:41:03

6950

发布于 2019-09-30 16:41:03

文章被收录于专栏：小麦苗的DB宝专栏

题目部分

普通表转换为分区表有哪些办法？

答案部分

将普通表转换成分区表有以下4种方法：

（1）导出/导入方法（Export/Import Method）

（2）子查询插入方法（Insert With a Subquery Method）

（3）分区交换方法（Partition Exchange Method）

（4）在线重定义方法（DBMS_REDEFINITION Method）

下面介绍一下这几种方法的主要过程：

1、导出/导入方法（Export/Import Method）

采用逻辑导出/导入很简单，首先在源库建立分区表，然后将数据导出，导入到新建的分区表即可。

（1）导出表：exp usr/pswd tables=T_TEST_LHR file=exp_lhr.dmp

（2）删除表：DROP TABLE T_TEST_LHR

（3）重建分区表的定义：

CREATE TABLE T_TEST_LHR(QTY NUMBER(3), NAME VARCHAR2(15))
  PARTITION BY RANGE (QTY) (PARTITION P1 VALUES LESS THAN (501),  PARTITION P2 VALUES LESS THAN (MAXVALUE));

（4）利用ignore=y来导入分区表：imp usr/pswd file=exp_lhr.dmp ignore=y

2、子查询插入方法（Insert With a Subquery Method）

主要过程如下所示，其中，T表是非分区表：

LHR@DLHR> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)
  2       (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),
  3        PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),
  4        PARTITION T3 VALUES LESS THAN (MAXVALUE))
  5   AS SELECT ID, TIME FROM T;

Table created.

然后改变表名：

LHR@DLHR> RENAME T_NEW TO T;
Table renamed.

3、分区交换方法（Partition Exchange Method）

主要过程有如下几个步骤：

创建分区表：

LHR@DLHR> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
  2    (PARTITION T1 VALUES LESS THAN (TO_DATE('2013-11-1', 'YYYY-MM-DD')),
  3     PARTITION T2 VALUES LESS THAN (MAXVALUE));
Table created.

交换数据：

LHR@DLHR> ALTER TABLE T_NEW EXCHANGE PARTITION T1 WITH TABLE T;
Table altered.

改变表名：

LHR@DLHR> RENAME T TO T_OLD;
Table renamed.
LHR@DLHR> RENAME T_NEW TO T;
Table renamed.

4、在线重定义方法（DBMS_REDEFINITION Method）

主要过程如下所示：

LHR@DLHR> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER,'T','T_NEW','ID ID,TIME TIME',DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
LHR@DLHR> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'T', 'T_NEW');
PL/SQL procedure successfully completed.

关于这几种方法的优缺点及适用情景如下表所示：

方法	主要过程	优点	缺点	适用情况
导出/导入方法（Export/Import Method）	采用逻辑导出/导入，首先在源库建立分区表，然后将数据导出，导入到新建的分区表即可。	操作简单。	整个实施过程中，目标表将不能进行DML操作。	该方法适用于业务量较小的表。
子查询插入方法（Insert With a Subquery Method）	采用CTAS的方式创建分区表，然后执行RENAME操作即可。	方法简单易用，由于采用DDL语句，不会产生Undo日志，且只会产生少量Redo日志，效率相对较高，而且建表完成后数据已经分布到各个分区中了。	对于数据的一致性方面需要做额外的考虑。在执行CREATE TABLE语句和RENAME T_NEW TO T（T_NEW为中间表）语句时，T表不能进行DML操作。如果要保证一致性，那么需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外，在执行RENAME语句时，其它会话将不能访问T表。	该方法适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。
分区交换方法（Partition Exchange Method）	执行分区交换命令“ALTER TABLE T_NEW EXCHANGE PARTITION T1 WITH TABLE T;”交换普通表和分区表的某个特定分区。	只对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，那么实现比较简单。在执行完RENAME操作后，可以检查T_OLD（T_OLD为中间表）中是否存在数据，如果存在的话，那么直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。	存在一致性问题，在交换分区之后到RENAME T_NEW TO T（T_NEW为中间表）之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，那么需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。	该方法适用于包含大数据量的表转到分区表中的一个分区的操作，应尽量在系统空闲时进行操作。
在线重定义方法（DBMS_REDEFINITION Method）	使用DBMS_REDEFINITION包进行在线转换。	保证数据的一致性，在大部分时间内，表T都可以正常执行DML操作，只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需求都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。	在实现上比其它几种方法略显复杂。	该方法适用于7*24系统环境。

1、导出/导入方法（Export/Import Method）

采用逻辑导出/导入很简单，首先在源库建立分区表，然后将数据导出，导入到新建的分区表即可。

（1）导出表：exp usr/pswd tables=T_TEST_LHR file=exp_lhr.dmp

（2）删除表：DROP TABLE T_TEST_LHR;

（3）重建分区表的定义：

    CREATE TABLE T_TEST_LHR(QTY NUMBER(3), NAME VARCHAR2(15))
    PARTITION BY RANGE (QTY)
    (PARTITION P1 VALUES LESS THAN (501),
     PARTITION P2 VALUES LESS THAN (MAXVALUE));

（4）利用ignore=y来导入分区表：imp usr/pswd file=exp_lhr.dmp ignore=y

2、子查询插入方法（Insert With a Subquery Method）

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

不足：对于数据的一致性方面需要做额外的考虑。在执行CREATE TABLE语句和RENAME T_NEW TO T（T_NEW为中间表）语句时，T表不能进行DML操作。如果要保证一致性，那么需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外，在执行RENAME语句时，其它会话将不能访问T表。

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

举一个例子，创建普通表并插入测试数据。

LHR@DLHR> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
Table created.
LHR@DLHR> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
87069 rows created.
LHR@DLHR> COMMIT;
Commit complete.
LHR@DLHR> SELECT TO_CHAR(T.TIME, 'YYYYMM'), COUNT(1)
  2    FROM T
  3   GROUP  BY TO_CHAR(T.TIME, 'YYYYMM');
TO_CHA   COUNT(1)
------ ----------
201310      85984
201605       1085

创建一个分区表。需要注意的是，这里的分区表的列后边没有数据类型。

LHR@DLHR> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)
  2       (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),
  3        PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),
  4        PARTITION T3 VALUES LESS THAN (MAXVALUE))
  5   AS SELECT ID, TIME FROM T;
Table created.

改变表名：

LHR@DLHR> RENAME T_NEW TO T;

Table renamed.

验证新表数据：

LHR@DLHR> SELECT TO_CHAR(T.TIME, 'YYYYMM'), COUNT(1)
  2    FROM T
  3   GROUP  BY TO_CHAR(T.TIME, 'YYYYMM'); 
TO_CHA   COUNT(1)
------ ----------
201310      85984
201605       1085

3、分区交换方法（Partition Exchange Method）

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

本方法的不足是存在一致性问题，在交换分区之后到RENAME T_NEW TO T（T_NEW为中间表）之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，那么需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。

本方法适用于包含大数据量的表转到分区表中的一个分区的操作，应尽量在系统空闲时进行操作。举个例子，创建普通表并插入测试数据的代码如下所示：

LHR@DLHR> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
Table created.
LHR@DLHR> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS WHERE CREATED<=to_date('201311','YYYYMM');
85984 rows created.
LHR@DLHR> COMMIT;
Commit complete.
LHR@DLHR> SELECT TO_CHAR(T.TIME, 'YYYYMM'), COUNT(1)
  2    FROM T
  3   GROUP  BY TO_CHAR(T.TIME, 'YYYYMM'); 
TO_CHA   COUNT(1)
------ ----------
201310      85984

创建分区表：

LHR@DLHR> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
  2    (PARTITION T1 VALUES LESS THAN (TO_DATE('2013-11-1', 'YYYY-MM-DD')),
  3     PARTITION T2 VALUES LESS THAN (MAXVALUE));
Table created.

交换数据：

LHR@DLHR> ALTER TABLE T_NEW EXCHANGE PARTITION T1 WITH TABLE T;
Table altered.

改变表名：

LHR@DLHR> RENAME T TO T_OLD;
Table renamed.
LHR@DLHR> RENAME T_NEW TO T;
Table renamed.

查询数据：

LHR@DLHR> SELECT TO_CHAR(T.TIME, 'YYYYMM'), COUNT(1)
  2    FROM T
  3   GROUP  BY TO_CHAR(T.TIME, 'YYYYMM');
TO_CHA   COUNT(1)
------ ----------
201310      85984

4、在线重定义方法（DBMS_REDEFINITION Method）

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

本方法的不足之处是在实现上比上面几种方法略显复杂。

本方法适用于各种情况。举个例子，创建普通表。

LHR@DLHR> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
Table created.
LHR@DLHR> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
87073 rows created.

然后执行：

LHR@DLHR> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
LHR@DLHR> SELECT TO_CHAR(T.TIME, 'YYYYMM'), COUNT(1)
  2    FROM T
  3   GROUP  BY TO_CHAR(T.TIME, 'YYYYMM');
TO_CHA   COUNT(1)
------ ----------
201310      85984
201605       1089

创建分区表：

LHR@DLHR> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
  2       (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),
  3        PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),
  4        PARTITION T3 VALUES LESS THAN (MAXVALUE));
Table created.

然后执行：

LHR@DLHR> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER,'T','T_NEW','ID ID,TIME TIME',DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
LHR@DLHR> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'T', 'T_NEW');
PL/SQL procedure successfully completed.

& 说明：

有关普通表转换为分区表的具体操作过程可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2109454/

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。

About Me：小麦苗

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-01-03，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

本文分享自 DB宝微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

sql

登录后参与评论

0 条评论

热度

【DB笔试面试474】普通表转换为分区表有哪些办法？

【DB笔试面试474】普通表转换为分区表有哪些办法？

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐