目的:测试表的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日志量。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-230927/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10972173/viewspace-230927/