|
很多人开发人员不清楚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 （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
