どんなシステムでもトランザクションテーブルにコード値を持たせて、コード値の名称はマスタテーブルに持たせるという方式がスタンダードな方式になっていると思います。
その際にマスタテーブルを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だけを確認しても失敗するので気を付けましょう。