線上重定義分割槽表和NOLOGGING APPEND分割槽表對比

1、模擬大表
create table tes_p
(it int,
 ic int,
 im varchar2(20));

declare 
   i number(10);
begin
  for i in 1..1000000
  loop
  insert into tes_p
   values(i,i,'test');
  end loop;
end;
commit;
建立主鍵
alter table tes_p add constraint pk_t primary key (it);
方法1：線上重定義
建立中間表(分割槽表，你想得到的格式）
 CREATE TABLE scubagear
     (it int ,
      ic int ,
      im varchar2(20))
 PARTITION BY RANGE (ic)
     ( PARTITION s_q1 VALUES LESS THAN (333333),
       PARTITION s_q2 VALUES LESS THAN (666666),
       PARTITION s_q3 VALUES LESS THAN (10000002))

驗證原表是否可以線上遷移
 exec dbms_redefinition.can_redef_table('ppzhu','tes_p');
新開啟一個會話驗證當前會話沒有REDO產生
 select c.sid,b.NAME,a.VALUE from v$sesstat a ,v$statname b ,(select SID from v$mystat where rownum<=1) c
   where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and   b.name like '%redo%' and a.VALUE<>0 ;
 
       SID NAME                                                                  VALUE
---------- ---------------------------------------------------------------- ----------
 
在新開會話進行遷移
SQL> exec dbms_redefinition.start_redef_table('ppzhu','tes_p','scubagear'); -8S
 
PL/SQL procedure successfully completed
 
SQL> exec dbms_redefinition.sync_interim_table('ppzhu','tes_p','scubagear');
 
PL/SQL procedure successfully completed
 
SQL> exec dbms_redefinition.finish_redef_table('ppzhu','tes_p','scubagear'); -1s
 
PL/SQL procedure successfully completed
檢視資料已經移動完成，索引已經遷移
最後由於分割槽表並沒有主鍵，我們建立主鍵
alter table tes_p add constraint pk_t_2 primary key (it);
此時你的分割槽表已經轉換完成
檢視這樣操作的日誌量
 select c.sid,b.NAME,a.VALUE from v$sesstat a ,v$statname b ,(select SID from v$mystat where rownum<=1) c
     where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and   b.name like '%redo%' and a.VALUE<>0 ;
 
       SID NAME                                                                  VALUE
---------- ---------------------------------------------------------------- ----------
       139 redo synch writes                                                        10
       139 redo synch time                                                         147
       139 redo entries                                                           9189
       139 redo size                                                          48358956  ---這裡
       139 redo buffer allocation retries                                           30
       139 redo log space requests                                                   4
       139 redo log space wait time                                                 65
方法2
使用NOLOGGING方法
原表已經變化為scubagear，我們先建立分割槽表
 CREATE TABLE test_p2
     (it int ,
      ic int ,
      im varchar2(20))
 PARTITION BY RANGE (ic)
     ( PARTITION s_q1 VALUES LESS THAN (333333),
       PARTITION s_q2 VALUES LESS THAN (666666),
       PARTITION s_q3 VALUES LESS THAN (10000002))
讓其不處於NOLOGGING模式
alter table test_p2 modify partition s_q1 nologging;
alter table test_p2 modify partition s_q2 nologging;
alter table test_p2 modify partition s_q3 nologging;
然後開啟新會話進行INSERT APPEND
insert /* +append */ into test_p2 select * from scubagear; -9s
建立主鍵
alter table test_p2 add constraint pk_t_3 primary key (it);
這裡完成了
然後RENAME
SQL> alter table  scubagear rename to test_123;
 
Table altered
 
SQL> alter table test_p2 rename to scubagear;
 
Table altered
表SCUBAGEAR已經是分割槽表
SQL>  select c.sid,b.NAME,a.VALUE from v$sesstat a ,v$statname b ,(select SID from v$mystat where rownum<=1) c
  2       where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and   b.name like '%redo%' and a.VALUE<>0 ;
 
       SID NAME                                                                  VALUE
---------- ---------------------------------------------------------------- ----------
       135 redo synch writes                                                         2
       135 redo synch time                                                          30
       135 redo entries                                                          22530
       135 redo size                                                          48463484 --這裡
       135 redo buffer allocation retries                                            6
       135 redo log space requests                                                   4
       135 redo log space wait time                                                 17
 
7 rows selected

顯示證明 兩種方法REDO和時間差不多，時間也是相同的，不過為了可控儘量使用方法2.如果確實需要線上重定義使用方法1；