SQL

UPDATE 処理速度検証

  • このエントリーをはてなブックマークに追加

以前から気になっていた、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がすっきりするので好きなんですけどね。

  • このエントリーをはてなブックマークに追加

コメントを残す

*