批量更新数据表
几天中午系统中断了几分钟，原因是数据库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),'未知')
-- 当然这不符合业务逻辑了
