Nologging and append test

歸檔模式

SQL

耗時（秒）

產生的redo日誌量

noarchivelog

create table t as select * from dba_objects ;

4.40

152152

create table t nologging as select * from dba_objects ;

5.59

153256

insert into t select * from dba_objects ;

6.04

56398248

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

4.20

130824

alter table t nologging ;

insert into t select * from dba_objects ;

5.90

56397756

alter table t nologging ;

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

4.21

131016

當存在索引的情況下：

create index idx_t on t (owner,object_id) ;

alter table t nologging ;

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

8.44

37078460

當存在索引的情況下：

create index idx_t on t (owner,object_id) ;

alter table t nologging ;

alter index idx_t nologging ;

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

8.38

37076400

歸檔模式

SQL

耗時（秒）

產生的redo日誌量

archivelog

create table t as select * from dba_objects ;

1.75

16002016

create table t nologging as select * from dba_objects ;

1.66

85072

insert into t select * from dba_objects ;

2.60

15919616

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

1.61

15980536

alter table t nologging ;

insert into t select * from dba_objects ;

2.65

15918656

alter table t nologging ;

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

1.62

62704

當存在索引的情況下：

create index idx_t on t (owner,object_id) ;

alter table t nologging ;

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

3.00

13012136

當存在索引的情況下：

create index idx_t on t (owner,object_id) ;

alter table t nologging ;

alter index idx_t nologging ;

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

3.11

13030424