nologging对delete以及update效果不大

SQL> create table a (id int);

Table created.

SQL> begin for i in 1..10000 loop
  2  insert into a select i from dual;
  3  end loop;
  4  commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> insert into a select * from a;

10000 rows created.

SQL> desc a;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                        NOT NULL NUMBER(38)

SQL> begin for i in 1..10000 loop
  2  insert into a select i from dual;
  3  end loop;
  4  commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select count(*) from a;

  COUNT(*)
----------
     10000

SQL>
SQL> set autotrace traceonly;
SQL>

----------
--insert

SQL>insert into a select * from a;

10000 rows created.

Execution Plan
----------------------------------------------------------
   0      INSERT STATEMENT ptimizer=ALL_ROWS (Cost=8 Card=20000 Bytes
          =260000)

   1    0   TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=8 Card=20000 Byte
          s=260000)

Statistics
----------------------------------------------------------
         58  recursive calls
      10238  db block gets
       3284  consistent gets
          0  physical reads
    2373796  redo size
        643  bytes sent via SQL*Net to client
        538  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL>insert into a nologging select * from a;

10000 rows created.

Execution Plan
----------------------------------------------------------
   0      INSERT STATEMENT ptimizer=ALL_ROWS (Cost=8 Card=20000 Bytes
          =260000)

   1    0   TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=8 Card=20000 Byte
          s=260000)

Statistics
----------------------------------------------------------
          5  recursive calls
      10193  db block gets
      10101  consistent gets
          0  physical reads
   2370584  redo size
        643  bytes sent via SQL*Net to client
        548  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> rollback;

Rollback complete.

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

10000 rows created.

Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
         93  recursive calls
         45  db block gets
         94  consistent gets
          0  physical reads
     136144  redo size
        627  bytes sent via SQL*Net to client
        562  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL>
SQL>
SQL> rollback;

---------------
--delete

SQL>delete from a;

10000 rows deleted.

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT ptimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)
   1    0   DELETE OF 'A'
   2    1     INDEX (FULL SCAN) OF 'SYS_C005064' (INDEX (UNIQUE)) (Cos
          t=1 Card=1 Bytes=3)

Statistics
----------------------------------------------------------
          9  recursive calls
      10450  db block gets
         24  consistent gets
          0  physical reads
   2540712  redo size
        642  bytes sent via SQL*Net to client
        522  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> rollback;

Rollback complete.

SQL>delete from a nologging;

10000 rows deleted.

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT ptimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)
   1    0   DELETE OF 'A'
   2    1     INDEX (FULL SCAN) OF 'SYS_C005064' (INDEX (UNIQUE)) (Cos
          t=1 Card=1 Bytes=3)

Statistics
----------------------------------------------------------
          1  recursive calls
      10437  db block gets
         19  consistent gets
          0  physical reads
   2534564  redo size
        642  bytes sent via SQL*Net to client
        532  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10000  rows processed

结论：在非force logging情况下，delete、update的命令使用nologging效果不大，insert的时候配合append效果明显