一次SQL优化

前不久一位同事在做两个表关联更新，等了N久没执行出来，找我帮他看是什么原因。他的更新语句如下

update dsamerr201109 a

set (a.opr_status, a.new_result, a.unique_no6, a.unique_no7, renew_time)= (select '1',

note_info,

function_attr5,

function_attr6,

sysdateup_date

from dmcs.edmcsattributeday_suess b

where b.collect_day =

'20110919'

and b.renew_flag = '1'

and a.id = to_char(b.stream_id))

执行计划

update

indexfullscan ind_dsamerr201109

table access full edmcsattributeday_suess

他在a表的id字段上建了一个索引，但是我看执行计划并没有走索引，我在update后面加上index hint后，还是没有变化，任然没有走索引，后面在网上搜索了一下，查阅了相关的资料，发现是因为建表时id字段设置的是允许为空(nullable)的，这样会导致索引失效，修改为不为空后，执行计划发生了变化

出现了索引全扫描，但是任然是等待N久也没法更新完成，分析是因为这个索引全扫描会有回表操作，跟之前的执行效果差不多，仔细分析他的SQL语句后，发现他的SQL存在问题，对关联不上的记录会被置空，我在最后加上了一句

where exists(select 1 fromdmcs.edmcsattributeday_suess c where a.id=to_char(c.stream_id))

执行计划

update

nested loop

sort unique

table aceess full edmcsattributeday_suess

index range scan dsamerr201109
table access full edmcsattributeday_suess

再看执行计划发生了明显变化，对A表执行的是索引范围扫描了，执行成本和IO都得到了大幅下降执行效果也证实了这一点，仅用0.09s就完成了update。

总结

1、 建索引的字段不能设置为nullable，否则不会走索引

2、 索引全扫描的回表操作会导致性能下降

3、 编写SQL语句时要仔细分析，尤其是多表的连接操作