ORACLE將不同表改為分割槽表

zangqianglei發表於2015-05-06
Oracle

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以上版本，利用線上重定義功能

    步驟：

  1.首先建立測試表，並插入測試資料： 

SQL> conn stream/stream
Connected.
SQL> create table t_test(id number,code varchar2(5),identifier varchar2(20));
Table created.
SQL> insert into t_test values(1,'01','01-01-0001-000001');  
1 row created.
SQL> insert into t_test values(2,'02','02-01-0001-000001');
1 row created.
SQL> insert into t_test values(3,'03','03-01-0001-000001');
1 row created.
SQL> insert into t_test values(4,'04','04-01-0001-000001');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t_test add constraint pk_test_id primary key (id);
Table altered

　　2.檢查下這張表是否可以線上重定義，無報錯表示可以，報錯會給出錯誤資訊： 

SQL> exec dbms_redefinition.can_redef_table('stream', 't_test');
PL/SQL procedure successfully completed

　　3.建立線上重定義需要的中間表，表結構就是要將原測試表重定義成什麼樣子，這裡建立的是按全宗號分割槽的分割槽表： 

SQL> create table t_temp(id number,code varchar2(5),
  2  identifier varchar2(20)) partition by list (code)
  3  (partition part01 values('01'),
  4  partition part02 values('02'),
  5  partition part03 values('03'),
  6  partition part04 values('04'),
  7  partition part05 values(default)
  8  );
Table created
SQL> alter table t_temp add constraint pk_temp_id primary key (id);
Table altered

　　4.啟動線上重定義： 

SQL> exec dbms_redefinition.start_redef_table('stream', 't_test', 't_temp');
PL/SQL procedure successfully completed

　　這裡dbms_redefinition包的start_redef_table模組有3個引數，分別是SCHEMA名字、原表的名字、中間表的名字。
5.啟動線上重定義後，中間表就可以查到原表的資料。 

SQL> select * from t_temp;
        ID CODE  IDENTIFIER
---------- ----- --------------------
         1 01    01-01-0001-000001
         2 02    02-01-0001-000001
         3 03    03-01-0001-000001
         4 04    04-01-0001-000001

　　6.由於在生成系統中，線上重定義的過程中原資料表可能會發生資料改變，向原表中插入資料模擬資料改變。 

SQL> insert into t_test values(5,'05','05-01-0001-000001');
1 row inserted
SQL> commit;
Commit complete

　　7.此時原表被修改，中間表並沒有更新。 

SQL> select * from t_test;
        ID CODE  IDENTIFIER
---------- ----- --------------------
         5 05    05-01-0001-000001
         1 01    01-01-0001-000001
         2 02    02-01-0001-000001
         3 03    03-01-0001-000001
         4 04    04-01-0001-000001
SQL>  select * from t_temp;
        ID CODE  IDENTIFIER
---------- ----- --------------------
         1 01    01-01-0001-000001
         2 02    02-01-0001-000001
         3 03    03-01-0001-000001
         4 04    04-01-0001-000001

　　8.使用dbms_redefinition包的sync_interim_table模組重新整理資料後，中間表也可以看到資料更改。 

SQL> exec dbms_redefinition.sync_interim_table('stream', 't_test', 't_temp');
PL/SQL procedure successfully completed
SQL> select * from t_test;
        ID CODE  IDENTIFIER
---------- ----- --------------------
         5 05    05-01-0001-000001
         1 01    01-01-0001-000001
         2 02    02-01-0001-000001
         3 03    03-01-0001-000001
         4 04    04-01-0001-000001
SQL> select * from t_temp;
        ID CODE  IDENTIFIER
---------- ----- --------------------
         1 01    01-01-0001-000001
         2 02    02-01-0001-000001
         3 03    03-01-0001-000001
         4 04    04-01-0001-000001
         5 05    05-01-0001-000001

　　9.結束線上重定義 

SQL> exec dbms_redefinition.finish_redef_table('stream', 't_test', 't_temp');
PL/SQL procedure successfully completed

　　10.驗證資料 

SQL> select * from t_test;
        ID CODE  IDENTIFIER
---------- ----- --------------------
         1 01    01-01-0001-000001
         2 02    02-01-0001-000001
         3 03    03-01-0001-000001
         4 04    04-01-0001-000001
         5 05    05-01-0001-000001
SQL> select * from t_temp;
        ID CODE  IDENTIFIER
---------- ----- --------------------
         5 05    05-01-0001-000001
         1 01    01-01-0001-000001
         2 02    02-01-0001-000001
         3 03    03-01-0001-000001
         4 04    04-01-0001-000001

　　11.檢視各分割槽資料是否正確 

SQL> select table_name, partition_name from user_tab_partitions where 
table_name = 'T_TEST';
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
T_TEST                         PART01
T_TEST                         PART02
T_TEST                         PART03
T_TEST                         PART04
T_TEST                         PART05
SQL> select * from t_test partition(part01);
        ID CODE  IDENTIFIER
---------- ----- --------------------
         1 01    01-01-0001-000001
SQL> select * from t_test partition(part02);
        ID CODE  IDENTIFIER
---------- ----- --------------------
         2 02    02-01-0001-000001
SQL> select * from t_test partition(part03);
        ID CODE  IDENTIFIER
---------- ----- --------------------
         3 03    03-01-0001-000001
SQL> select * from t_test partition(part04);
        ID CODE  IDENTIFIER
---------- ----- --------------------
         4 04    04-01-0001-000001
SQL> select * from t_test partition(part05);
        ID CODE  IDENTIFIER
---------- ----- --------------------
         5 05    05-01-0001-000001

　　12.線上重定義後，中間表已經沒有意義，刪掉中間表 

SQL> drop table t_temp purge;
Table dropped

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25954236/viewspace-1625648/，如需轉載，請註明出處，否則將追究法律責任。

相關文章