这两天在写一个sql,就是有A,B两个表,要利用b表的字段更新a表对应的字段。形如
update A set A.a=(select B.b from B where A.id=B.id);
这是一个非常常见的更新查询,但是在实际使用中,我要更新的a表和b表并不是主键相关,而是一个非唯一键相等,这样在实际中,观察执行计划发现根本不走索引,尽管我在A和B的相关字段上都添加了索引,甚至使用了
hint-/*+ index()*/ ,但是依然无效,在25w大小的数据量面前,两个全表查询连接完全无法接受。
update /*+ index(t INDEX_FLAG) */ t_download_temp t set t.status= (select /*+ index (t1 INDEX_URL_TEMP)*/ t1.url from t_download_temp t1 where t1.url=t.referer) where t.flag=1 and t.status=0
后来经过思考我采用了如下方案得到了较好的效果
truncate table t_download_temp2;
insert into t_download_temp2 select t.id mid,max(t1.referer) rurl,max(t1.id) rid,max(t1.status) rstatus from t_download_temp t ,t_download_temp t1 where t.referer_ref=t1.url and t.flag=1 and t.status=0 group by t.id;
实质就是先做select * from A,B where A.c=B.c(非主键)
然后insert到一个临时表,其中在临时表中存入A的主键,并对临时表设置主键。
update (select t1.*,t2.* from t_download_temp t1,t_download_temp2 t2 where t1.id=t2.id)
set status=rstatus ,refererid=rid,referer_ref=RURL;
然后采用oracle中主键相等可以采用的一种更新方式如上,这样总运行时间只花掉了4s左右,对比之前的sql
4分钟没有跑出结果,速度完全可以接受