构造例子

drop table if exists tt1;
drop table if exists tt2;
create table tt1 (id int primary key,c1 int,c2 int);
insert into tt1 select level,dbms_random.value(1,1000),dbms_random.value(1,1000) from dual connect by level<=3000000;
commit;
insert into tt1 select level+3000000,dbms_random.value(1,10000),dbms_random.value(1,1000) from dual connect by level<=2000000;
commit;
create table tt2 (id int primary key,d1 int,d2 int);
insert into tt2 select level,dbms_random.value(1,1000),dbms_random.value(1,1000) from dual connect by level<=500000;
commit;
dbms_stats.gather_table_stats(USER,'TT1',null,100);
dbms_stats.gather_table_stats(USER,'TT2',null,100);
create index IDX_DM_TT1_C1 on TT1(C1);
create index IDX_DM_TT2_d1 on TT2(d1);
update tt1 set c2=(select d2 from tt2 where tt1.c1=tt2.d1 and rownum=1) where c1<10;
计划如下：
1   #UPDATE : [0, 0, 0]; table(TT1), type(select), mpp_opt(0), hp_opt(0)
2     #PIPE2: [4, 27293, 16] 
3       #PRJT2: [3, 27293, 16]; exp_num(2), is_atom(FALSE) 
4         #SSEK2: [3, 27293, 16]; scan_type(ASC), IDX_DM_TT1_C1(TT1), scan_range(null2,10)
5       #SPL2: [1, 1, 8]; key_num(1), spool_num(0), is_atom(TRUE), has_var(1), sites(-)
6         #PRJT2: [1, 1, 8]; exp_num(1), is_atom(TRUE) 
7           #TOPN2: [1, 1, 8]; top_num(1)
8             #RN: [1, 500, 8] 
9               #BLKUP2: [1, 500, 8]; IDX_DM_TT2_D1(TT2)
10                #SSEK2: [1, 500, 8]; scan_type(ASC), IDX_DM_TT2_D1(TT2), scan_range[var2,var2]

在项目上遇到这样的update语句，从计划上分析每更新一行c2都要去tt2匹配数据。
分步分析：
1、update tt1 set c2=>? where c1<10;
相当于更新c1<10的数据，这里从计划上可以看c1<10的数据量是27293。
2、c2=(select d2 from tt2 where tt1.c1=tt2.d1 and rownum=1)
这一部分是标量子查询，是指去tt2中寻找满足tt1.c1=tt2.d1条件的d2值去更新c2,每更新一行就去匹配一次。
按照这样的理解可以知道该语句很低效。

如何优化

思路：先过滤数据再批量一次性更新
先tt2去重拿出想要的数据
select * from (select d2,d1,row_number()over(partition by d1 order by null) rn
from tt2 )t where rn=1
再与tt1关联去更新。
用merge into改写

merge into tt1 using (select * from (select d2,d1,row_number()over(partition by d1 order by null) rn 
from tt2 )t where rn=1 ) t on tt1.c1=t.d1 and tt1.c1<10 when matched then update set tt1.c2=t.d2;
计划如下：
1   #UPDATE : [0, 0, 0]; table(TT1), type(select), mpp_opt(0), hp_opt(0)
2     #NTTS2: [10, 2729, 24]; for_mdis(FALSE); is_atom(FALSE)
3       #UFLT: [10, 2729, 24]; IS_TOP_1(FALSE)
4         #PRJT2: [10, 2729, 24]; exp_num(2), is_atom(FALSE) 
5           #HASH2 INNER JOIN: [10, 2729, 24];  KEY_NUM(1); KEY(T.D1=TT1.C1) KEY_NULL_EQU(0)
6             #PRJT2: [4, 1000, 8]; exp_num(2), is_atom(FALSE) 
7               #SLCT2: [4, 1000, 8]; T.RN = var3
8                 #PRJT2: [3, 4198, 8]; exp_num(3), is_atom(FALSE) 
9                   #AFUN: [3, 4198, 8]; afun_num(1); partition_num(1)[DMTEMPVIEW_889194159.TMPCOL1]; order_num(1)[DMTEMPVIEW_889194159.TMPCOL0]
10                    #SORT3: [3, 4198, 8]; key_num(2), partition_key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
11                      #PRJT2: [3, 4198, 8]; exp_num(3), is_atom(FALSE) 
12                        #BLKUP2: [3, 4198, 8]; IDX_DM_TT2_D1(TT2)
13                          #SSEK2: [3, 4198, 8]; scan_type(ASC), IDX_DM_TT2_D1(TT2), scan_range(null2,10)
14            #SSEK2: [3, 27293, 16]; scan_type(ASC), IDX_DM_TT1_C1(TT1), scan_range(null2,10)

语句从原来的20s提升到0.2s

总结

语句利用到的优化准则是先过滤数据再关联。更新大量的数据时，merge into会比update高效一些。