不管表是否处于nologging下，direct insert 都不会对数据产生 UNDO

原文出处:  http://www.itpub.net/thread-217094-1-1.html

从理论上来说，对于 direct insert 的 undo （rowid）实在没有存在的必要
因为HWM 在移动的过程中，这些block是不能被其他process使用的，那么，意味着，只要记录下该次direct insert所涉及到的 空间的redo 和 undo  ，在失败回滚的时候，只需要把这些空间修改为原来的状态就可以而而不用逐个记录去delete。

为此我将在这里做几个组合实验

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for Linux: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL>
SQL> drop table t;

Table dropped.

SQL> create table t as select * from dba_objects ;

Table created.

SQL> select usn,RSSIZE ,WRITES from  v$rollstat;

       USN     RSSIZE     WRITES
---------- ---------- ----------
         0     385024       7620
         1     253952   11168864
         2    2220032   11335220
         3    2220032    9668346
         4    1171456    8887572
         5     122880      84154
         6     122880          0
         7    1171456        234
         8     122880          0
         9     122880          0
        10     122880          0

11 rows selected.

SQL> insert into  t select * from dba_objects;

13197 rows created.

SQL> commit;

Commit complete.

SQL> select usn,RSSIZE ,WRITES from  v$rollstat;

       USN     RSSIZE     WRITES
---------- ---------- ----------
         0     385024       7620
         1     253952   11168864
         2    2220032   11336024
         3    2220032    9668346
         4    2220032    9810066
         5     122880      85452
         6     122880          0
         7    1171456        234
         8     122880          0
         9     122880          0
        10     122880          0

11 rows selected.

SQL> select 9810066 - 8887572 from dual;

9810066-8887572
---------------
         922494       正常情况下产生的回滚段信息量
SQL> select 85452 - 84152 from dual;

85452-84152
-----------
       1300       undo中这个细微变化目前不详，估计可能是空间的变化导致的


SQL> alter table t nologging; 设置表为nologging

Table altered.

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

13197 rows created.

SQL> commit;

Commit complete.

SQL> select usn,RSSIZE ,WRITES from  v$rollstat;

       USN     RSSIZE     WRITES
---------- ---------- ----------
         0     385024       7620
         1     253952   11169498
         2    2220032   11339548
         3    2220032    9670376
         4    2220032    9813076
         5     122880      85452
         6     122880          0
         7    1171456        234
         8     122880          0
         9     122880          0
        10     122880          0

11 rows selected.

SQL> select 9670376 - 9668346 from dual;

9670376-9668346
---------------
           2030 发现回滚段信息极少

SQL> select 9813076-9810066 from dual;

9813076-9810066
---------------
           3010

SQL>






SQL> alter table t logging;    把表置回logging状态

Table altered.

SQL> select usn,RSSIZE ,WRITES from  v$rollstat;

       USN     RSSIZE     WRITES
---------- ---------- ----------
         0     385024       7620
         1     253952   11173844
         2    2220032   11343052
         3    2220032    9676700
         4    2220032    9816500
         5     122880      89668
         6     122880          0
         7    1171456        234
         8     122880          0
         9     122880          0
        10     122880          0

11 rows selected.

SQL> insert into t select * from dba_objects;   正常插入数据

13197 rows created.

SQL> commit;

Commit complete.

SQL> select usn,RSSIZE ,WRITES from  v$rollstat;

       USN     RSSIZE     WRITES
---------- ---------- ----------
         0     385024       7620
         1     253952   11173844
         2    2220032   12266342
         3    2220032    9676918
         4    2220032    9817356
         5     122880      90478
         6     122880          0
         7    1171456        234
         8     122880          0
         9     122880          0
        10     122880          0

11 rows selected.

SQL> select 12266342 - 11343052 from dual;

12266342-11343052
-----------------
           923290    产生的回滚段信息

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

13197 rows created.

SQL> commit;

Commit complete.

SQL> select usn,RSSIZE ,WRITES from  v$rollstat;

       USN     RSSIZE     WRITES
---------- ---------- ----------
         0     385024       7620
         1     253952   11174528
         2    2220032   12266768
         3    2220032    9678420
         4    2220032    9817356
         5     122880      92562
         6     122880          0
         7    1171456        234
         8     122880          0
         9     122880          0
        10     122880          0

11 rows selected.

SQL>

SQL> select  11174528 - 11173844 from dual;

11174528-11173844
-----------------
              684

SQL> select  92562 - 90478 from dual;

92562-90478
-----------
       2084

SQL>
在这里我们核对任意一个回滚段的生成量，发现几乎很少

由此我们几乎可以下个结论，不管表是否在nologging 下，只要是 direct  insert，就不会对数据内容生成undo，也就是不会为insert而记录 rowid






接下来我们进一步实验，在表同样处于 logging 状态下测试

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME                      CLASS      VALUE    STAT_ID
---------- -------------------- ---------- ---------- ----------
       133 redo size                     2  133173584 1236385760

SQL> insert into t select * from dba_objects;

13197 rows created.

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME                      CLASS      VALUE    STAT_ID
---------- -------------------- ---------- ---------- ----------
       133 redo size                     2  137974492 1236385760

SQL> select  137974492 - 133173584 from dual;

137974492-133173584
-------------------
            4800908   正常插入产生的日志

SQL> roll;
Rollback complete.
SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME                      CLASS      VALUE    STAT_ID
---------- -------------------- ---------- ---------- ----------
       133 redo size                     2  140087680 1236385760

SQL> select 140087680 - 137974492 from dual;

140087680-137974492
-------------------
            2113188   正常插入后回滚所产生的日志

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

13197 rows created.

SQL>  select * from v$sysstat where name = 'redo size';

STATISTIC# NAME                      CLASS      VALUE    STAT_ID
---------- -------------------- ---------- ---------- ----------
       133 redo size                     2  141531644 1236385760

SQL> select 141531644 - 140087680 from dual;

141531644-140087680
-------------------
            1443964    direct 插入产生的日志

SQL> roll;
Rollback complete.
SQL>  select * from v$sysstat where name = 'redo size';

STATISTIC# NAME                      CLASS      VALUE    STAT_ID
---------- -------------------- ---------- ---------- ----------
       133 redo size                     2  141534344 1236385760

SQL> select 141534344 - 141531644 from dual;

141534344-141531644
-------------------
               2700    direct插入后回滚产生的日志

SQL>

从这里的实验可以看出，在 direct insert 后回滚数据，实际上并没有进行数据的 删除操作
而是仅仅对空间进行了回收。若是删除，不可能只产生这么少的 redo，这里从另一个侧面证明
即使 logging 下的 direct insert 对于回滚信息，也是不会对数据产生 undo 而仅仅产生空间变化的 undo
带索引表，表和索引均是logging状态,测试结果及过程如下

----------------------------常规插入-------direct插入      
                  
插入日志生成量----------------8350864--------2364484

插入回滚段生成量--------------2343894--------426838

回滚日志生成量----------------4018204--------76032

回滚本身不存在产生回滚-------------------------------------


结论是很显然的，也许这里有人要问，既然direct有这么多好处，那为什么还用常规？
因为920前的版本sqlldr direct导致trigger无用、函数无用
direct直接在hwm上移动而不使用delete删除释放的空间可能导致空间浪费
direct的时候，据说同一个extent只能由一个进程使用（未测试，目前无LMT表空间环境）
direct insert 未提交会阻塞其他dml，也会阻止进程自己对该表的查询。

direct +  nologging 由于不产生数据日志导致恢复会出现问题

SQL> truncate table t;

Table truncated.

SQL> create index t_index on t(object_id);

Index created.

SQL> col name format a20
SQL> select usn,RSSIZE ,WRITES from v$rollstat;

       USN     RSSIZE     WRITES
---------- ---------- ----------
         0     385024       7620
         1     516096   14166140
         2    1171456   17003930
         3    2220032   13918700
         4    1171456   13550540
         5     122880     756246
         6     122880          0
         7    1171456        312
         8     122880          0
         9     122880          0
        10     122880          0

11 rows selected.

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME                      CLASS      VALUE    STAT_ID
---------- -------------------- ---------- ---------- ----------
       133 redo size                     2  181757168 1236385760

SQL> insert into t select * from dba_objects;

13198 rows created.

SQL> select usn,RSSIZE ,WRITES from v$rollstat;

       USN     RSSIZE     WRITES
---------- ---------- ----------
         0     385024       7620
         1     516096   14166140
         2    4317184   19347824
         3    2220032   13918700
         4    1171456   13551396
         5     122880     756246
         6     122880          0
         7    1171456        312
         8     122880          0
         9     122880          0
        10     122880          0

11 rows selected.

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME                      CLASS      VALUE    STAT_ID
---------- -------------------- ---------- ---------- ----------
       133 redo size                     2  190108032 1236385760

SQL> select 19347824 - 17003930 from dual;

19347824-17003930
-----------------
          2343894    存在索引，常规插入方式下产生的回滚量

SQL> select 190108032 - 181757168 from dual;

190108032-181757168
-------------------
            8350864 存在索引，常规插入方式下产生的日志量

SQL> roll;
Rollback complete.
SQL> select usn,RSSIZE ,WRITES from v$rollstat;

       USN     RSSIZE     WRITES
---------- ---------- ----------
         0     385024       7620
         1     516096   14166140
         2    4317184   19347824
         3    2220032   13918700
         4    1171456   13551396
         5     122880     757102
         6     122880          0
         7    1171456        312
         8     122880          0
         9     122880          0
        10     122880          0

11 rows selected.

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME                      CLASS      VALUE    STAT_ID
---------- -------------------- ---------- ---------- ----------
       133 redo size                     2  194126236 1236385760

SQL> select 194126236 - 190108032 from dual;

194126236-190108032
-------------------
            4018204    存在索引，常规插入方式下然后回滚所产生的日志量

SQL> truncate table t;

Table truncated.

SQL> select usn,RSSIZE ,WRITES from v$rollstat;

       USN     RSSIZE     WRITES
---------- ---------- ----------
         0     385024       7620
         1     581632   14185742
         2    4317184   19356862
         3    2220032   13936438
         4    1171456   13566936
         5     122880     757102
         6     122880          0
         7    1171456        312
         8     122880          0
         9     122880          0
        10     122880          0

11 rows selected.

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME                      CLASS      VALUE    STAT_ID
---------- -------------------- ---------- ---------- ----------
       133 redo size                     2  194287336 1236385760

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

13198 rows created.

SQL> select usn,RSSIZE ,WRITES from v$rollstat;

       USN     RSSIZE     WRITES
---------- ---------- ----------
         0     385024       7620
         1    2088960   14612580
         2    4317184   19356862
         3    2220032   13936438
         4    1171456   13569090
         5     122880     757102
         6     122880          0
         7    1171456        312
         8     122880          0
         9     122880          0
        10     122880          0

11 rows selected.

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME                      CLASS      VALUE    STAT_ID
---------- -------------------- ---------- ---------- ----------
       133 redo size                     2  196651820 1236385760

SQL> select 14612580 - 14185742 from dual;

14612580-14185742
-----------------
           426838    存在索引，direct插入方式下产生的回滚量

SQL> select 196651820 - 194287336 from dual;

196651820-194287336
-------------------
            2364484   存在索引，direct插入方式下产生的日志量

SQL> roll;
Rollback complete.
SQL> select usn,RSSIZE ,WRITES from v$rollstat;

       USN     RSSIZE     WRITES
---------- ---------- ----------
         0     385024       7620
         1    2088960   14612580
         2    4317184   19360722
         3    2220032   13936438
         4    1171456   13569946
         5     122880     757908
         6     122880          0
         7    1171456        312
         8     122880          0
         9     122880          0
        10     122880          0

11 rows selected.

SQL> select * from v$sysstat where name = 'redo size';

STATISTIC# NAME                      CLASS      VALUE    STAT_ID
---------- -------------------- ---------- ---------- ----------
       133 redo size                     2  196727852 1236385760

SQL> select  196727852 - 196651820 from dual;

196727852-196651820
-------------------
              76032   存在索引，direct插入方式下回滚产生的日志量

SQL>
我测试的数据

    
    
     
     PHP code:
    
    
    
    



SQL> show user


USER is "SCOTT"


SQL> create table t as select * from emp;       





Table created.





SQL> @roll.sql





NAME                                  USN     RSSIZE     WRITES


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


SYSTEM                                  0     385024       7020


_SYSSMU1$                               1    1171456     956160


_SYSSMU2$                               2     188416     943404


_SYSSMU3$                               3     319488    1171200


_SYSSMU4$                               4    1171456     998598


_SYSSMU5$                               5    1171456     970452


_SYSSMU6$                               6    1171456     967354


_SYSSMU7$                               7    1171456     948962


_SYSSMU8$                               8    2220032     968382


_SYSSMU9$                               9    1171456    1101832


_SYSSMU10$                             10    3268608    1162138





11 rows selected.





SQL> insert into t select * from emp;





14 rows created.





SQL> commit;





Commit complete.





SQL> @roll.sql





NAME                                  USN     RSSIZE     WRITES


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


SYSTEM                                  0     385024       7020


_SYSSMU1$                               1    1171456     956386


_SYSSMU2$                               2     188416     943458


_SYSSMU3$                               3     319488    1171318


_SYSSMU4$                               4    1171456     998794


_SYSSMU5$                               5    1171456     970562


_SYSSMU6$                               6    1171456     967580


_SYSSMU7$                               7    1171456     949072


_SYSSMU8$                               8    2220032     968608


_SYSSMU9$                               9    1171456    1101942


_SYSSMU10$                             10    3268608    1162248





11 rows selected.





SQL> alter table t nologging;





Table altered.





SQL> insert /*+ append */ into t select * from emp;





14 rows created.





SQL> commit;





Commit complete.





SQL> @roll.sql





NAME                                  USN     RSSIZE     WRITES


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


SYSTEM                                  0     385024       7020


_SYSSMU1$                               1    1171456     957058


_SYSSMU2$                               2     188416     944334


_SYSSMU3$                               3     319488    1172750


_SYSSMU4$                               4    1171456     999406


_SYSSMU5$                               5    1171456     971234


_SYSSMU6$                               6    1171456     968252


_SYSSMU7$                               7    1171456     949744


_SYSSMU8$                               8    2220032     969280


_SYSSMU9$                               9    1171456    1102270


_SYSSMU10$                             10    3268608    1162920





11 rows selected.





SQL> 








SQL> alter table t logging;





Table altered.





SQL> @roll.sql





NAME                                  USN     RSSIZE     WRITES


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


SYSTEM                                  0     385024       7020


_SYSSMU1$                               1    1171456     957644


_SYSSMU2$                               2     188416     944920


_SYSSMU3$                               3     319488    1173392


_SYSSMU4$                               4    1171456    1000274


_SYSSMU5$                               5    1171456     972016


_SYSSMU6$                               6    1171456     969150


_SYSSMU7$                               7    1171456     950526


_SYSSMU8$                               8    2220032     970006


_SYSSMU9$                               9    1171456    1102740


_SYSSMU10$                             10    3268608    1164018





11 rows selected.





SQL> insert into t select * from emp;





14 rows created.





SQL> commit;





Commit complete.





SQL> @roll.sql





NAME                                  USN     RSSIZE     WRITES


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


SYSTEM                                  0     385024       7020


_SYSSMU1$                               1    1171456     957840


_SYSSMU2$                               2     188416     945146


_SYSSMU3$                               3     319488    1173502


_SYSSMU4$                               4    1171456    1000500


_SYSSMU5$                               5    1171456     972126


_SYSSMU6$                               6    1171456     969376


_SYSSMU7$                               7    1171456     950636


_SYSSMU8$                               8    2220032     970060


_SYSSMU9$                               9    1171456    1102850


_SYSSMU10$                             10    3268608    1164136





11 rows selected.





SQL>  insert /*+ append */ into t select * from 


  2  emp;





14 rows created.





SQL> commit;




Commit complete.




SQL> @roll




NAME                                  USN     RSSIZE     WRITES


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


SYSTEM                                  0     385024       7020


_SYSSMU1$                               1    1171456     958036


_SYSSMU2$                               2     188416     945372


_SYSSMU3$                               3     319488    1173612


_SYSSMU4$                               4    1171456    1000726


_SYSSMU5$                               5    1171456     972236


_SYSSMU6$                               6    1171456     969602


_SYSSMU7$                               7    1171456     950746


_SYSSMU8$                               8    2220032     970114


_SYSSMU9$                               9    1171456    1102960


_SYSSMU10$                             10    3268608    1164528



11 rows selected.




SQL>