update文と副問い合わせ (UPDATE with sub query)

見逃されがちなUPDATE文の構文について説明します。


以下のSQLは、同一の表 sales に副問合せで3回アクセスしています。

List 1:

update customers cst
   set last_purchase_date = (select max(time_id) from sales where cust_id = cst.cust_id),
       total_amount       = (select sum(amount_sold) from sales where cust_id = cst.cust_id),
       annual_amount      = (select sum(case when time_id > to_date('20000101','yyyymmdd') then amount_sold else 0 end) from sales where cust_id = cst.cust_id)
 where cust_id = 2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0        389          2           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0        389          2           1


Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  CUSTOMERS (cr=389 pr=0 pw=0 time=2019 us)
         1          1          1   INDEX UNIQUE SCAN CUSTOMERS_PK (cr=2 pr=0 pw=0 time=20 us cost=1 size=40 card=1)(object id 76070)
         1          1          1   SORT AGGREGATE (cr=129 pr=0 pw=0 time=854 us)
       176        176        176    TABLE ACCESS BY GLOBAL INDEX ROWID SALES PARTITION: ROW LOCATION ROW LOCATION (cr=129 pr=0 pw=0 time=829 us cost=90 size=1690 card=130)
       176        176        176     INDEX RANGE SCAN SALES_CUST_IX (cr=3 pr=0 pw=0 time=21 us cost=3 size=0 card=130)(object id 76319)
         1          1          1   SORT AGGREGATE (cr=129 pr=0 pw=0 time=421 us)
       176        176        176    TABLE ACCESS BY GLOBAL INDEX ROWID SALES PARTITION: ROW LOCATION ROW LOCATION (cr=129 pr=0 pw=0 time=282 us cost=90 size=1300 card=130)
       176        176        176     INDEX RANGE SCAN SALES_CUST_IX (cr=3 pr=0 pw=0 time=10 us cost=3 size=0 card=130)(object id 76319)
         1          1          1   SORT AGGREGATE (cr=129 pr=0 pw=0 time=567 us)
       176        176        176    TABLE ACCESS BY GLOBAL INDEX ROWID SALES PARTITION: ROW LOCATION ROW LOCATION (cr=129 pr=0 pw=0 time=550 us cost=90 size=1300 card=130)
       176        176        176     INDEX RANGE SCAN SALES_CUST_IX (cr=3 pr=0 pw=0 time=192 us cost=3 size=0 card=130)(object id 76319)

この様なupdate文については、以下の構文への変換が効果的です。

List 2:

update customers cst
   set (last_purchase_date, total_amount, annual_amount)
       =
       (select max(time_id),
               sum(amount_sold),
               sum(case when time_id > to_date('20000101','yyyymmdd') then amount_sold else 0 end)
          from sales
         where cust_id = cst.cust_id)
 where cust_id = 2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0        131          2           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0        131          2           1



Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  CUSTOMERS (cr=131 pr=0 pw=0 time=672 us)
         1          1          1   INDEX UNIQUE SCAN CUSTOMERS_PK (cr=2 pr=0 pw=0 time=11 us cost=1 size=40 card=1)(object id 76070)
         1          1          1   SORT AGGREGATE (cr=129 pr=0 pw=0 time=604 us)
       176        176        176    TABLE ACCESS BY GLOBAL INDEX ROWID SALES PARTITION: ROW LOCATION ROW LOCATION (cr=129 pr=0 pw=0 time=725 us cost=90 size=2340 card=130)
       176        176        176     INDEX RANGE SCAN SALES_CUST_IX (cr=3 pr=0 pw=0 time=12 us cost=3 size=0 card=130)(object id 76319)

実行計画からも、処理が改善された事が分かります。
sales 表への読み込みが3回→1回に減ったことで、論理読み込みも低減しています。

最近の潮流ではExadata等、足回り（H/W）の強化でボトルネックを解消する事が多くなっていますが、ミッションクリティカルなシステムでは、この様に処理そのものの改善で対応する事が先決です。

---

[summary]
List 1 shows bad UPDATE statement.  It accesses sales table three times.
To reduce consistent read access, it should be written like List 2.