oracle 多表update，索引失效

最新推荐文章于 2023-07-06 17:46:05 发布

原创最新推荐文章于 2023-07-06 17:46:05 发布 · 614 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

数据库专栏收录该内容

24 篇文章

订阅专栏

本文针对非唯一键关联的两张表进行高效数据更新的问题，提出了通过创建临时表并利用主键匹配来显著提升更新操作的速度，从原来的4分钟缩短至4秒。

这两天在写一个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分钟没有跑出结果，速度完全可以接受