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

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

             模式普通insert下redo生成量（Byte）insert/*+append*/下redo生成量（Byte）非归档模式(表logging)570632488340非归档模式(表nologging)568586463348归档模式(表logging)568619663424归档模式(表nologging)568536463348

 

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----------   9236752SQL> insert into test select * from dba_objects;

已创建50325行。

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

已创建50325行。

SQL> commit;SQL> select * from  m_redo;     VALUE----------  15136764SQL> 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----------  15190864SQL> insert into test select * from dba_objects;

已创建50325行。

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

2.      归档模式

 在重新开一个session

SQL> conn test/test

已连接。

SQL>shutdown immediateSQL>startup mountSQL>alter database archivelogSQL> alter database open;

数据库已更改。

1.1  表为logging

SQL> truncate table test;

表被截断。

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

已创建50325行。

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

已创建50325行。

SQL> commit;SQL> select * from m_redo;    VALUE----------  5943068SQL> 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----------  5990988SQL> insert into test select * from dba_objects;

已创建50325行。

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

已创建50325行。

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