SELECT句の副問合せ処理速度検証

こじ

7年前

どんなシステムでもトランザクションテーブルにコード値を持たせて、コード値の名称はマスタテーブルに持たせるという方式がスタンダードな方式になっていると思います。

その際にマスタテーブルをINNER JOINして名称を取得する方法と、SELECT句でサブクエリを使用してコード値の名称を取得する方法の2パターンがありますが、処理速度的にどちらが優れているか検証しました。

INNER JOINとサブクエリのコード値の名称取得SQL 処理速度検証

TB_SAMPLEに1,000万レコードが格納されており、カラム「ID」はPKとなっています。

MST_ITEMには10レコードが格納されており、カラム「ITEM_CD」はPKとなっています。

下記は前提条件としてのデータ件数とデータの内容になります・

前提条件

--------------------------------------------
-- トランザクションテーブルのレコード件数
--------------------------------------------
SELECT COUNT(*) FROM TB_SAMPLE;

  COUNT(*)
----------
  10000000

--------------------------------------------
-- コード値毎のレコード件数
--------------------------------------------
SELECT 
  ITEM_CD
 ,COUNT(*)
FROM
  TB_SAMPLE
GROUP BY
  ITEM_CD
ORDER BY
  ITEM_CD
;

   ITEM_CD  COUNT(*)
---------- ----------
         1     998454
         2     999044
         3    1000188
         4    1000279
         5    1001524
         6     999894
         7    1000468
         8    1000246
         9    1000652
        10     999251

--------------------------------------------
-- マスタテーブルの件数
--------------------------------------------
SELECT COUNT(*) FROM MST_ITEM;

  COUNT(*)
----------
        10

--------------------------------------------
-- マスタテーブルの内容
--------------------------------------------
SELECT
  ITEM_CD
 ,ITEM_NM
FROM
  MST_ITEM
;

   ITEM_CD ITEM_NM
---------- --------------------
         1 歯ブラシ
         2 ポテトチップス
         3 アイスカフェラテ
         4 牛丼
         5 カレーライス
         6 からあげくん
         7 メロンパン
         8 えんぴつ
         9 筆箱
        10 オレンジジュース

INNER JOINでコード値の名称を取得するSQL

SELECT
  Z.ITEM_NM
 ,COUNT(*)
FROM
  (
    SELECT
      X.ID
     ,Y.ITEM_NM
    FROM
      TB_SAMPLE X
        INNER JOIN MST_ITEM Y
                ON Y.ITEM_CD = X.ITEM_CD
  ) Z
GROUP BY
  Z.ITEM_NM
;

サブクエリでコード値の名称を取得するSQL

SELECT
  Z.ITEM_NM
 ,COUNT(*)
FROM
(
  SELECT
    X.ID
   ,( SELECT
        Y.ITEM_NM
      FROM
        MST_ITEM Y
      WHERE
        Y.ITEM_CD = X.ITEM_CD
    ) ITEM_NM
  FROM
    TB_SAMPLE X
) Z
GROUP BY
  Z.ITEM_NM
;

INNER JOINとサブクエリの処理速度測定結果

回数/平均	INNER JOIN	サブクエリ
1回目	9.01秒	11.58秒
2回目	8.68秒	11.88秒
3回目	8.40秒	11.40秒
平均	8.7秒	11.6秒

結論としては「INNER JOINでコード値の名称を取得するSQL」の方が処理速度的に優れています。

1,000万レコードで処理測定を行うと3秒ぐらいの違いがでます。

INNER JOINとサブクエリのコード値の名称取得SQL 実行計画統計情報の比較

INNER JOINでコード値の名称を取得するSQL 実行計画

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |    10 |   360 |  5570   (7)| 00:01:07 |
|   1 |  HASH GROUP BY                |             |    10 |   360 |  5570   (7)| 00:01:07 |
|   2 |   MERGE JOIN                  |             |    10 |   360 |  5569   (7)| 00:01:07 |
|   3 |    TABLE ACCESS BY INDEX ROWID| MST_ITEM    |    10 |   200 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | SYS_C007813 |    10 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |             |    10 |   160 |  5567   (7)| 00:01:07 |
|   6 |     VIEW                      | VW_GBC_9    |    10 |   160 |  5566   (7)| 00:01:07 |
|   7 |      HASH GROUP BY            |             |    10 |    30 |  5566   (7)| 00:01:07 |
|   8 |       TABLE ACCESS FULL       | TB_SAMPLE   |    10M|    28M|  5274   (1)| 00:01:04 |
---------------------------------------------------------------------------------------------

サブクエリでコード値の名称を取得するSQL 実行計画

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |    10M|   209M|  5566   (7)| 00:01:07 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MST_ITEM    |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C007813 |     1 |       |     0   (0)| 00:00:01 |
|   3 |  HASH GROUP BY              |             |    10M|   209M|  5566   (7)| 00:01:07 |
|   4 |   VIEW                      |             |    10M|   209M|  5274   (1)| 00:01:04 |
|   5 |    TABLE ACCESS FULL        | TB_SAMPLE   |    10M|    28M|  5274   (1)| 00:01:04 |
-------------------------------------------------------------------------------------------

実行計画の違い

実行計画の違いは下記の3点になりました。

1. INNER JOINの方がCostが「4」多い

2. INNER JOINの方がBytesが「約208M」少ない

3. INNER JOINの方がマスターテーブルをFULLスキャンしているが、サブクエリの方はINDEXスキャンをしている

実行計画だけを確認するとINNER JOINのBytesが低く、サブクエリのBytesが極端に高いです。

サブクエリのクエリが遅くなる原因はBytesが高いという部分に起因していると読み取れます。

おそらくサブクエリは内部的にレコード件数分だけサブクエリを実行するため、ディスクI/Oがたくさん発生し遅くなるのではと思われます。

統計情報

統計項目	INNER JOIN	サブクエリ
recursive calls	1	0
db block gets	0	0
consistent gets	19282	19294
physical reads	19277	19277
redo size	0	0
bytes sent via SQL*Net to client	928	928
bytes received via SQL*Net from client	523	523
SQL*Net roundtrips to/from client	2	2
sorts (memory)	1	0
sorts (disk)	0	0
rows processed	10	10

統計項目の違いは下記になりました。

1. INNER JOINの方がrecursive calls(再帰的コールの回数)が「1」多い

2. INNER JOINの方がconsistent gets(一貫性読み込み回数)が「12」少ない

3. INNER JOINの方がsorts（memory）が「1」多い

統計情報の違いはほとんどありません。consistent gets(一貫性読み込み回数)だけ、INNER JOINの方が負荷は少しだけ少ないようです。

INNER JOINとサブクエリのコード値の名称取得SQL 処理速度計測の結論

サブクエリよりもINNER JOINを使った方が処理速度は速いので、なるべくINNER JOINを使った方がよいです。

INNER JOINを使った方が速いという理由は、INNER JOINはディスクI/Oが少なく、サブクエリは極端に多くなるためです。

実行計画のCostが高くてもディクスI/O（Bytes）が低いSQLの方が、処理速度が速いので、実行計画を確認する際はCostだけを確認しても失敗するので気を付けましょう。