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

技术2025-04-28 17

前天有同事在QQ上问了我个问题：问一下，insert之后update和insert语句中使用left join哪个效率高一些？每个update 差不多4个字段这样。我脑海里第1刻闪过的答案是：后者效率更高。依据如下： 1、从生成redo和undo来考虑 2、直观的执行时间考虑 3、减少访问表的次数事后把这个问题拿到QQ群，网络讨论的意见基本和我一致。那么下面我们就对此进行简单的测试，验证下理论依据。测试很简单，创建一个测试表(准备插入的数据是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-

最新回复(0)