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