Question

以下sql语句执行效率太低，如何改造才能提高其效率。

update dcustmsg d
set attr_code = substr(attr_code, 1, 4) ||
(select substr(b.attr_code, 5, 1)
from dgrpusermebmsg a, dcustmsg b
where a.member_id = d.id_no
and a.id_no = b.id_no
and substr(b.attr_code, 5, 1) !=
substr(d.attr_code, 5, 1)
and substr(d.attr_code, 5, 1) = '3') ||
substr(attr_code, 6, 3)
where exists (select 1
from dgrpusermebmsg a, dcustmsg b
where a.member_id = d.id_no
and a.id_no = b.id_no
and substr(b.attr_code, 5, 1) != substr(d.attr_code, 5, 1)
and substr(d.attr_code, 5, 1) = '3');

Answer 1

--不就是将d.attr_code与b.attr_code的第五位进行替换吗，你想多了
update dcustmsg d
set attr_code = substr(d.attr_code, 1, 4) ||
(select substr(b.attr_code, 5, 1)
from dgrpusermebmsg a, dcustmsg b
where a.member_id = d.id_no
and a.id_no = b.id_no) ||
substr(d.attr_code, 6, 3)
where substr(d.attr_code, 5, 1) != '3';

Answer 2

具体性能修改还得你自己捉摸，因为，你的数据架构别人不了解
但有一点一定能大大提高你这式子的性能，不要把函数放在where之后

Answer 3

exists 不如换成in ，当然后面不能是 select 1了，后面的语句也要换，然后不知道有没有建立索引，如果没有去填一个索引