Nologging append 对 产生redo log 的影响

表空间： nologging

基本上，insert append 产生少量redo，insert into nologging 和insert 效果一样。

[@more@]--------------------------------------------------------------------------------------------------------------------------

SQL> SQL> insert into mytest select * from user_tables;


3988 rows created.

SQL> SQL> select value from v$mystat where statistic# in (select statistic# from v$sysstat where name = 'redo size');




VALUE
----------
2590940

SQL> SQL> SQL> SQL> select (2590940 - 1735620 ) from dual;


(2590940-1735620)
-----------------
855320

SQL> SQL>
---------------------------------------------------------------------------------------------------------------------------
SQL> SQL> insert into mytest nologging select * from user_tables;


3988 rows created.

SQL> SQL> select value from v$mystat where statistic# in (select statistic# from v$sysstat where name = 'redo size');




VALUE
----------
3445824

SQL> SQL> SQL> SQL> select (3445824 - 2609640 ) from dual;


(3445824-2609640)
-----------------
836184
--------------------------------------------------------------------------------------------------------------
SQL> SQL> insert /*+append */ into mytest select * from user_tables;


3988 rows created.

SQL> SQL> select value from v$mystat where statistic# in (select statistic# from v$sysstat where name = 'redo size');


VALUE
----------


3490752

SQL> SQL> SQL> SQL> select (3490752 - 3465328) from dual;


(3490752-3465328)
-----------------
25424

SQL> SQL>
-----------------------------------------------------------------------------------------------------------------
SQL> insert /*+ append */ into mytest nologging select * from user_tables;


3988 rows created.

SQL> SQL> select value from v$mystat where statistic# in (select statistic# from v$sysstat where name = 'redo size');




VALUE
----------
3535296

SQL> SQL> SQL> SQL> select (3535296 - 3509872) from dual;


(3535296-3509872)
-----------------
25424
------------------------------------------------------------------------------------------------------------------------------------
Nologging::::::::::::



SQL> SQL> select value from v$mystat where statistic# in (select statistic# from v$sysstat where name = 'redo size');


VALUE
----------


3645876

SQL> SQL> SQL> SQL>

SQL> SQL> select


SQL> SQL> insert into mytest select * from user_tables;


3988 rows created.

SQL> SQL> select value from v$mystat where statistic# in (select statistic# from v$sysstat where name = 'redo size');


VALUE
----------
4482064

SQL> SQL> select (4482064 - 3645876 ) from dual;


(4482064-3645876)
-----------------
836188

SQL> SQL>
----------------------------------------------------------------------------------------------------------------------------

SQL> SQL> select value from v$mystat where statistic# in (select statistic# from v$sysstat where name = 'redo size');




VALUE
----------
4482064

SQL> SQL> SQL> SQL>

SQL> SQL>

SQL> SQL> truncate table mytest;


Table truncated.

SQL> SQL> insert into mytest nologging select * from user_tables;


3988 rows created.

SQL> SQL> select value from v$mystat where statistic# in (select statistic# from v$sysstat where name = 'redo size');




VALUE
----------
5336988

SQL> SQL> SQL> SQL> select (5336988 - 4482064 ) from dual;


(5336988-4482064)
-----------------
854924

SQL> SQL>
---------------------------------------------------------------------------------------------------------------------------
SQL> SQL> truncate table mytest;


Table truncated.

SQL> SQL> select value from v$mystat where statistic# in (select statistic# from v$sysstat where name = 'redo size');


VALUE
----------
5355704

SQL> SQL> insert /*+ append */ into mytest select * from user_tables;


3988 rows created.

SQL> SQL> select value from v$mystat where statistic# in (select statistic# from v$sysstat where name = 'redo size');


VALUE
----------
5381128

SQL> SQL> select (5381128 - 5355704) from dual;


(5381128-5355704)
-----------------
25424

SQL> SQL>
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL> SQL> truncate table mytest;


Table truncated.

SQL> SQL> select value from v$mystat where statistic# in (select statistic# from v$sysstat where name = 'redo size');


VALUE
----------
5400248

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


3988 rows created.

SQL> SQL> select value from v$mystat where statistic# in (select statistic# from v$sysstat where name = 'redo size');


VALUE
----------
5425672

SQL> SQL> select (5425672 - 5400248) from dual;


(5425672-5400248)
-----------------
25424

SQL> SQL>
--------------------------------------------------------------------------------------------------------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21342557/viewspace-1032349/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21342557/viewspace-1032349/