Oracle NoLogging Append 方式减少批量insert的redo_size
业务处理中，很多时候使用实表临时表处理中间结果，而实表的Insert操作缺省会记录redo log，针对此问题收集相关测试总结信息如下：
【转】 常见dml、ddl语句使用nologging选项所生成的redo和undo大小比较
|
DDL/DML Operations |
Direct-path |
nologging |
parallel |
Noarchivelog Redo |
Noarchivelog Undo |
Archivelog Redo |
Archivelog Undo |
|
|
Insert into XXX select * from YYY |
N |
N |
N |
19076324 |
627240 |
18938620 |
612980 |
|
|
Alter table XXX nologging; Insert into XXX select * from YYY; |
N |
Y |
N |
19085860 |
631584 |
18935548 |
612912 |
|
|
Insert /*+ append */ into XXX select * from YYY |
Y |
N |
N |
26628 |
4048(only metadata) |
19145656 |
4048 |
|
|
Alter table XXX nologging; Insert /*+ append */ into XXX select * from YYY |
Y |
Y |
N |
26868 |
4048(only metadata) |
26836 |
4048 |
|
|
Create table XXX as select * from YYY |
Y |
N |
N |
77624 |
18500 |
19162220 |
15468 |
|
|
Create table XXX nologging as select * from YYY |
Y |
Y |
N |
52160 |
11176 |
52408 |
11248 |
|
|
Alter table XXX move nologging; |
N |
Y |
N |
36288 |
6208 |
36576 |
6208 |
|
|
Alter table XXX move; |
N |
N |
N |
39788 |
7288 |
19154024 |
5972 |
|
|
Create index XXX |
N |
N |
N |
21280 |
1864 |
3093616 |
1888 |
|
|
Create index XXX nologging |
N |
Y |
N |
22264 |
2208 |
22620 |
2232 |
|
|
update XXX set |
N |
N |
N |
122903212 |
47987880 |
122403692 |
47786680 |
|
|
Update /*+ parallel(5) */ XXX set |
N |
N |
Y(Queries) |
121629928 |
46706296 |
120818336 |
46466056 |
|
|
Alter table XXX nologging; update XXX set |
N |
Y |
N |
123155288 |
48006404 |
110396512 |
41617700 |
|
|
Alter table XXX nologging; update /*+ parallel(5) */ XXX set |
N |
Y |
Y(Queries) |
119748064 |
46152324 |
120205036 |
46261536 |
|
|
Alter session enable parallel dml Update /*+ parallel(5) */ XXX set |
N |
N |
Y(DML) |
59846488 |
24680220 |
59740516 |
24611672 |
|
|
Alter table XXX nologging; Alter session enable parallel dml Update /*+ parallel(5) */ XXX set |
N |
Y |
Y(DML) |
59831756 |
24673396 |
59717116 |
24602628 |
|
|
delete XXX; |
N |
N |
N |
60684720 |
37650208 |
61770472 |
38254648 |
|
|
delete /*+parallel(5) */ XXX; |
N |
N |
Y(Queries) |
60685416 |
37650436 |
61988568 |
38461832 |
|
|
Alter table XXX nologging; delete XXX; |
N |
Y |
N |
60684956 |
37650216 |
61989984 |
38460424 |
|
|
Alter table XXX nologging; delete /*+parallel(5) */ XXX; |
N |
Y |
Y(Queries) |
60685092 |
37650224 |
61986156 |
38459172 |
|
|
Alter session enable parallel dml delete /*+parallel(5) */ aabbn; |
N |
N |
Y(DML) |
65072896 |
40990412 |
61979216 |
38457372 |
|
|
Alter table XXX nologging; Alter session enable parallel dml delete /*+parallel(5) */ aabbn; |
N |
Y |
Y(DML) |
65073828 |
40998048 |
62022668 |
38500548 |
|
结论：
1、insert into：非direct方式下noarchivelog和archivelog两种模式均产生大量undo、redo；direct方式下noarchivelog模式均产生少量undo、redo；direct+logging方式下archivelog模式产生少量undo、大量redo；direct+nologging方式下archivelog模式产生少量undo、少量redo
2、create table as：CTAS本身就是一种direct的操作，CTAS+logging方式下noarchivelog模式产生少量redo、少量undo；CTAS+nologging方式下noarchivelog模式产生更少量redo、更少量undo；CTAS+logging方式下archivelog模式产生少量undo、大量redo；CTAS+nologging方式下archivelog模式产生少量undo、少量redo；
3、alter table ... move：nologging方式下noarchivelog模式产生少量undo、少量redo；nologging方式下archivelog模式产生少量undo、少量redo；logging方式下archivelog模式产生少量undo、大量redo
4、create index：logging+nologging方式下noarchivelog模式产生少量undo、少量redo；logging方式下archivelog模式产生少量undo、大量redo；nologging方式下archivelog模式产生少量undo、少量redo
5、update：enable parallel方式下，noarchivelog+ archivelog模式生成少量undo、少量redo；非enable parallel方式下，noarchivelog+ archivelog模式生成大量undo、大量redo
6、delete：任何组合都会生成大量undo、大量redo
其他参考列表：
1) Nologging到底何时才能生效? http://www.eygle.com/faq/Nologging&append.htm
非归档模式下：append能大量减少redo量。
归档模式下：在表空间和数据库级非force logging模式下，表如果是nologging，则append能大量减少redo量。
