2021.04.22
SQLチューニング(第4回)「サブクエリと性能問題の理解」(2/8)
SQLチューニング第4回は、「サブクエリと性能問題理解」に関しての8回シリーズの第2回目になります。今回は、サブクエリーの典型的な2パターンに関する説明です。
2.1.1 サブクエリーに対する基本内容を理解すること
2.1.1.2 サブクエリの使用パターンに対して調べてみよう
サブクエリは場合により多様な形式で作成できるので、この中ではすべてのパターンを網羅するよりは、しばしば使われる二種類のパターンだけを用いてサブクエリの概念に対して説明する。
◎ 使用パターン[1]
SELECT *
FROM emp
WHERE sal > ( SELECT AVG( sal )
FROM emp )
使用パターン[1]とともに作成されたサブクエリの特徴は抽出結果が必ず1件でなければならないことである。 もし、サブクエリの結果が2件以上抽出されるならば、エラーが発生する。上記のようなパターンは普通サブクエリが先に実行され、その後にMain SQLのカラム値と比較する形態で実行されることが一般的である。
◎ 使用パターン[2]
SELECT c1, c2, c3
FROM SUBQUERY_T2 t2
WHERE c2 = 'A'
AND EXISTS (
SELECT /*+ NO_UNNEST */
'x'
FROM SUBQUERY_T1 t1
WHERE t1.c5 = t2.c2
)
使用パターン[2]は、EXISTSやIN演算子(またはNOT EXISTS、NOT IN)を使用した場合に、副問い合わせの結果を数件抽出することが可能である。サブクエリ内Main SQLとの接続条件であるT1.C5= T2.C2が存在(EXISTS、NOT EXISTSの場合)し、Main SQLから抽出した値を継承して、サブクエリのテーブルに対応する値が存在するかチェックするように実行される。もちろん逆にサブクエリーが最初に実行されMain SQLに値を渡すこともできる。
実際のサブクエリ使用時、性能問題を発生させる多くのパターンは使用パターン[2]のような方式である。したがって使用パターン[2]のような方式のSQLを作成後、サブクエリが作成意図に合うように実行されるか実行計画を必ず確認しなければならない。なぜなら、サブクエリが作成意図と違って実行される場合DBサーバーの性能に及ぼす影響力が大きいためである。このような検討はDBサーバーで発生可能な性能問題を予防できる良い方法である。
サブクエリの動作と活用の部分の説明に入る前に、サブクエリの基本特性について知っておく必要がある。この特性は、まさに、サブクエリから抽出されたデータが、重複する値が多くても、Main SQLのデータを増加させないということである。
理解を助けるためにテストを通じて調べてみることにする。
◎ テスト[1]. サブクエリの基本的な特性調べてみること
SELECT /*+ QB_NAME(B)*/col1
FROM (
SELECT LEVEL col1
FROM DUAL
CONNECT BY LEVEL <= 3
) a
WHERE a.col1 IN (
SELECT/*+ QB_NAME(A) */col1 -- 1,2,3がそれぞれ3ヶずつ出力
FROM (
SELECT LEVEL col1
FROM DUAL
CONNECT BY LEVEL <= 3
UNION ALL
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 3
UNION ALL
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 3
)
);
QUERY BLOCK Aつまり、サブクエリのみ実行された結果の値は、以下の通りである(QB_NAMEヒントにブロック名を指定)。
COL1
-----
1
2
3
1
2
3
1
2
3
QUERY BLOCK B、すなわち、全体のSQLを実行した結果の値は、以下の通りである。
COL1
-----
1
2
3
上記の結果を比較すると、サブクエリではMain SQLと結合連結条件に満足するデータが9件抽出されるが、全体SQLにより最終抽出されたデータはMain SQLの抽出データである3件だけである。 すなわち、サブクエリで抽出されるデータに重複値が多くてもUnique値のみ処理するので、サブクエリを結合に変更するSQLを作成する際、サブクエリの重複されたデータは、削除される特性をSQLに必ず反映する必要がある。
◎ テスト[2].サブクエリを結合(ビュー)で実行されるように誘導
SELECT /*+ ORDERED QB_NAME(B)*/
col1
FROM (
SELECT LEVEL col1
FROM DUAL
CONNECT BY LEVEL <= 3
) a
WHERE a.col1 IN (
SELECT /*+ QB_NAME(A) */
col1 -- 1,2,3がそれぞれ3ヶずつ出力
FROM (
SELECT LEVEL col1
FROM DUAL
CONNECT BY LEVEL <= 3
UNION ALL
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 3
UNION ALL
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 3
)
)
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 00:00:01 |
|* 1 | HASH JOIN | | 1 | 26 | 00:00:01 |
| 2 | VIEW | VW_NSO_1 | 3 | 39 | 00:00:01 |
| 3 | HASH UNIQUE | | 1 | 39 | |
| 4 | VIEW | | 3 | 39 | 00:00:01 |
| 5 | UNION-ALL | | | | |
|* 6 | CONNECT BY WITHOUT FILTERING| | | | |
| 7 | FAST DUAL | | 1 | | 00:00:01 |
|* 8 | CONNECT BY WITHOUT FILTERING| | | | |
| 9 | FAST DUAL | | 1 | | 00:00:01 |
|* 10 | CONNECT BY WITHOUT FILTERING| | | | |
| 11 | FAST DUAL | | 1 | | 00:00:01 |
| 12 | VIEW | | 1 | 13 | 00:00:01 |
|* 13 | CONNECT BY WITHOUT FILTERING | | | | |
| 14 | FAST DUAL | | 1 | | 00:00:01 |
---------------------------------------------------------------------------------
実行計画中ID 2を見ればVIEWというオペレーションが存在して、サブクエリがビューに変更されたということがわかる。 そしてID 3のHASH UNIQUEオペレーションを通じてOracleがサブクエリをビューに変更時、内部的に重複値を除去(DISTINCT処理)したということがわかる。
いかがでしたでしょうか?次回は、サブクエリの動作方式の1回目「FILTER動作方式」についてです。ご期待ください。それでは、See you ^-^