一次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语句时要仔细分析，尤其是多表的连接操作