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



发布了144 篇原创文章 · 获赞 125 · 访问量 67万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览