insert之后update和insert之中left join效率测试

izuoyan

浏览: 8949396 次
性别:
来自: 上海

最近访客更多访客>>

GDGZWQZ

morelily

eternal1025

devcang

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

2012-06 ( 32)
2012-05 ( 145)
2012-04 ( 50)
更多存档...

前天有同事在QQ上问了我个问题：
问一下，insert之后update和insert语句中使用left join哪个效率高一些？每个update 差不多4个字段这样。
我脑海里第1刻闪过的答案是：后者效率更高。
依据如下：
1、从生成redo和undo来考虑
2、直观的执行时间考虑
3、减少访问表的次数
事后把这个问题拿到，网络讨论的意见基本和我一致。
那么下面我们就对此进行简单的测试，验证下理论依据。
测试很简单，创建一个测试表(准备插入的数据是dba_objects的记录)，记录2个方案的redo、undo生成量和执行时间。
Last login: Sat Feb 12 10:06:58 2011 from 192.168.112.1
[oracle@gtlions ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 2月 12 10:12:22 2011

idle> conn store/store
已连接。
store(at)TEST> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

store(at)TEST> create table test_insert_update as select * from dba_objects where 1=2;

表已创建。

store(at)TEST> Create Global Temporary Table tempstat(Type varchar2(10),Sid Number,statname Varchar2(64),Value Number) On Commit Preserve Rows;

表已创建。
好勒，基本测试环境准备完毕，下面进行测试。
1、测试方案1，insert之后再update4个字段

store(at)TEST> Alter System Flush buffer_cache;

系统已更改。

store(at)TEST> Alter System Flush shared_pool;

系统已更改。
store(at)TEST> declare
2 begin
3 Insert Into tempstat(type,Sid,statname,value) Select 'begin',a.sid,b.NAME,a.VALUE From v$mystat a,v$statname b Where a.STATISTIC#=b.STATISTIC# And b.name In ('DB time','redo size','undo change vector size','recursive calls');
4 Insert Into test_insert_update Select * From Dba_Objects;
5 Update test_insert_update a Set (a.owner,a.object_name,a.object_type,a.status)=(Select 'test' owner,'test' object_name,'test' object_type,'test' status from dual);
6 Insert Into tempstat(type,Sid,statname,value) Select 'end',a.sid,b.NAME,a.VALUE From v$mystat a,v$statname b Where a.STATISTIC#=b.STATISTIC# And b.name In ('DB time','redo size','undo change vector size','recursive calls');
7 commit;
8 end;
9 /

PL/SQL 过程已成功完成。

store(at)TEST> column statname format a20;
store(at)TEST> select a.sid,a.statname,a.type,a.value,b.type,b.value from tempstat a, tempstat b where a.type='begin' and b.type='end' and a.statname=b.statname;

SID STATNAME TYPE VALUE TYPE VALUE
---------- -------------------- ---------- ---------- ---------- ----------
101 recursive calls begin 21208 end 24944
101 DB time begin 301 end 301
101 redo size begin 38324 end 32473384
101 undo change vector s begin 11048 end 11062672
ize

2、测试方案2，insert带上left join
为了干净测试，创建2个测试表格。
Last login: Sat Feb 12 10:12:19 2011 from 192.168.112.1
[oracle@gtlions ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 2月 12 10:19:40 2011

idle> conn store/store
已连接。
store(at)TEST> drop table test_insert_update;

表已删除。

store(at)TEST> drop table tempstat;

表已删除。
store(at)TEST> create table test_insert_update as select * from dba_objects where 1=2;

表已创建。

store(at)TEST> Create Global Temporary Table tempstat(Type varchar2(10),Sid Number,statname Varchar2(64),Value Number) On Commit Preserve Rows;

表已创建。

store(at)TEST> Alter System Flush buffer_cache;

系统已更改。

store(at)TEST> Alter System Flush shared_pool;

系统已更改。

store(at)TEST> declare
2 begin
3 Insert Into tempstat(type,Sid,statname,value) Select 'begin',a.sid,b.NAME,a.VALUE From v$mystat a,v$statname b Where a.STATISTIC#=b.STATISTIC# And b.name In ('DB time','redo size','undo change vector size','recursive calls');
Insert Into test_insert_update Select b.owner,b.object_name,a.SUBOBJECT_NAME,a.OBJECT_ID,a.DATA_OBJECT_ID,b.object_type,a.CREATED,a.LAST_DDL_TIME,a.TIMESTAMP,b.status,a.TEMPORARY,a.GENERATED,a.SECONDARY,a.namespace,a.EDITION_NAME From Dba_Objects a
5 left join (Select 'test' owner,'test' object_name,'test' object_type,'test' status from dual) b on 1=1;
6 Insert Into tempstat(type,Sid,statname,value) Select 'end',a.sid,b.NAME,a.VALUE From v$mystat a,v$statname b Where a.STATISTIC#=b.STATISTIC# And b.name In ('DB time','redo size','undo change vector size','recursive calls');
7 commit;
8 end;
9 /

PL/SQL 过程已成功完成。

SID STATNAME TYPE VALUE TYPE VALUE
---------- -------------------- ---------- ---------- ---------- ----------
101 recursive calls begin 54213 end 57750
101 DB time begin 420 end 420
101 redo size begin 48688 end 6541740
101 undo change vector s begin 14676 end 268776
ize

store(at)TEST> Select 6541740-48688 As "测试2redo",32473384-38324 As "测试1redo",268776-14676 As "测试2undo",11062672-11048 As "测试1undo"From dual;

测试2redo 测试1redo 测试2undo 测试1undo
---------- ---------- ---------- ----------
6493052 32435060 254100 11051624
小结
比较这2次测试，数据是最有力的证据。我们可以看到测试结果正如我们预料的那样，在语句当中尽量减少表格的访问次数，能在一起执行的语句不要人为分开来。
自己有点疑问的是DB time似乎我理解错误了：为什么这个值在两次测试都没有变化。
-The End-

分享到：