关联更新建议用merge

本章节执行计划请自行采集。

1.数据准备

drop table test1;

drop table test2;

create table test1 as select * from dba_objects;

create table test2 as select * from dba_objects;

2.问题SQL

update test1 t1

set t1.object_id=(select max(t2.OBJECT_ID)

                  from test2 t2

                              where t1.object_name=t2.OBJECT_NAME)

where exists (select 1

              from test2 t

                       where t1.owner=t.owner);

Update下面跟了两个节点，类似于嵌套循环，表行数都是7万多，还没有索引，当然很慢。