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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29990276/viewspace-2155434/