日期:2014-05-17  浏览次数:20675 次

*********update 语句速度问题,帮忙看看**************
语句如下:
  update OT_114phone_navigation
  set CHARGETAG = 1
  where latn_id = an_latn_id
  and exists (select 1
  from cmss.pub_serv d
  where d.latn_id = an_latn_id
  and d.serv_id = main_serv_id
  and d.acct_month = ac_acct_month
  and d.m_charge > 0)

帮忙分析一下,怎样可以让这个语句速度快些。谢谢!!!!

------解决方案--------------------
请给出OT_114phone_navigation表和cmss.pub_serv表的记录数,以及这两个表都有什么索引。
------解决方案--------------------
update OT_114phone_navigation a ,cmss.pub_serv d 
set a.CHARGETAG = 1 
where latn_id = an_latn_id and d.latn_id = an_latn_id 
and d.serv_id = main_serv_id 
and d.acct_month = ac_acct_month 
and d.m_charge > 0

语句的改造好像这么改改或许能快些 ?

但完全取决于索引 ..
------解决方案--------------------
cmss.pub_ser表是否存在latn_id+serv_id+acct_month的复合索引?如果没有会很慢。
------解决方案--------------------
选择你的sql语句然后看下执行计划,看下是否用到了效率最高的索引,一般索引过多的情况下自动优化时有可能用了低效索引,这种情况下要指定索引
------解决方案--------------------
应该跟你建的索引有关吧
------解决方案--------------------
语句如下:
update OT_114phone_navigation
set CHARGETAG = 1
where latn_id = an_latn_id
and exists (select 1
from cmss.pub_serv d
where d.latn_id = an_latn_id
and d.serv_id = main_serv_id
and d.acct_month = ac_acct_month
and d.m_charge > 0) 

如果表cmss.pub_serv是复合索引的话,条件里应该把符合索引项的第一个项目作为第一个条件,如果不是,Oracle就不会用符合索引搜索
我的修改如下,可以试一下看看:

update OT_114phone_navigation
set CHARGETAG = 1
where latn_id = an_latn_id
and exists (select 1
from cmss.pub_serv d
where d.serv_id = main_serv_id
and d.latn_id = an_latn_id
and d.acct_month = ac_acct_month
and d.m_charge >=1) 
m_charge如果有索引的话会更快些,否则就Full_table_search了

如果是4楼的做法,应该把表数据量少的表作为基表,即from语句里应该把OT_114phone_navigation写成最后一个表
update OT_114phone_navigation a ,cmss.pub_serv d 
set a.CHARGETAG = 1 
where latn_id = an_latn_id and d.latn_id = an_latn_id 
and d.serv_id = main_serv_id 
and d.acct_month = ac_acct_month 
and d.m_charge > 0 


------解决方案--------------------
update OT_114phone_navigation a ,cmss.pub_serv d
set a.CHARGETAG = 1
where latn_id = an_latn_id and d.latn_id = an_latn_id
and d.serv_id = main_serv_id
and d.acct_month = ac_acct_month
and d.m_charge > 0
Mark