使用dbms_redefinition包線上把大資料量非分割槽錶轉換為分割槽表

pxbibm發表於2015-04-13
本實驗是線上使用dbms_redefinition包把大資料量非分割槽錶轉換為分割槽表。它適用於
Oracle Database - Enterprise Edition - Version 11.2.0.2.0 以及以後的任何版本.

本文使用DBMS_REDEFINITION包提供了大量詳細的方法把普通表線上的轉換為分割槽表。

下面是詳細的操作步驟:

1.  建立一個臨時的分割槽表,表結構與要轉換的非分割槽表結構相同.
2.  執行 DBMS_REDEFINITION.can_redef_table...
3.  執行 DBMS_REDEFINITION.start_redef_table...
4.  執行 DBMS_REDEFINITION.sync_interim_table...
5.  執行 DBMS_REDEFINITION.finish_redef_table...
6.  刪除臨時的分割槽表 Drop the interim table.

詳細的操作步驟:

本實驗在 11.2.0.3平臺下做的。


--   建立非分割槽表,並初始化資料.

SQL>CREATE TABLE unpar_table (
  a NUMBER, y number,
  name VARCHAR2(100), date_used date);
-- 新增一主鍵
SQL>alter table unpar_table ADD (CONSTRAINT unpar_table_pk PRIMARY KEY (a,y));

--  載入資料 1,000,000 行

SQL>begin
        for i in 1 .. 1000
        loop
            for j in 1 .. 1000
            loop
                insert into unpar_table values ( i, j, dbms_random.random, sysdate-j );
            end loop;
        end loop;
end;
   /
commit;

PL/SQL procedure successfully completed.
Elapsed: 00:01:56.90

Commit complete.
--收集統計資訊
SQL> EXEC DBMS_STATS.gather_table_stats(user, 'unpar_table', cascade => TRUE);

SQL>SELECT   num_rows FROM user_tables WHERE table_name = 'UNPAR_TABLE';
  NUM_ROWS
----------
   1000000

Elapsed: 00:00:00.01
--建立分割槽表
SQL> CREATE TABLE par_table (
    a NUMBER, y number,
    name VARCHAR2(100),date_used DATE)
   PARTITION BY RANGE (date_used)
    (PARTITION unpar_table_12 VALUES LESS THAN (TO_DATE('10/07/2012', 'DD/MM/YYYY')),
     PARTITION unpar_table_15 VALUES LESS THAN (TO_DATE('15/07/2012', 'DD/MM/YYYY')),
     PARTITION unpar_table_MX VALUES LESS THAN (MAXVALUE));

SQL> EXEC Dbms_Redefinition.can_redef_table(USER, 'unpar_table');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

--  這個儲存過程包 (DBMS_REDEFINITION.start_redef_table) 會建立一個物化檢視.
 
SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.69

SQL> select mview_name,container_name, build_mode from user_mviews;
MVIEW_NAME                     CONTAINER_NAME                 BUILD_MOD
------------------------------ ------------------------------ ---------
PAR_TABLE                      PAR_TABLE                      PREBUILT

Elapsed: 00:00:00.13

 
--  當 DBMS_REDEF 為活動狀態是,我們向非分割槽表'unpar_table'插入1000資料.
--  我們會使用到 DBMS_REDEFINITION.start_redef_table建立的物化檢視記錄更新的資訊.
--  檢查物化檢視 MLOG$_

 確認是否有更新的記錄.
SQL> begin
        for i in 1001 .. 1010
        loop
            for j in 1001 .. 1100
            loop
                   insert into unpar_table values ( i, j, dbms_random.random, sysdate-j );
            end loop;
        end loop;
    end;
   /
commit;

Elapsed: 00:00:00.24

SQL> select count(*) from MLOG$_UNPAR_TABLE;

  COUNT(*)
----------
      1000

 
--  執行 dbms_redefinition.sync_interim_table基於物化檢視 FAST REFRESH
--  線上更新資料. 這將清空物化檢視中被應用的每一條記錄.
--  在我們執行Finish_REDEF_TABLE儲存過程包之前,我們可以執行多次.

SQL> BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.01

SQL> ALTER TABLE par_table ADD (CONSTRAINT par_table_pk2 PRIMARY KEY (a,y)); 


SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'par_table', cascade => TRUE);


--  執行儲存過程Finish_redef_table 包,將交換表名,臨時分割槽表名將變為原非分割槽表名.
--  執行完這步驟後, 原非分割槽表將基於臨時分割槽表重新定義表屬性和資料. 
--  在此期間原非分割槽表將會短暫的鎖住表.

SQL>BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/

 
--  注意,這個兩個表將同步資料.

SQL> select count(*) from par_table ;
  COUNT(*)
----------
   1001000

SQL> select count(*) from unpar_table ;
  COUNT(*)
----------
   1001000

 
--  從資料字典表中我們來確認非分割槽表 "UNPAR_TABLE"的結構變為了分割槽表.

SQL>SELECT partitioned FROM user_tables WHERE table_name = 'UNPAR_TABLE';

PAR
---
YES

SQL>SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'UNPAR_TABLE';


PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
UNPAR_TABLE_12                     980000
UNPAR_TABLE_15                       5000
UNPAR_TABLE_MX                      16000

 
--  此時我們完成了線上非分割槽表的轉換,我們可以刪除臨時分割槽表了.

SQL> drop TABLE par_table  cascade constraints;

 

注意先前的實驗版本是在 11.2.0.3完成的.


下面的一個測試是在 11.1.0.7 下,執行 sync_interim_table儲存包執行的時間,表的資料量為 (1,000,000 ) .

example:

11.1.0.7 
SYNC of 1000 rows --         Elapsed: 00:01:36.30     (1.5 minutes)

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

使用dbms_redefinition包線上把大資料量非分割槽錶轉換為分割槽表
請登入後發表評論 登入
全部評論

相關文章