大家一定遇到过不想让oracle产生日志的情况。那么什么时候oracle能够不产生日志呢？
有吗？这里我总结一下oracle产生少量日志的情况。

在scott下，构造了一个9万多的表
create table t1 as select * from emp;

SQL> insert into t1 select * from t1;

14 rows created.

SQL> /

28 rows created.

SQL> /

56 rows created.

重复的不断插入，直到

917504 rows created.

SQL> commit;

-----------------------------------------------------------------------
下面开始我们的实验

SQL> conn /as sysdba
Connected.
SQL> desc v$sysstat
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 STATISTIC#                                                     NUMBER
 NAME                                                           VARCHAR2(64)
 CLASS                                                          NUMBER
 VALUE                                                          NUMBER
 STAT_ID                                                        NUMBER

SQL> select * from v$sysstat where class=2;

STATISTIC# NAME                                                    CLASS      VALUE    STAT_ID
---------- -------------------------------------------------- ---------- ---------- ----------
       132 redo blocks read for recovery                               2          0 2679943069
       133 redo entries                                                2     104183 3488821837
       134 redo size                                                   2  107480316 1236385760
       135 redo buffer allocation retries                              2          4 1446958922
       136 redo wastage                                                2     556420 3462806146
       137 redo writer latching time                                   2          2 2166056472
       138 redo writes                                                 2       2149 1948353376
       139 redo blocks written                                         2     217925 2391431605
       140 redo write time                                             2       1307 3094453259
       141 redo log space requests                                     2          5 1985754937
       142 redo log space wait time                                    2         27  252430928
       143 redo log switch interrupts                                  2          0  674283274
       144 redo ordering marks                                         2       2294 2104561012
       145 redo subscn max counts                                      2          0  449106517
       168 flashback log writes                                        2          0 3123176560

15 rows selected.

查看当前的redo size

SQL> select * from v$sysstat where STATISTIC#=134;

STATISTIC# NAME                                                    CLASS      VALUE    STAT_ID
---------- -------------------------------------------------- ---------- ---------- ----------
       134 redo size                                                   2  107576456 1236385760

SQL> show user
USER is "SYS"
SQL> conn scott/tigger
Connected.
SQL> create table test as select * from t1;

Table created.

SQL> conn /as sysdba
Connected.
SQL> select * from v$sysstat where STATISTIC#=134;

STATISTIC# NAME                                                    CLASS      VALUE    STAT_ID
---------- -------------------------------------------------- ---------- ---------- ----------
       134 redo size                                                   2  108493988 1236385760

SQL> select ( 108493988-107576456)/1024/1024 from dual;

(108493988-107576456)/1024/1024
-------------------------------
                     .875026703
                    
我们日常中的备份表产生了.875026703M大小的日志               

SQL> conn scott/tigger
Connected.
SQL> drop table test purge;

Table dropped.

SQL> create table test as select * from t1 nologging;

Table created.

SQL> conn /as sysdba
Connected.
SQL> select * from v$sysstat where STATISTIC#=134;

STATISTIC# NAME                                                    CLASS      VALUE    STAT_ID
---------- -------------------------------------------------- ---------- ---------- ----------
       134 redo size                                                   2  108905676 1236385760

SQL> select (108905676- 108493988)/1024/1024 from dual;

(108905676-108493988)/1024/1024
-------------------------------
                     .392616272

加上nologging后，产生的日志比原来的1/2还要小。

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

SQL> conn scott/tigger
Connected.
SQL> select count(*) from test;

  COUNT(*)
----------
         0

SQL> insert into test select * from t1;

1835008 rows created.

SQL> commit;

Commit complete.

SQL> conn /as sysdba
Connected.
SQL> select * from v$sysstat where STATISTIC#=134;

STATISTIC# NAME                                                    CLASS      VALUE    STAT_ID
---------- -------------------------------------------------- ---------- ---------- ----------
       134 redo size                                                   2  202231620 1236385760

SQL> select (202231620- 109424508)/1024/1024 from dual;

(202231620-109424508)/1024/1024
-------------------------------
                     88.5077591

产生88.5077591M的日志

SQL> truncate table test;

Table truncated.

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

1835008 rows created.

SQL> commit;

Commit complete.

SQL> select * from v$sysstat where STATISTIC#=134;

STATISTIC# NAME                                                    CLASS      VALUE    STAT_ID
---------- -------------------------------------------------- ---------- ---------- ----------
       134 redo size                                                   2  489164360 1236385760

SQL> select * from v$sysstat where STATISTIC#=134;

STATISTIC# NAME                                                    CLASS      VALUE    STAT_ID
---------- -------------------------------------------------- ---------- ---------- ----------
       134 redo size                                                   2  489208144 1236385760

SQL> select (489208144-488407836)/1024/1024 from dual;

(489208144-488407836)/1024/1024
-------------------------------
                     .763233185
                    
  产生日志大小为.763233185M
 
----------------------------------------------------------------------------
三、创建索引
为9十多万数据的test创建索引
SQL> create index idx_1 on test(sal);

SQL> select (520157608-489472044)/1024/1024 from dual;

(520157608-489472044)/1024/1024
-------------------------------
                     29.2640343
                    
产生日志29.2640343M

SQL> drop index idx_1;

Index dropped.

SQL> create index idx_1 on test(sal) nologging;

Index created.

SQL> select (520661968-520507264)/1024/1024 from dual;

(520661968-520507264)/1024/1024
-------------------------------
                     .147537231
                    
产生日志.147537231M

到此我们使用了三种方法，来降低日志的产生