NOLOGGING的正确用法

今天在看以前一些工作文档，发现有些DML脚本，用到了nologging的hint，即/*+NOLOGGING*/，这样写到底能不能减少日志量了？？带着疑问，我做了以下测试：

1. 直接update，看redo量

SQL> create table a as select * from dba_objects;

Table created.

SQL> set autotrace trace stat;
SQL> update a set object_id=2;

49758 rows updated.

Statistics
----------------------------------------------------------
         50  recursive calls
      50719  db block gets
        806  consistent gets
        684  physical reads
   12760212  redo size
        676  bytes sent via SQL*Net to client
        560  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      49758  rows processed

2. 加nologging hint，再比较redo量，可以看到redo量几乎没变化！！！

SQL> drop table a;

Table dropped.

SQL> create table a as select * from dba_objects;

Table created.

SQL> update/*+nologging*/ a set object_id=2;

49761 rows updated.

Statistics
----------------------------------------------------------
         50  recursive calls
      50722  db block gets
        799  consistent gets
        684  physical reads
   12761040  redo size
        679  bytes sent via SQL*Net to client
        574  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      49761  rows processed

通过上面的测试，我们可以看到nologging作为hint是起不了减少日志的作用滴，其实nologging并不是oracle有效的hint，而是oracle一个关键字，那应该怎么正确使用nologging了？？？

1. create table a nologging as select * from b;

SELECT a.VALUE
    FROM v$mystat a,v$statname b
WHERE a.STATISTIC#=b.STATISTIC#
      AND b.NAME='redo size';

     VALUE
----------
       624

SQL> create table t1 as select object_id from dba_objects;

Table created.

SELECT a.VALUE
FROM v$mystat a,v$statname b
WHERE a.STATISTIC#=b.STATISTIC#
      AND b.NAME='redo size';

     VALUE
----------
    701536

可见产生的redo量为：701536-624=700912，再用nologging同样创建一个表t2

SQL> create table t2 nologging as select object_id from dba_objects;

Table created.

SELECT a.VALUE
    FROM v$mystat a,v$statname b
WHERE a.STATISTIC#=b.STATISTIC#
         AND b.NAME='redo size';


     VALUE
----------
    738232

这样用nologging产生的redo量为：738232-701536=36696，redo量明显减少

2. CREATE INDEX T1_IDX ON T1(A) NOLOGGING；

3. ALTER INDEX T1_IDX REDUILD ONLINE NOLOGGING;

4. ALTER TABLE T1 NOLOGGING

以上才是nologging的正确用法，就不一一举例子了。强调一点，存在的不一定是合理的，大家一定要带着怀疑的眼光去看待存在的东西，它们真的不一定是合理的。。。。。。。