更新语句的效率比较(merge into )

来源:未知 责任编辑:责任编辑 发表时间:2013-08-27 15:59 点击:

    昨晚更新了一批数据,用update的老办法耗时20多分,而用 merge into 不到2秒结束,效率真是天壤之别。具体见下:

     用T_TMP_SCHOOL(135868行)的BIRTH 字段更新T_TMP_NT_CUSTOMERDETAIL( 763119行) 的BIRTHDATE 字段,连接条件 T_TMP_SCHOOL.ID = t_tmp_nt_customerdetail.SCHOOLID

--表结构
create table T_TMP_NT_CUSTOMERDETAIL
(
  CUSTOMERID         VARCHAR2(15) not null,
  DOCCATEGORY        VARCHAR2(2) not null,
  DOCNUMBER          VARCHAR2(20) not null,
  BIRTHDATE          VARCHAR2(8),
  ...........
  SCHOOLID           VARCHAR2(60)
);

create table T_TMP_SCHOOL
(
  ID      VARCHAR2(20),
  COMPANY VARCHAR2(100),
  NAME    VARCHAR2(20),
  BIRTH   VARCHAR2(20)
);

--两个表的数据见下:
select count(1) from t_tmp_nt_customerdetail t;  --763119
select count(1) from  t_tmp_school;              --135868

--为了验证结果,测试前先清空birthdate的值,共更改 135879 行
update  t_tmp_nt_customerdetail t   
set t.birthdate = null
where t.schoolid is not null;

---实现的过程:
create or replace procedure p_tmp_update_customerdetail
is
  v_BeginTran INT := 0;    -- 事务标志,初始值为0,表示没有事务
  v_ErrCode   INT;
  v_ErrMsg    VARCHAR2(200);   -- 处理异常变量

begin
   -- 设置事务标志为1,表示开始事务
  v_BeginTran := 1;

  merge into t_tmp_nt_customerdetail t
  using (select b.id, b.birth from t_tmp_school b where b.birth is not null) a
     on (t.schoolid = a.id)
   when matched then
     update set t.birthdate = a.birth where t.schoolid is not null;

   COMMIT;
    -- 提交事务并且置事务标志为0。
    v_BeginTran := 0;

EXCEPTION
  WHEN OTHERS THEN
    -- 如果异常,回滚事务。
    IF v_BeginTran = 1 THEN
      ROLLBACK;
    END IF;
     v_ErrCode := SQLCODE;
    v_ErrMsg  := SUBSTR(SQLERRM, 1, 150);
    dbms_output.put_line(v_ErrCode);
    dbms_output.put_line(v_ErrMsg);
end;

--执行过程,用时1.11秒
SQL> exec  p_tmp_update_customerdetail;

--再次验证结果,先前清空birthdate的值已经有了,返回 135879 行
select count(1) from t_tmp_nt_customerdetail t  
where  t.schoolid is not null
and  t.birthdate is not  null;

--而用下面类似的语句,这些数据执行了24分钟多:
update t_tmp_nt_customerdetail t
   set t.birthdate = (select b.birth
                        from t_tmp_school b
                       where t.schoolid = b.id)
 where t.schoolid =
       (select c.id from t_tmp_school c where t.schoolid = c.id)
   and t.schoolid is not null;     ---注:为什么要写这个罗嗦的条件呢?因为没有这个条件就把整个表的数据全部更新了,因此必须写,所以大家应该多实践,不要被一些表面现象所蒙蔽。
 

 

 

 

本文出自 “srsunbing” 博客,请务必保留此出处http://srsunbing.blog.51cto.com/3221858/1130337

    发表评论
    请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
    用户名: 验证码:点击我更换图片
    最新评论 更多>>

    推荐热点

    • Request.ServerVariables 参数大全
    • 执行全文索引时出现权限不足的解决方法
    • 导入excel文件处理流程节点的解决方案
    • 查看sql修改痕迹(SQL Change Tracking on Table)
    • MongoDB安装为Windows服务方法与注意事项
    • App数据层设计及云存储使用指南
    • PostgreSQL启动过程中的那些事三:加载GUC参数
    • 写给MongoDB开发者的50条建议Tip1
    • Percolator与分布式事务思考(二)
    网站首页 - 友情链接 - 网站地图 - TAG标签 - RSS订阅 - 内容搜索
    Copyright © 2008-2015 计算机技术学习交流网. 版权所有

    豫ICP备11007008号-1