登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

飞哥的技术博客

世上无难事,只怕有心人!

 
 
 

日志

 
 
 
 

存在性的判断和MERGE语法的使用  

2009-06-23 20:26:26|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 存在性的判断和MERGE语法的使用收藏

 

select count(*) into t_count from t where condition;
if t_count> 0 then ....

这种方法的问题在于:我们需要的仅仅是是否存在,而不是得到总记录数。查询记录总数付出了不必要的性能代价。

两种情况:
1. 如果判断是否存在记录后, 要查询记录中的某些列的信息,或者是决定要对表进行insert/update操作,典型的操作为:

select count(*) into t_count from t where condition;

   select cols into t_cols from t where condition;

select count(*) into t_count from t where condition;

这两种操作,都可以采用直接操作,然后进行例外处理的方式,根本就不进行这个存在性判断!

  select cols into t_cols from t where condition;

  when no_data_found then begin

update t set ... where condition;

  when DUP_VAL_ON_INDEX then begin

MERGE语句是Oracle9i新增的语法,用来合并UPDATE和INSERT语句。通过MERGE语句,根据一张表或子查询的连接条件对另外一张表进行查询,连接条件匹配上的进行UPDATE,无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE。

下面看个具体的例子:

create table t(id number, name varchar2(10));

create table tt(id number, name varchar2(10));

insert into t(id,name)values(1,'dm');

insert into tt(id,name)values(1,'dddddd');

merge into t

using tt

on (t.id=tt.id)

when matched then update set t.id=tt.id

when not matched then  insert values(tt.id,tt.name)

ORA-38104:无法更新ON子句中引用的列:"T"."ID"

错误原因是:不允许更新关联的列。

merge into t

using tt

on (t.id=tt.id)

when matched then update set t.name=tt.name

when not matched then  insert values(tt.id,tt.name)

insert into tt(id,name)values(1,'mmmmmm');

select * from tt;

merge into t

using tt

on (t.id=tt.id)

when matched then update set t.name=tt.name

when not matched then  insert values(tt.id,tt.name)

这个错误是由于根据条件(t.id=tt.id)连接之后得到的结果集不唯一。

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

在之前,merge语句支持匹配更新和不匹配插入2种简单的用法,在10g中Oracle对merge语句做了增强,增加了条件选项和DELETE操作。下面我通过一个demo来简单介绍一下10g中merge的增强和10g前merge的用法。

参考Oracle 的SQL Reference,大家可以看到Merge Statement的语法如下:
MERGE [hint] INTO [schema .] table [t_alias] USING [schema .]
{ table | view | subquery } [t_alias] ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;

下面我在windows xp 下10.2.0.1版本上做一个测试看看

SQL> select * from v$version;

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL> create table subs(msid number(9),

  2                    ms_type char(1),

  3                    areacode number(3)

SQL> create table acct(msid number(9),

  2                    bill_month number(6),

  3          areacode   number(3),

  4                    fee        number(8,2) default 0.00);

SQL> insert into subs values(905310001,0,531);

SQL> insert into subs values(905320001,1,532);

SQL> insert into subs values(905330001,2,533);

1) matched

     using subs b on (a.msid=b.msid)

        update set a.areacode=b.areacode

        insert(msid,bill_month,areacode)

        values(b.msid,'200702',b.areacode);

2) 只有not matched clause,也就是只插入不更新

     using subs b on (a.msid=b.msid)  

        insert(msid,bill_month,areacode)

    values(b.msid,'200702',b.areacode);

3)

     using subs b on (a.msid=b.msid)

        update set a.areacode=b.areacode

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

      MSID BILL_MONTH AREACODE        FEE

---------- ---------- -------- ----------

  2       using subs b on (a.msid=b.msid)

  4          update set a.areacode=b.areacode

  5     when NOT MATCHED then

  6          insert(msid,bill_month,areacode)

  7          values(b.msid,'200702',b.areacode);

      MSID BILL_MONTH AREACODE        FEE

---------- ---------- -------- ----------

905320001     200702      532       0.00

905330001     200702   533       0.00

905310001     200702      531       0.00

SQL> insert into subs values(905340001,3,534);

  2       using subs b on (a.msid=b.msid)

  3     when NOT MATCHED then

  4          insert(msid,bill_month,areacode)

  5          values(b.msid,'200702',b.areacode);

      MSID BILL_MONTH AREACODE        FEE

---------- ---------- -------- ----------

905320001     200702      532       0.00

905330001     200702      533       0.00

905310001     200702      531       0.00

905340001     200702      534       0.00

SQL> update subs set areacode=999;

      MSID BILL_MONTH AREACODE        FEE

---------- ---------- -------- ----------

905320001     200702      532       0.00

905330001     200702      533       0.00

905310001     200702      531      0.00

905340001     200702      534       0.00

  2       using subs b on (a.msid=b.msid)

  4          update set a.areacode=b.areacode;

      MSID BILL_MONTH AREACODE        FEE

---------- ---------- -------- ----------

905320001     200702      999       0.00

905330001     200702      999       0.00

905310001     200702      999       0.00

905340001     200702      999       0.00

1) matched

     using subs b on (a.msid=b.msid)    

        update set a.areacode=b.areacode

        insert(msid,bill_month,areacode)

        values(b.msid,'200702',b.areacode)

2) 只有not matched clause,也就是只插入不更新

     using subs b on (a.msid=b.msid)  

        insert(msid,bill_month,areacode)

     values(b.msid,'200702',b.areacode)

3)

     using subs b on (a.msid=b.msid)

        update set a.areacode=b.areacode

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

      MSID BILL_MONTH AREACODE        FEE

---------- ---------- -------- ----------

  2       using subs b on (a.msid=b.msid)

  4          update set a.areacode=b.areacode

  5          where b.ms_type=0

  6     when NOT MATCHED then

  7          insert(msid,bill_month,areacode)

  8          values(b.msid,'200702',b.areacode)

  9          where b.ms_type=0;

      MSID BILL_MONTH AREACODE        FEE

---------- ---------- -------- ----------

905310001     200702      531       0.00

SQL> insert into subs values(905360001,0,536);

  2       using subs b on (a.msid=b.msid)

  3     when NOT MATCHED then

  4          insert(msid,bill_month,areacode)

  5  values(b.msid,'200702',b.areacode)

  6          where b.ms_type=0;

      MSID BILL_MONTH AREACODE        FEE

---------- ---------- -------- ----------

905310001     200702      531       0.00

905360001     200702 536       0.00

SQL> update subs set areacode=888 where ms_type=0;

      MSID BILL_MONTH AREACODE        FEE

---------- ---------- -------- ----------

905310001     200702      531       0.00

905360001     200702      536       0.00

  2 using subs b on (a.msid=b.msid)

  4          update set a.areacode=b.areacode

  5          where b.ms_type=0;

      MSID BILL_MONTH AREACODE        FEE

---------- ---------- -------- ----------

905310001     200702      888       0.00

905360001     200702      888       0.00

An optional DELETE WHERE clause can be used to clean up after a

merge operation. Only those rows which match both the ON clause

and the DELETE WHERE clause are deleted.

     using subs b on (a.msid=b.msid)

        update set a.areacode=b.areacode       

        delete where (b.ms_type!=0);            

  2       using subs b on (a.msid=b.msid)

  4          update set a.areacode=b.areacode

  5          delete where (b.ms_type!=0);

更为详尽的语法,请参考Oracle SQL Reference手册!

以下是对采用merge语法做插入操作与一般的单独插入操作做性能比较测试:                    

                    

create table subs1

as

from dba_objects

create table acct(id number(9),

                   NAME VARCHAR2(10)

create table acct1(id number(9),

                   NAME VARCHAR2(10)

create table acct2(id number(9),

SQL> set timing on

SQL> merge into acct a

Done

Executed in 7.172 seconds

SQL> insert into acct2 b

  4                  from acct2 b

  5                  where a.id=b.id);

50376 rows inserted

Executed in 0.125 seconds

SQL> BEGIN

  3            where not exists(select 1

  4                            from acct1 b

  5                            where a.id=b.id)

  7  INSERT INTO acct1 values(R.ID,R.NAME);

PL/SQL procedure successfully completed

Executed in 5.312 seconds

分析:法2的插入性能是最快的,因为此种插入是批量操作,不会记录日志

      法1的插入与法3比较,说明merge语法对于只做插入或者只做更新时并不一定是性能最好的。

  评论这张
 
阅读(455)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018