专栏首页小麦苗的DB宝专栏【DB笔试面试474】普通表转换为分区表有哪些办法?

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

题目部分

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

答案部分

将普通表转换成分区表有以下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/

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

● 版权所有,欢迎分享本文,转载请保留出处

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

本文分享自微信公众号 - DB宝(xiaomaimiaolhr)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-01-03

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • prometheus 统计MySQL 自增主键的剩余可用百分比

    最近生产环境一套数据库因为疯狂写日志数据,造成主键值溢出的情况出现,因此有必要将这个指标监控起来。

    二狗不要跑
  • MySQL数据全量导入PG的方法

    下面会用到一个perl脚本来做数据格式的转换: https://github.com/ahammond/mysql2pgsql

    二狗不要跑
  • windows上安装 MySQL-python包的方法

    安装过程中,遇到很多问题,终于解决了,贴下我的解决步骤。也为了其它小伙伴们少采坑。

    二狗不要跑
  • postgres中mysql_fdw 扩展的使用

    参考连接: https://www.percona.com/blog/2018/08/24/postgresql-accessing-mysql-as-a-da...

    二狗不要跑
  • MongoDB中删除document的方法

    > db.users.remove({z:'abc'}) 删除记录 delete from users where z="abc" 默认remove没有带选项t...

    二狗不要跑
  • 一文带你揭开Redis复制原理的神秘面纱

    墨墨导读:本文在依托Redis主从环境下,针对访问的数据一致性进行分析,解开Redis复制原理的神秘面纱。‍

    数据和云
  • Java SPI机制浅析

    在平时开发项目的过程中,相信很多读者都看到过这样的目录,/META-INF/services目录,该目录下的文件名是接口的全称,其内容是具体的接口实现。这就是使...

    孟君
  • 一种基于proxysql的数据脱敏思路

    背景:我们这边给研发查数据的是通过phpmyadmin进行的,通常情况下研发人员查数据写法是 select * from db1.tb1 where id=xx...

    二狗不要跑
  • DBLE分库分表实战

    OS版本: CentOS Linux release 7.6.1810 (Core) 

    二狗不要跑
  • PG中只读账号的授权操作

    但是,只读账号稍微费事点,如果我们处理不好的话,每次新加表都要再执行一次对只读账号的重新授权操作。好在PG为我们考虑好了这个场景,也是有方法解决的。

    二狗不要跑

扫码关注云+社区

领取腾讯云代金券