很多人开发人员不清楚update的原则，按照写代码的习惯写update：

update a set a.value=(select count(1) from b where a.col=b.col);

实际上，这个update语句大概运行逻辑是这样的：

for c in (select * from a) loop

select count(1) from b where b.col=c.col;

update ......

end loop;

遇到这种情况，常用的解决方法有两种：

1. 创建一个临时表，把b表的数据做好聚合后放到临时表上，这样就可以把对b表的扫描转换为更小的临时表的扫描；
2. 用merge代替update。

sql> merge into DM_REPORT_MV_LINE_EQUIPMENT T
2 using (SELECT RO.MRID,COUNT(C.ID) AS DLFJX_COUNT
3 FROM DM_FL_REGION_LINE RL,
4 DM_FL_LINE_SUBSTATION LS,
5 DM_FL_OBJECT RO,
6 DM_FL_SUBSTATION_DLFJX SD,
7 DM_INSTALL_HISTORY H,
8 DM_A_COMMON C
9 WHERE RL.OBJECT_A_ID = RO.ID
10 AND RL.OBJECT_B_ID = LS.OBJECT_A_ID
11 AND LS.OBJECT_B_ID = SD.ID
12 AND SD.ID = H.FUNCTION_ID
13 AND H.ASSET_ID = C.ID
14 GROUP BY RO.MRID) L
15 on (T.ID = L.MRID)
16 when matched then
17 update set T.DLFJX_COUNT = L.DLFJX_COUNT;

20 行已合并。

已用时间: 00: 00: 00.92

-----------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
24 db block gets
69072 consistent gets
0 physical reads
0 redo size
551 bytes sent via sql*Net to client
1084 bytes received via sql*Net from client
3 sql*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
20 rows processed

sql> UPDATE DM_REPORT_MV_LINE_EQUIPMENT T
2 SET T.DLFJX_COUNT = (SELECT L.DLFJX_COUNT
3 FROM (SELECT RO.MRID,COUNT(C.ID) AS DLFJX_COUNT
4 FROM DM_FL_REGION_LINE RL,
5 DM_FL_LINE_SUBSTATION LS,
6 DM_FL_OBJECT RO,
7 DM_FL_SUBSTATION_DLFJX SD,
8 DM_INSTALL_HISTORY H,
9 DM_A_COMMON C
10 WHERE RL.OBJECT_A_ID = RO.ID
11 AND RL.OBJECT_B_ID = LS.OBJECT_A_ID
12 AND LS.OBJECT_B_ID = SD.ID
13 AND SD.ID = H.FUNCTION_ID
14 AND H.ASSET_ID = C.ID
15 GROUP BY RO.MRID) L
16 WHERE T.ID = L.MRID);

已更新20行。

已用时间: 00: 00: 10.26

--------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
42 db block gets
227902 consistent gets
0 physical reads
5080 redo size
551 bytes sent via sql*Net to client
1422 bytes received via sql*Net from client
3 sql*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
20 rows processed

merge的原理是直接关联两个记录集，等到关联结果后直接基于关联结果进行更新，它对每个表只需扫描一次。update改为merge很简单，但是带很大的性能收益。

10046跟踪：

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.03 0 0 0 0
Execute 1 1.68 1.68 0 69072 22 20
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.73 1.71 0 69072 22 20

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 21.18 21.20 0 227902 44 20
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 21.20 21.21 0 227902 44 20