*********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