Oracle——插入效率普通insert和insert /*+append*/

在归档模式和非归档模式下，设定表为logging和nologging，测量普通的insert 和insert /*+append*/生成redo大小。第一次测试我对下列结果报怀疑态度，于是又重新测试一遍，结果差不多，想想是测试的数量太少。如果是在正式环境上做数据迁移，最好就选择最后一种模式。

模式	普通insert下redo生成量（Byte）	insert/*+append*/下redo生成量（Byte）
非归档模式(表logging)	5706324	88340
非归档模式(表nologging)	5685864	63348
归档模式(表logging)	5686196	63424
归档模式(表nologging)	5685364	63348

 

1.      非归档模式

SQL> create view m_redo as
  2   select  value
  3   from v$sysstat, v$statname
  4  where v$sysstat.statistic# =v$statname.statistic#
  5     and v$statname.name ='redo size';
SQL> create table test as select * from dba_objects where 1=0;

1.1表为logging

SQL> select * from  m_redo;
     VALUE
----------
   9236752
SQL> insert into test select * from dba_objects;

已创建50325行。

SQL> commit;
 
SQL> select * from  m_redo;
     VALUE
----------
  14943076
SQL> select (14943076-9236752)/1024/1024||'M' from dual;
(14943076-9236752)/10
---------------------
5.441974639892578125M
SQL> truncate table test;
SQL> select * from  m_redo;
     VALUE
----------
  15048424
SQL> insert /*+append*/  into test select * from dba_objects;

已创建50325行。

SQL> commit;
SQL> select * from  m_redo;
     VALUE
----------
  15136764
SQL> select (15136764-15048424)/1024/1024||'M' from dual;
(15136764-15048424)/
--------------------
.084247589111328125M

1.2表为nologging

SQL> truncate table test;
SQL> alter table test nologging;
SQL> select * from  m_redo;
     VALUE
----------
  15190864
SQL> insert into test select * from dba_objects;

已创建50325行。

SQL> commit;
SQL> select * from  m_redo;
     VALUE
----------
  20876728
SQL> select (20876728-15190864)/1024/1024||'M' from dual;
(20876728-15190864)/
--------------------
5.42246246337890625M
SQL> truncate table test;
SQL> select * from  m_redo;
     VALUE
----------
  20937256
SQL> insert /*+append*/  into test select * from dba_objects;
已创建50325行。
SQL> commit;
SQL> select * from  m_redo;
     VALUE
----------
  21000604
SQL> select (21000604-20937256)/1024/1024||'M' from dual;
(21000604-20937256)/
--------------------
.060413360595703125M

2.      归档模式

 在重新开一个session

SQL> conn test/test

已连接。

SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog
SQL> alter database open;

数据库已更改。

1.1  表为logging

SQL> truncate table test;

表被截断。

SQL> select * from m_redo;
    VALUE
----------
   130848
SQL> insert into test select * from dba_objects;

已创建50325行。

SQL> commit;
SQL> select * from m_redo;
    VALUE
----------
  5817044
SQL> select (5817044-130848)/1024/1024||'M' from dual;
(5817044-130848)/1024
---------------------
5.422779083251953125M
SQL> truncate table test;
SQL> select * from m_redo;
    VALUE
----------
  5879644
SQL> insert /*+append*/ into test select * from dba_objects;

已创建50325行。

SQL> commit;
SQL> select * from m_redo;
    VALUE
----------
  5943068
SQL> select (5943068-5879644)/1024/1024||'M' from dual;
(5943068-5879644
----------------
.06048583984375M

1.2  表为nologging

SQL> truncate table test;
SQL> alter table test nologging;

表已更改。

SQL> select * from m_redo;
    VALUE
----------
  5990988
SQL> insert into test select * from dba_objects;

已创建50325行。

SQL> commit;
SQL> select * from m_redo;
    VALUE
----------
  11676352
SQL> select (11676352-5990988)/1024/1024||'M' from dual;
(11676352-5990988)/10
---------------------
5.421985626220703125M
SQL> truncate table test;。
SQL> select * from m_redo;
    VALUE
----------
  11736328
SQL> insert /*+append*/ into test select * from dba_objects;

已创建50325行。

SQL> commit;
SQL> select * from m_redo;
    VALUE
----------
  11799676
SQL> select (11799676-11736328)/1024/1024||'M' from dual;
(11799676-11736328)/
--------------------
.060413360595703125M