学习笔记－nologging与logging对DML操作的影响

   目的：测试表的nologging与logging属性的区别。

试验1：logging模式下，插入数据时产生的redo日志量。

SQL> create table t as select * from sys.dba_objects;
 
Table created.
 
SQL> @mystat "redo size"
 
NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                            36399652
 
SQL> insert into t select * from t;
 
5922 rows created.
 
SQL> @mystat2
 
NAME                                                        V DIFF
-------------------------------------------------- ---------- ----------------
redo size                                            37000040          600,388

可见，在logging模式下，插入5992条记录所产生的日志量为600k。

试验2：nologging模式下，插入同等数据产生的redo日志量。

SQL> drop table t;
 
Table dropped.
 
SQL> create table t nologging as select * from sys.dba_objects;
 
Table created.
 
SQL> @mystat "redo size"
 
NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                            37051240
 
SQL> insert into t select * from t;
 
5922 rows created.
 
SQL> @mystat2
 
NAME                                                        V DIFF
-------------------------------------------------- ---------- ----------------
redo size                                            37651440          600,200

发现产生的日志与logging模式下相当，继续进行如下试验

SQL> @mystat "redo size"
 
NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                            37651440
 
SQL> insert into t select * from t;
 
11844 rows created.
 
SQL> /
 
23688 rows created.
 
SQL> /
 
47376 rows created.
 
SQL> /
 
94752 rows created.
 
SQL> @mystat2
 
NAME                                                        V DIFF
-------------------------------------------------- ---------- ----------------
redo size                                            55469200       17,817,760

连续插入了4次，产生18MB的日志。

测试3：继续用logging模式测试

SQL> create table t as select * from sys.dba_objects;
 
Table created.
 
SQL> @mystat "redo size"
 
NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                            56127616
 
SQL> insert into t select * from t;
 
5922 rows created.
 
SQL> @mystat "redo size"
 
NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                            56727816
 
SQL>  insert into t select * from t;
 
11844 rows created.
 
SQL> /
 
23688 rows created.
 
SQL> /
 
47376 rows created.
 
SQL> /
 
94752 rows created.
 
SQL> @mystat2
 
NAME                                                        V DIFF
-------------------------------------------------- ---------- ----------------
redo size                                            74549380       17,821,564

同样连续插入相同数据后，产生的日志与nologging模式相同。

      可见，无论表的logging属性为yes或no，都不会影响到对该表进行dml操作时生成的redo日志量。