SQL> create table test nologging as select * from dba_objects where 1=0;
Table created.
SQL> select * from redo_size;
VALUE
----------
2270284
SQL>
SQL> insert into test select * from dba_objects;
10470 rows created.
SQL> select * from redo_size;
VALUE
----------
3357644
SQL>
SQL> insert /*+ append */ into test select * from dba_objects;
10470 rows created.
SQL> select * from redo_size;
VALUE
----------
3359024
SQL> select (3359024 -3357644) redo_append,(3357644 - 2270284) redo from dual;
REDO_APPEND REDO
----------- ----------
1380 1087360
SQL> drop table test;
Table dropped.
我们注意到,只有append才能减少redo
b.在非归档模式下:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 235999908 bytes
Fixed Size 451236 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> @redo
SQL> create table test nologging as select * from dba_objects where 1=0;
Table created.
SQL> select * from redo_size;
VALUE
----------
56580
SQL>
SQL> insert into test select * from dba_objects;
10470 rows created.
SQL> select * from redo_size;
VALUE
----------
1144148
SQL>
SQL> insert /*+ append */ into test select * from dba_objects;
10470 rows created.
SQL> select * from redo_size;
VALUE
----------
1145528
SQL> select (1145528 -1144148) redo_append,(1144148 -56580) redo from dual;
REDO_APPEND REDO
----------- ----------
1380 1087568
SQL>