Oracle中关于insert中使用/*+append*/和nologging的总结

关于Nologging和append,一直存在很多误解，经过一系列研究,终于发现了Nologging的真相，

我们来看一下：

1.对于logging 的table处理

a.非归档模式下:

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Enabled

Archive destination            /opt/oracle/oradata/hsjf/archive

Oldest online log sequence     155

Current log sequence           157

SQL> @redo

SQL> create table test as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

     VALUE

----------

     63392

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------

   1150988

SQL> insert /*+ append */ into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE

----------

   1152368

SQL> select (1152368 -1150988) redo_append,(1150988 -63392) redo from dual;

REDO_APPEND       REDO

----------- ----------

       1380    1087596

SQL> drop table test;

Table dropped.  

我们看到在Noarchivelog模式下,对于常规表的insert 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