语句如下:
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)帮忙分析一下,怎样可以让这个语句速度快些。谢谢!!!!
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)帮忙分析一下,怎样可以让这个语句速度快些。谢谢!!!!
有main_serv_id 做为索引。
cmss.pub_serv 几百万。
索引很多个。
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
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
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