【原创】oracle中普通insert和直接装载的性能差别

这是以前做的一个测试,拿出来给大家作为参考吧

测试表:mqqtest

测试表原始数据量:0

插入数据量:1000w

分三种方法插入作为对比参数:

1.create table as select 方式

2.insert into方式

3.insert /*+append*/ into方式

第一种方法由于是一个ddl操作,不需写回滚段,因此耗时在dbwr

第二种方法是一个dml操作,在默认情况下是要写回滚段,因此增加了lgwr的额外开销

第三者方法显示的指定insert操作无需写回滚段,因此性能跟第一种方法一样。

方法一:create table mqqtest
as
select * from mqqstat.t_sub_stat where rownum <= 10000000
///////////////////////////////
Table created.

Elapsed: 00:01:53.79
//////////////////////////////

方法二:
insert into mqqtest(STATDATE, SERVICE_CLASS, SERVICE_ID,
   STATION, CITY_CODE, REG_MODE,
   UNREG_MODE, CHANNEL_CODE, SUBCHANNEL_CODE,
   PAY_WAY, FEATURE_STR, TOTALNUM,
   OPENNUM, CLOSENUM, CLOSEIN1DAYNUM,
   CLOSEIN3DAYNUM, O1REMAIN3, O2REMAIN3,
   O3REMAIN3, THISMONTHOPENCLOSE, HISTORYOPENCLOSE)
select STATDATE, SERVICE_CLASS, SERVICE_ID,
   STATION, CITY_CODE, REG_MODE,
   UNREG_MODE, CHANNEL_CODE, SUBCHANNEL_CODE,
   PAY_WAY, FEATURE_STR, TOTALNUM,
   OPENNUM, CLOSENUM, CLOSEIN1DAYNUM,
   CLOSEIN3DAYNUM, O1REMAIN3, O2REMAIN3,
   O3REMAIN3, THISMONTHOPENCLOSE, HISTORYOPENCLOSE
    from mqqstat.t_sub_stat where rownum <= 10000000

10000000 rows created.

Elapsed: 00:08:38.57

方法三:
insert /*+APPEND*/into mqqtest(STATDATE, SERVICE_CLASS, SERVICE_ID,
   STATION, CITY_CODE, REG_MODE,
   UNREG_MODE, CHANNEL_CODE, SUBCHANNEL_CODE,
   PAY_WAY, FEATURE_STR, TOTALNUM,
   OPENNUM, CLOSENUM, CLOSEIN1DAYNUM,
   CLOSEIN3DAYNUM, O1REMAIN3, O2REMAIN3,
   O3REMAIN3, THISMONTHOPENCLOSE, HISTORYOPENCLOSE)
select STATDATE, SERVICE_CLASS, SERVICE_ID,
   STATION, CITY_CODE, REG_MODE,
   UNREG_MODE, CHANNEL_CODE, SUBCHANNEL_CODE,
   PAY_WAY, FEATURE_STR, TOTALNUM,
   OPENNUM, CLOSENUM, CLOSEIN1DAYNUM,
   CLOSEIN3DAYNUM, O1REMAIN3, O2REMAIN3,
   O3REMAIN3, THISMONTHOPENCLOSE, HISTORYOPENCLOSE
    from mqqstat.t_sub_stat where rownum <= 10000000
//////////////////////////
10000000 rows created.

Elapsed: 00:01:25.73
/////////////////////////

posted @ 2008-02-27 09:29  jacktu  阅读(1870)  评论(0编辑  收藏  举报