优化包含聚合运算的关联update

   很多人开发人员不清楚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