堆錶轉換成分割槽表

堆錶轉換成分割槽表

 

方法一：利用原表重建分割槽表

 

優點：

方法簡單易用，由於採用DDL語句，不會產生UNDO，且只產生少量REDO，效率相對較高，而且建表完成後資料已經在分佈到各個分割槽中了。

 

不足：

對於資料的一致性方面還需要額外的考慮。由於幾乎沒有辦法透過手工鎖定T表的方式保證一致性，在執行CREATE TABLE語句和RENAME T_NEW TO T語句直接的修改可能會丟失，如果要保證一致性，需要在執行完語句後對資料進行檢查，而這個代價是比較大的。另外在執行兩個RENAME語句之間執行的對T的訪問會失敗。

 

適用場景：

適用於修改不頻繁的表，在閒時進行操作，表的資料量不宜太大。

 

 

1 建立堆表，插入資料

SQL> create table t(id number primary key,time date);

Table created.

 

SQL> insert into t select rownum,created from dba_objects;

72632 rows created.

 

SQL> commit;

Commit complete.

 

2 建立分割槽表並載入T 表資料

SQL> create table t_new(id,time) partition by range(time)

     (partition p1 values less than(to_date('2009-07-01','yyyy-mm-dd')),

      partition p2 values less than(to_date('2010-07-01','yyyy-mm-dd')),

      partition p3 values less than(maxvalue))

     as select id,time from t;

Table created.

 

SQL> select count(*) from t_new partition(p1);

  COUNT(*)

----------

         0

 

SQL> select count(*) from t_new partition(p2);

  COUNT(*)

----------

     71812

 

SQL> select count(*) from t_new partition(p3);

  COUNT(*)

----------

       820

 

3 重新命名錶

SQL> rename t to t_old;

Table renamed.

 

SQL> rename t_new to t;

Table renamed.

 

SQL> select count(*) from t;

  COUNT(*)

----------

     72632

 

SQL> select count(*) from t partition(p2);

  COUNT(*)

----------

方法二：使用交換分割槽的方法

 

優點：

只是對資料字典中分割槽和表的定義進行了修改，沒有資料的修改或複製，效率最高。如果對資料在分割槽中的分佈沒有進一步要求的話，實現比較簡單。在執行完RENAME操作後，可以檢查T_OLD中是否存在資料，如果存在的話，直接將這些資料插入到T中，可以保證對T插入的操作不會丟失。

 

不足：

仍然存在一致性問題，交換分割槽之後RENAME T_NEW TO T之前，查詢、更新和刪除會出現錯誤或訪問不到資料。如果要求資料分佈到多個分割槽中，則需要進行分割槽的SPLIT操作，會增加操作的複雜度，效率也會降低。

 

適用場景：

適用於包含大資料量的錶轉到分割槽表中的一個分割槽的操作。應儘量在閒時進行操作。

 

1 建立堆表，插入資料

SQL> create table t(id number primary key,time date);

Table created.

 

SQL> insert into t select rownum,created from dba_objects;

72632 rows created.

 

2 建立分割槽表

SQL> create table t_new(id number primary key,time date) partition by range(time)

     (partition p1 values less than(to_date('2009-07-01','yyyy-mm-dd')),

      partition p2 values less than(to_date('2010-07-01','yyyy-mm-dd')),

      partition p3 values less than(maxvalue));

Table created.

 

3 交換分割槽

SQL> alter table t_new exchange partition p1 with table t;

alter table t_new exchange partition p1 with table t

                                                   *

ERROR at line 1:

ORA-14099: all rows in table do not qualify for specified partition

原因：資料中含有大於'2009-07-01'的資料,但是分割槽只能存放小區'2011-10-01'的資料,所以報錯

SQL> select min(time),max(time) from t;

 

MIN(TIME)           MAX(TIME)

------------------- -------------------

2009-08-15 00:16:51 2015-07-15 13:07:34

 

解決辦法：without validation( 儘量不使用，會是分割槽資料不準確)

SQL> alter table t_new exchange partition p1 with table t without validation;

Table altered.

 

4 重新命名

SQL> rename t to t_old;

Table renamed.

SQL> rename t_new to t;

Table renamed.

SQL> select count(*) from t partition(p1);

  COUNT(*)

----------

     72632

 

SQL> select count(*) from t partition(p2);

  COUNT(*)

----------

         0

SQL> select count(*) from t partition(p3);

  COUNT(*)

----------

         0

 

 

 

===============================================================================

三 利用線上重定義功能 (Oracle9i 以上版本 )

 

優點：

保證資料的一致性，在大部分時間內，表T都可以正常進行DML操作。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強的靈活性，對各種不同的需要都能滿足。而且，可以在切換前進行相應的授權並建立各種約束，可以做到切換完成後不再需要任何額外的管理操作。

 

不足：

實現上比上面兩種略顯複雜,適用於各種情況。

Oracle9i重定義過程的部分限制：

1 必須有足以維護兩份表格複製的空間。 　　

2 不能更改主鍵欄。 　　

3 表格必須有主鍵。 　　

4 必須在同一個大綱中進行表格重定義。 　　

5 在重定義操作完成之前，你不能對新加欄加以NOT NULL約束。

6 表格不能包含LONG、BFILE以及使用者型別（UDT）。 　　

7 不能重定義連結串列（clustered tables）。

8 不能在SYS和SYSTEM大綱中重定義表格。 　　

9 不能用具體化檢視日誌（materialized view logs）來重定義表格；不能重定義含有具體化檢視的表格。

10不能在重定義過程中進行橫向分集（horizontal subsetting）。

 

1 建立堆表，插入資料

SQL> create table t(id number primary key,time date);

Table created.

 

SQL> insert into t select rownum,created from dba_objects;

72632 rows created.

 

SQL> commit;

Commit complete.

 

2 不能重定義SYS ，SYSTEM 使用者下的表

SQL> exec dbms_redefinition.can_redef_table('SYS','T',dbms_redefinition.cons_use_pk);

BEGIN dbms_redefinition.can_redef_table('SYS','T',dbms_redefinition.cons_use_pk); END;

 

*

ERROR at line 1:

ORA-12087: online redefinition not allowed on tables owned by "SYS"

ORA-06512: at "SYS.DBMS_REDEFINITION", line 139

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1782

ORA-06512: at line 1

 

SQL> conn chen/chen

SQL> create table t(id number primary key,time date);

SQL> insert into t select rownum,created from dba_objects;

72631 rows created.

 

SQL> commit;

Commit complete.

 

3 判斷目標資料表是否可以進行重定義

可以使用dbms_redefinition包的can_redef_table方法進行判斷。

 

SQL> exec dbms_redefinition.can_redef_table('CHEN','T',dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

 

注意該方法的第三個引數，使用主鍵還是rowid方法。本質上，Online Redefinition是使用物化檢視Materialized View技術。過程定義記錄就是主鍵和rowid兩種策略。通常而言，我們還是推薦資料表有一個明確主鍵，也就是使用 cons_use_pk。如果希望使用rowid，就使用dbms_redefinition.cons_use_rowid。

透過了檢查之後，就可以進行下一步，定義目標資料表格式。無論是何種變化，我們需要建立一箇中間表interim，將我們“期望”的資料表定義實現在裡面。其中包括表型別、列定義、分割槽定義和索引等。但是注意，約束（主外來鍵）可以不定義在其中。

 

4 建立分割槽表

SQL> create table t_new(id number primary key,time date) partition by range(time)

     (partition p1 values less than(to_date('2009-07-01','yyyy-mm-dd')),

      partition p2 values less than(to_date('2010-07-01','yyyy-mm-dd')),

      partition p3 values less than(maxvalue));

Table created.

 

5 開始重定義過程

使用dbms_redefinition的start_redef_table方法。

SQL> exec dbms_redefinition.start_redef_table('CHEN','T','T_NEW')

PL/SQL procedure successfully completed.

 

SQL> exec dbms_redefinition.sync_interim_table('CHEN','T','T_NEW')

PL/SQL procedure successfully completed.

 

SQL> exec dbms_redefinition.finish_redef_table('CHEN','T','T_NEW')

PL/SQL procedure successfully completed.

 

6 檢視重定義效果

SQL> select count(*) from t;

  COUNT(*)

----------

     72631

 

SQL> select count(*) from t partition(p2);

  COUNT(*)

----------

     71812

 

SQL> col table_name for a5

SQL> col partition_name for a10

SQL> col tablespace_name for a10

SQL> select table_name,partition_name,tablespace_name from dba_tab_partitions where table_owner='CHEN';

 

TABLE PARTITION_ TABLESPACE

----- ---------- ----------

T     P3         USERS

T     P2         USERS

T     P1         USERS

TXN   PART1      T1

TXN   PART2      T2

TXN   PART3      T3

 

6 rows selected.

 

SQL> select table_name,partitioned from user_tables where partitioned='YES';

TABLE PARTIT

----- ------

TXN   YES

T     YES

 

如果重組織失敗，那麼你就必須採取特殊的步驟來讓它重新開始。由於重定義過程需要建立表格的快照，因此為了重新開始這一過程，你必須呼叫DBMS_REDEFINITION.ABORT_REDEF_TABLE來釋放快照。　　

DBMS_REDEFINITION.ABORT_REDEF_TABLE過程有三個引數，即使用者（schema）、原始表格（original table name）名稱以及持有表格名稱（holding table name）。它“出棧”並允許你開始重組織表格。

 

 

來自： http://www.cnblogs.com/hfliyi/p/3626302.html