【DB筆試面試474】普通錶轉換為分區表有哪些辦法？

  • 2019 年 10 月 11 日
  • 筆記

題目部分

普通錶轉換為分區表有哪些辦法？

答案部分

將普通錶轉換成分區表有以下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/

● 本系列題目來源於作者的學習筆記，部分整理自網絡，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

● 題目解答若有不當之處，還望各位朋友批評指正，共同進步

VirMach 便宜 VPS

QNews

QNews