现在有两张表,一张tmp表9W多数据,另一张his表7KW多数据。
要将tmp表的一个字段,根据his表计算最大时间,来更新。现在速度挺慢的,有没有什么办法解决?update t_inf_tmp tmp
set tmp.tmp_ua_date =
(select max(to_char(his.insertdate, 'yyyymmddhh24miss') ||
rpad(his.useragent, 50, ' '))
from history_table his
where his.isdn = tmp.isdn
and his.insertdate < tmp.insertdate);his表isdn有建索引了。
要将tmp表的一个字段,根据his表计算最大时间,来更新。现在速度挺慢的,有没有什么办法解决?update t_inf_tmp tmp
set tmp.tmp_ua_date =
(select max(to_char(his.insertdate, 'yyyymmddhh24miss') ||
rpad(his.useragent, 50, ' '))
from history_table his
where his.isdn = tmp.isdn
and his.insertdate < tmp.insertdate);his表isdn有建索引了。
using (select tmp.rowid rid,
max(to_char(his.insertdate, 'yyyymmddhh24miss') ||
rpad(his.useragent, 50, ' ')) str
from history_table his, t_inf_tmp tmp
where his.isdn = tmp.isdn
and his.insertdate < tmp.insertdate
group by tmp.rowid) t2
on (t1.rowid = t2.rid)
when matched then
update set t1.tmp_ua_date = t2.str;
用merge into试试
merge into不太熟了。只知道是存在时更新,不存在的时候插入。merge into有提升性能?