update tt_sta_mon_new
set during_new =(select during_new from
(select calling_code,sum(duration) as during_new
from tlw_list_ip group by calling_code) a
where a.calling_code = tt_sta_mon_new.calling_no)
tlw_list_ip这张表里的数据量比较大 有好几百万条
我执行这行这条语句三个小时没执行完成
请教各位高手
set during_new =(select during_new from
(select calling_code,sum(duration) as during_new
from tlw_list_ip group by calling_code) a
where a.calling_code = tt_sta_mon_new.calling_no)
tlw_list_ip这张表里的数据量比较大 有好几百万条
我执行这行这条语句三个小时没执行完成
请教各位高手
from tlw_list_ip group by calling_code
改为:
select calling_code,sum(duration) as during_new
from tlw_list_ip where a.calling_code = tt_sta_mon_new.calling_no
这个问题比较关键
然后,几百万的表,为什么动不动就全表操作呢,难道没有什么约束条件?
update tt_sta_mon_new
set during_new =
(select sum(duration) as during_new
from tlw_list_ip
where calling_code = tt_sta_mon_new.calling_no)
比如
update tt_sta_mon_new
set during_new =(select during_new from
(select calling_code,sum(duration) as during_new
from tlw_list_ip group by calling_code) a
where a.calling_code = tt_sta_mon_new.calling_no)本来运算9次,但是你的这个就应该是 8*8 + 1次
所以你的速度就不能提高上去
set during_new =
(select sum(duration) as during_new
from tlw_list_ip
where calling_code = tt_sta_mon_new.calling_no)
所以我认为,即使有索引,也不如新建表
set during_new =
(select sum(duration) as during_new
from tlw_list_ip
where calling_code = tt_sta_mon_new.calling_no)这种方式至少可以在半分钟检索完.
2 重建索引