批量更新数据表

时间:2022-08-18 14:50:36

      几天中午系统中断了几分钟,原因是数据库down了。当时发现系统中有大量的锁,几乎都是来自生产用户的,被锁的对象和语句都是平时每天都要千百次使用的。为什么会出现这么大面积的锁,原因到现在都还没弄明白。只是事后回忆起大量的锁中有条update的语句,是有个同事在批量更新数据,而是是关联了其他表的更新(数据量有160多万),而被更新的表正是被锁住的表,疑似这个更新导致了今天中午的down库。整个down库的过程是:数据库有锁-》2号机达到最大连接数-》停止2号机侦听无效-》重启2号机-》1号机失去响应达3分钟左右。

      这里不是要分析宕库的原因,而是要分析一下update语句。在网上找了个好帖:ORACLE UPDATE 语句语法与性能分析

      一般的update就不说了,真正难的是关联其他表得到被更新字段的值。也就是上文中的第3个,现摘录如下:

      两表(多表)关联update -- 被修改值由另一个表运算而来:

   update customers a   -- 使用别名
   set    city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
   where  exists (select 1
                  from   tmp_cust_city b
                  where  b.customer_id=a.customer_id
                 )
   -- update 超过2个值
   update customers a   -- 使用别名
   set    (city_name,customer_type)=(select b.city_name,b.customer_type
                                     from   tmp_cust_city b
                                     where  b.customer_id=a.customer_id)
   where  exists (select 1
                  from   tmp_cust_city b
                  where  b.customer_id=a.customer_id
                 ) 

       注意在这个语句中,
                                   =(select b.city_name,b.customer_type
                                     from   tmp_cust_city b
                                     where  b.customer_id=a.customer_id
                                    )
      与
                 (select 1
                  from   tmp_cust_city b
                  where  b.customer_id=a.customer_id
                 )
      是两个独立的子查询,查看执行计划可知,对b表/索引扫描了2篇;如果舍弃where条件,则默认对A表进行全表更新,但由于(select b.city_name from tmp_cust_city b where where  b.customer_id=a.customer_id)有可能不能提供"足够多"值,因为tmp_cust_city只是一部分客户的信息,所以报错(如果指定的列--city_name可以为NULL则另当别论):

      01407, 00000, "cannot update (%s) to NULL"
      // *Cause:
      // *Action:

      一个替代的方法可以采用:
      update customers a   -- 使用别名
      set    city_name=nvl((select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id),a.city_name)
      或者
      set    city_name=nvl((select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id),'未知')
      -- 当然这不符合业务逻辑了