谁帮我看看这个SQL，怎么效率这么低呢？在线等...

Question

update TEMP_FACILITY_MITIGATION_INFO f
   set f.DebtorRatingGrade = nvl((select r.RatingGrade99
                                   from rating_record_pd_corporate    r 
                                  where r.customerid = f.customerid
                                    and (SUBSTR(r.ApproveTime, 0, 10) <=
                                        f.recorddate and
                                        (SUBSTR(r.InvalidDate, 0, 10) >
                                        f.recorddate or
                                        r.InvalidDate is null))
                                    and r.procstatus = '40'
                                    and rownum = 1),
                                 'E') 
   where f.SendingOrg = '11111111111111111111111111111111'
   and f.RecordDate = '2010-09-30'
   and f.sendingdate = '2010-12-14 10:07:35';说明：TEMP_FACILITY_MITIGATION_INFO 表要更新的记录有2000条
      rating_record_pd_corporate 表里记录有一千多条

Answer 1

看看执行计划，是否是你内层子查询比较慢？
对你表rating_record_pd_corporate的customerid、procstatus加上索引呢，再对 ApproveTime、InvalidDate 加上一个函数索引