UPDATE 処理速度検証

こじ

7年前

以前から気になっていた、UPDATE文の処理速度検証を行いました。

UPDATE文で内部ビューを使用した場合と、EXISTS句を使った場合で、どちらが速いか検証しました。

前提条件

下記の検証SQL1、検証SQL2を使って検証を行いました。SQL文は異なりますが、同じ更新結果となります。

検証SQL1／内部ビューを使ったUPDATE文

UPDATE
(
  SELECT
    SAM.NAME AS OLD_NAME
   ,PAT.NAME AS NEW_NAME
  FROM
    TB_SAMPLE SAM
      INNER JOIN WK_PATCH PAT ON PAT.ID = SAM.ID
) VW
SET
  VW.OLD_NAME = VW.NEW_NAME
;

検証SQL2／EXISTS句を使ったUPDATE文

UPDATE
  TB_SAMPLE SAM
SET
  SAM.NAME = (
    SELECT
      PAT.NAME
    FROM
      WK_PATCH PAT
    WHERE
      PAT.ID = SAM.ID
  )
WHERE
  EXISTS (
    SELECT
      *
    FROM
      WK_PATCH PAT
    WHERE
      PAT.ID = SAM.ID
  )
;

データ件数

TB_SAMPLEに100万レコード、WK_PATCHに10万レコードを用意しています。

テーブル定義

テーブル定義は下記になります。両テーブルのIDはPLAYMARY KEYになっています。

DESC TB_SAMPLE;

名前     NULL?    型
------- -------- -------------------
ID      NOT NULL NUMBER(10)
NAME             VARCHAR2(12 CHAR)

DESC WK_PATCH;
名前     NULL?    型
------- -------- -------------------
ID      NOT NULL NUMBER(10)
NAME             VARCHAR2(12 CHAR)

処理速度検証結果

回数/平均	検証SQL1／内部ビュー	検証SQL2／EXISTS
1回目	7.17秒	6.83秒
2回目	8.26秒	6.80秒
3回目	6.07秒	6.81秒
4回目	7.69秒	7.18秒
5回目	8.80秒	6.09秒
平均	7.60秒	6.74秒

「EXISTS句を使ったUPDATE文」の方が処理速度的に優れています。10万レコードの更新で約1秒の違いがでます。

実行計画の違い

実行計画の比較もしてみました。

検証SQL1／内部ビューを使ったUPDATE文

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |             |     1 |    38 |     4   (0)| 00:00:01 |
|   1 |  UPDATE                       | TB_SAMPLE   |       |       |            |          |
|   2 |   NESTED LOOPS                |             |       |       |            |          |
|   3 |    NESTED LOOPS               |             |     1 |    38 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL         | WK_PATCH    |     1 |    19 |     3   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | SYS_C007843 |     1 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| TB_SAMPLE   |     1 |    19 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

検証SQL2／EXISTS句を使ったUPDATE文

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |             |     1 |    22 |     5  (40)| 00:00:01 |
|   1 |  UPDATE                      | TB_SAMPLE   |       |       |            |          |
|   2 |   NESTED LOOPS               |             |     1 |    22 |     3  (34)| 00:00:01 |
|   3 |    SORT UNIQUE               |             |     1 |     3 |     1   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN          | SYS_C007844 |     1 |     3 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | SYS_C007843 |     1 |    19 |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID| WK_PATCH    |     1 |    19 |     1   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN         | SYS_C007844 |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

「検証SQL1／内部ビューを使ったUPDATE文」はWK_PATCHがTABLE ACCESS FULLになっていますが、「検証SQL2／EXISTS句を使ったUPDATE文」はINDEX UNIQUE SCANになっていますね。内部ビューの場合はなぜかINDEXが効かず、遅くなる原因になっているようです。

統計項目の違い

統計項目の比較結果になります。

統計項目	検証SQL1／内部ビュー	検証SQL2／EXISTS
recursive calls	18	9
db block gets	116512	103040
consistent gets	326273	203559
physical reads	0	0
redo size	40098192	31292992
bytes sent via SQL*Net to client	870	870
bytes received via SQL*Net from client	939	993
SQL*Net roundtrips to/from client	3	3
sorts (memory)	1	2
sorts (disk)	0	0
rows processed	100000	100000

TABLE ACCESS FULLの影響によるものだと思いますが、全体的に「検証SQL1／内部ビューを使ったUPDATE文」の方が値が大きくなっており負荷が高いと読み取れます。

結論

UPDATE文に処理速度を求めるのであれば「内部ビューを使ったUPDATE文」は使わずに「EXISTS句を使ったUPDATE文」を使って下さい。

個人的には「内部ビューを使ったUPDATE文」の方がSQLがすっきりするので好きなんですけどね。