Oracle 將普通錶轉換為分區表

標籤：

DB:11.2.0.30

將普通錶轉換為區分表

一.利用原表重建分區表
SQL>create table yoon ( id number primary key ,time date );

Table created.

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

74930 rows created.

SQL>select count(*) from yoon;
COUNT(*)
----------
74930

SQL>create table yoon_new (ID,TIME) partition by range(time)
(partition p1 values less than (to_date(‘2011-10-01‘,‘YYYY-MM-DD‘)),
partition p2 values less than (to_date(‘2012-10-01‘,‘YYYY-MM-DD‘)),
partition p3 values less than (to_date(‘2013-10-01‘,‘YYYY-MM-DD‘)),
partition p4 values less than (MAXVALUE))
as select id,time from yoon;

Table created.

SQL>select table_owner,table_name,partition_name from dba_tab_partitions where table_name=‘YOON_NEW‘;

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
YOON                           YOON_NEW                       P1
YOON                           YOON_NEW                       P2
YOON                           YOON_NEW                       P3
YOON                           YOON_NEW                       P4

SQL>alter table yoon rename to yoon_old;

Table altered.

SQL>alter table yoon_new rename to yoon;

Table altered.

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

COUNT(*)
----------
     74445
     
SQL> select count(*) from yoon partition(p2);   

  COUNT(*)
----------
         0

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

  COUNT(*)
----------
         0
         
SQL> select count(*) from yoon partition(p4);

  COUNT(*)
----------
       485
       
優點：方法簡單、易用,由於採用DDL語句,不會產生UNDO,且只產生少量REDO,效率相對較高,而且建表完成後資料已經在分布到各個分區中了.
缺點：1.適用於修改不頻繁的表,在閑時進行操作,表的資料量不宜太大.
      2.表太大,在匯入資料的時候會產生大量的UNDO;非要採用這種方式,將資料分批次匯入.

 

二.交換分區
SQL> create table yoon ( id number primary key,time date ) ;

Table created.

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

74930 rows created.

SQL> commit;

Commit complete.

SQL> create table yoon_new ( id number primary key,time date ) partition by range(time)
  2  (partition p1 values less than (to_date(‘2015-10-01‘,‘YYYY-DD-MM‘)),
  3   partition p2 values less than (maxvalue));

Table created.

SQL> ALTER TABLE YOON_NEW EXCHANGE PARTITION P1 WITH TABLE YOON ;

Table altered.

SQL> select count(*) from yoon;

  COUNT(*)
----------
         0
         
SQL> select count(*) from yoon_new;

  COUNT(*)
----------
     74930
     
SQL> alter table yoon rename to yoon_old;

Table altered.

SQL> alter table yoon_new rename to yoon;

Table altered.

SQL> select table_owner,table_name,partition_name from dba_tab_partitions where table_name=‘YOON‘;

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
YOON                           YOON                           P1
YOON                           YOON                           P2

三.線上重定義

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

Table created.

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

74930 rows created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_REDEFINITION.can_redef_table(user,‘YOON‘,DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed.

SQL> create table yoon_new ( id number primary key,time date ) partition by range(time)
  2  (partition p1 values less than (to_date(‘2011-01-01‘,‘YYYY-DD-MM‘)),
  3   partition p2 values less than (to_date(‘2012-01-01‘,‘YYYY-DD-MM‘)),
  4   partition p3 values less than (to_date(‘2013-01-01‘,‘YYYY-DD-MM‘)),
  5   partition p4 values less than (maxvalue));

Table created.

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER,‘YOON‘,‘YOON_NEW‘,‘ID ID,TIME TIME‘,DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(‘YOON‘,‘YOON‘,‘YOON_NEW‘);

PL/SQL procedure successfully completed.

S
QL> select table_name from user_tables;

TABLE_NAME
------------------------------
YOON_NEW
YOON

SQL> select count(*) from yoon_new;

  COUNT(*)
----------
     74930

SQL> select count(*) from yoon;

  COUNT(*)
----------
     74930

SQL> select table_owner,table_name,partition_name from dba_tab_partitions where table_name=‘YOON‘;

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
YOON                           YOON                           P1
YOON                           YOON                           P2
YOON                           YOON                           P3
YOON                           YOON                           P4

線上重定義能夠保證資料的一致性,在大部分時間中,表YOON都可以正常進行DML操作,在切換的瞬間鎖表,具有狠高的可用性,具有狠強的靈活性,能夠滿足各種不同的需求.可以在切換前建立各種約束,做到切換後不用任何額外的管理操作.

Oracle 將普通錶轉換為分區表