alter table nologging /*+APPEND PARALLEL(n)*/

                   最近系統中關鍵任務做了如下操作，
alter table table_name nologging;
alter session enable parallel dml;
insert into /*+append parallel(n)*/ table_name select xx left join xxx on xx.id = xxx.id;
commit;
alter table table_name logging;

注意：系統中允許table nologging，注意任務中表鎖的情況，需要及時提交。

下面是測試結果：
--------------------------------------------------------------------------------
create table test1 as select * from test where 1=2;
[1]
insert into test1 
select a.* from (select * from test order by owner desc,object_id,status desc,sharing ,created ) 
a left join (select object_id ,count(owner) from test group by object_id ) b on a.object_id = 
b.object_id order by a.object_id desc;
Elapsed: 00:01:42.44
       3355  recursive calls
    2009619  db block gets
    1455125  consistent gets
     393476  physical reads
  2971133724  redo size
   18754752  rows processed

[2]
alter table test1 nologging;
insert into test1 
select a.* from (select * from test order by owner desc,object_id,status desc,sharing ,created ) 
a left join (select object_id ,count(owner) from test group by object_id ) b on a.object_id = 
b.object_id order by a.object_id desc;
Elapsed: 00:01:52.06
       3426  recursive calls
    2010276  db block gets
    1455523  consistent gets
     393476  physical reads
  2971157132  redo size
875  bytes sent via SQL*Net to client
       1167  bytes received via SQL*Net from client
  3  SQL*Net roundtrips to/from client
  4  sorts (memory)
  1  sorts (disk)
   18754752  rows processed
   
[3]
drop table test1 purge;
create table test1 as select * from test where 1=2;
alter table test1 nologging;
insert /*+append*/into test1 
select a.* from (select * from test order by owner desc,object_id,status desc,sharing ,created ) 
a left join (select object_id ,count(owner) from test group by object_id ) b on a.object_id = 
b.object_id order by a.object_id desc;
Elapsed: 00:00:53.17

       3588  recursive calls
     368898  db block gets
     727929  consistent gets
     393476  physical reads
      460940   redo size
    861  bytes sent via SQL*Net to client
       1186  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      3  sorts (memory)
      1  sorts (disk)
   18754752  rows processed
[4]
drop table test1 purge;
create table test1 as select * from test where 1=2;
alter table test1 nologging;
insert /*+append parallel(12)*/into test1 
select a.* from (select * from test order by owner desc,object_id,status desc,sharing ,created ) 
a left join (select object_id ,count(owner) from test group by object_id ) b on a.object_id = 
b.object_id order by a.object_id desc;
Elapsed: 00:00:23.98
       3476  recursive calls
     368906  db block gets
     728849  consistent gets
    1101962  physical reads
      460896   redo size
      3  SQL*Net roundtrips to/from client
      6  sorts (memory)
      4  sorts (disk)
   18754752  rows processed
[5]
drop table test1 purge;
create table test1 as select * from test where 1=2;
insert /*+ parallel(12)*/into test1 
select a.* from (select * from test order by owner desc,object_id,status desc,sharing ,created ) 
a left join (select object_id ,count(owner) from test group by object_id ) b on a.object_id = 
b.object_id order by a.object_id desc;
   
【結論】並行開的多避免磁碟排序，加相同的並行不加append時間是1分08秒，都加了是23秒，只加append時間介於兩者之間。
所以先設定alter table test nologging; 然後sql寫hint /*+append parallel(n)*/

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