--1.实验环境
SQL> conn scott/tiger
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as scott@howe
SQL> drop table record purge;
Table dropped
SQL> create table record(name varchar2(20),value number);
Table created
SQL> drop table t1;
Table dropped
SQL> create table t1 as select * from emp;
Table created
SQL> insert into t1 select * from t1;
14 rows inserted
SQL> insert into t1 select * from t1;
28 rows inserted
SQL> insert into t1 select * from t1;
56 rows inserted
SQL> insert into t1 select * from t1;
112 rows inserted
SQL> insert into t1 select * from t1;
224 rows inserted
SQL> insert into t1 select * from t1;
448 rows inserted
SQL> insert into t1 select * from t1;
896 rows inserted
SQL> insert into t1 select * from t1;
1792 rows inserted
SQL> insert into t1 select * from t1;
3584 rows inserted
SQL> insert into t1 select * from t1;
7168 rows inserted
SQL> select count(*) from t1;
COUNT(*)
----------
14336
SQL> commit;
Commit complete
SQL> --2.测试语句
SQL> --2.1nologging状态table
SQL> alter table t1 nologging;
Table altered
SQL> truncate table record;
Table truncated
SQL> insert into record(name,value) select 'no1',value from v$sysstat where STATISTIC# =194;
1 row inserted
SQL> commit;
Commit complete
SQL> insert /*+ append */ into t1 select * from t1;
14336 rows inserted
SQL> rollback;
Rollback complete
SQL> insert into record(name,value) select 'no1',value from v$sysstat where STATISTIC# =194;
1 row inserted
SQL> commit;
Commit complete
SQL> insert into record(name,value) select 'no2',value from v$sysstat where STATISTIC# =194;
1 row inserted
SQL> commit;
Commit complete
SQL> insert /*+ append */ into t1 select * from t1 nologging;
14336 rows inserted
SQL> rollback;
Rollback complete
SQL> insert into record(name,value) select 'no2',value from v$sysstat where STATISTIC# =194;
1 row inserted
SQL> commit;
Commit complete
SQL> insert into record(name,value) select 'no3',value from v$sysstat where STATISTIC# =194;
1 row inserted
SQL> commit;
Commit complete
SQL> insert into t1 select * from t1;
14336 rows inserted
SQL> rollback;
Rollback complete
SQL> insert into record(name,value) select 'no3',value from v$sysstat where STATISTIC# =194;
1 row inserted
SQL> commit;
Commit complete
SQL> insert into record(name,value) select 'no4',value from v$sysstat where STATISTIC# =194;
1 row inserted
SQL> commit;
Commit complete
SQL> insert into t1 select * from t1 nologging;
14336 rows inserted
SQL> rollback;
Rollback complete
SQL> insert into record(name,value) select 'no4',value from v$sysstat where STATISTIC# =194;
1 row inserted
SQL> commit;
Commit complete
SQL> --2.2logging状态table
SQL> alter table t1 logging;
Table altered
SQL> insert into record(name,value) select 'log1',value from v$sysstat where STATISTIC# =194;
1 row inserted
SQL> commit;
Commit complete
SQL> insert /*+ append */ into t1 select * from t1;
14336 rows inserted
SQL> rollback;
Rollback complete
SQL> insert into record(name,value) select 'log1',value from v$sysstat where STATISTIC# =194;
1 row inserted
SQL> commit;
Commit complete
SQL> insert into record(name,value) select 'log2',value from v$sysstat where STATISTIC# =194;
1 row inserted
SQL> commit;
Commit complete
SQL> insert /*+ append */ into t1 select * from t1 nologging;
14336 rows inserted
SQL> rollback;
Rollback complete
SQL> insert into record(name,value) select 'log2',value from v$sysstat where STATISTIC# =194;
1 row inserted
SQL> commit;
Commit complete
SQL> insert into record(name,value) select 'log3',value from v$sysstat where STATISTIC# =194;
1 row inserted
SQL> commit;
Commit complete
SQL> insert into t1 select * from t1;
14336 rows inserted
SQL> rollback;
Rollback complete
SQL> insert into record(name,value) select 'log3',value from v$sysstat where STATISTIC# =194;
1 row inserted
SQL> commit;
Commit complete
SQL> insert into record(name,value) select 'log4',value from v$sysstat where STATISTIC# =194;
1 row inserted
SQL> commit;
Commit complete
SQL> insert into t1 select * from t1 nologging;
14336 rows inserted
SQL> rollback;
Rollback complete
SQL> insert into record(name,value) select 'log4',value from v$sysstat where STATISTIC# =194;
1 row inserted
SQL> commit;
Commit complete
SQL> --3.分析及结论
SQL> select name,max(value)-min(value) redosize from record group by name order by 2;
NAME REDOSIZE
-------------------- ----------
no2 1128
no1 11380
log2 700248
log1 702272
log4 782996
no4 785136
no3 788168
log3 789528
8 rows selected
第二次测试
SQL> select count(*) from t1;
COUNT(*)
----------
1835008
SQL> select name,max(value)-min(value) redosize from record group by name order by 2;
NAME REDOSIZE
-------------------- ----------
no2 18680
no1 352092
log2 88943948
log1 89526160
no4 100242344
log3 100398632
log4 100788388
no3 106720044
最小日志量
第一 insert /*+ append */ into t1 select * from t1 nologging; --nologging
第二 insert /*+ append */ into t1 select * from t1; -- nologging
最大日志量
insert into t1 select * from t1; --t1 nologging
insert into t1 select * from t1;
insert into t1 select * from t1 nologging;
在t1属性为nologging或logging下，日志量基本相同
insert /*+ append */ into t1 select * from t1;
insert /*+ append */ into t1 select * from t1 nologging;
在t1表logging状态下,日志量基本相同 且是该状态下日志量最小的。