2021.07.07
SQLチューニング（第9回）「サブクエリ動作方式を理解すること」(7/8)
今回は、結合とサブクエリに関する考察です。早速、始めましょう。
2.1.3.2. 結合の代わりにサブクエリを活用しよう
リゾルバの役割を実行するテーブルとは、From句に記載され、テーブルのSelect句に抽出するカラムはなく、単にWhere句に結合条件やFilter条件でのみ使用されているテーブルをいう。
単にリゾルバとしての役割のみを実行するテーブルに対して結合により処理するとき、多くの場合、予期しない非効率が発生することがある。リゾルバの役割のテーブルを結合で実行するように処理するときに発生するパフォーマンスの問題とその解決方法について調べる。理解を助けるために、以下のSQLを用いて説明する。
var b1 varchar2(10)
var b2 number
var b3 number
exec :b1 := 'A'
exec :b2 := 200000
exec :b3 := 300000
SELECT /*+ LEADING(T2 T1 T3) USE_NL(T2 T1 T3) */
DISTINCT t2.c1, t2.c2, t3.c3
FROM SUBQUERY_T2 T2,
SUBQUERY_T1 T1,
SUBQUERY_T3 T3
WHERE t2.c2 = :b1
AND t2.c1 >= :b2
AND t2.c1 <= :b3
AND t2.c1 = t1.c4
AND t2.c2 = t1.c5
AND t1.c4 = t3.c1
AND t1.c5 = t3.c2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.04 0 0 0 0
Fetch 130 1.06 7.25 1893 254597 0 1923
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 132 1.07 7.29 1893 254597 0 1923
Rows Row Source OPERATION
------- ---------------------------------------------------
1923 HASH UNIQUE (cr=254597 pr=1893 pw=0 time=7250803 us)
123072 FILTER (cr=254597 pr=1893 pw=0 time=282167079 us)
123072 TABLE ACCESS BY INDEX ROWID SUBQUERY_T3 (cr=254597 pr=1893 …)
246145 NESTED LOOPS (cr=254479 pr=1775 …)
123072 NESTED LOOPS (cr=8013 pr=1646 …)
3846 INDEX RANGE SCAN SUBQUERY_T2_IDX_01 (cr=12 pr=12 …)
123072 INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=8001 pr=1634 …)
123072 INDEX RANGE SCAN SUBQUERY_T3_IDX_01 (cr=246466 pr=129 …)
SQLおよび実行結果整理
- 結合順序:T2→ T1→ T3 (結合は固定、T2とT3の結合連結条件なし)
- データ抽出:t2.c1,t2.c2,t3.c3
- 条件節:WHERE t2.c2 =:b1 AND t2.c1 >=:b2 AND t2.c1 <=:b3 AND t2.c1 = t1.c4 AND t2.c2 = t1.c5 AND t1.c4 = t3.c1 AND t1.c5 = t3.c2
Key Point ： T2のC1カラムはUniqueであり、T1のC4カラムはUniqueではない。 そしてテーブルT3のC1カラムとC3カラム値がそれぞれUniqueである。SQLトレース結果を見れば、まずT2テーブルを先に読んで3,846件抽出する。 そしてT1テーブルと1:M結合を実行し、データが123,072件に大きく増加した。 抽出されたデータ123,072件は再びT3テーブルと1:1結合を実行して合計123,072件抽出された。 最終抽出された123,072件をDISTINCT処理して重複値を除去し、1,923件と大きく減少された。すなわち、T1テーブルと結合処理時不必要なデータが増加したと判断することができる。
したがってT2テーブルとT3テーブルを先に結合した後に最後にT1テーブルと結合するように結合順序を変えるならば、性能が改善されると予想される。 だが、T2とT3テーブル間には直接的な結合条件がなくて、結合順序を変更できないのでこの方法は使うことはできない。
結合順序を変更できないので、これ以上性能改善方法がなく見える。 だが、改善方法はある。もし、初めから重複値を抽出しないならば、結合連結の試行回数が減って非効率が改善される。
まず、T2とT1テーブルを結合すると、データが急激に増加したことから見てT1との結合時に重複データが多く発生したものと予想される。そして実際にデータを分析してみると、T1との結合時に、重複したデータが多く発生することを確認することができる。幸いなことにT1テーブルの場合、データは抽出せず、結合接続のみに関与するリゾルバの役割をするテーブルである。つまり、値を抽出せずに、単純にデータの存在の有無を確認する目的で使用されたテーブルであるため、EXISTSを使用するサブクエリで置き換えても、データの整合性を損なうことはない。従って次のようにEXISTSを使用したサブクエリにSQLを変更して実行すればよい。
Note.ただ、SUMやCOUNTなどのグループ関数を実行する場合はデータが毀損されることがあるので、データ整合性チェックは必ず実行しなければならない。 前の場合はデータ整合性き損が発生しない場合である。SELECT /*+ LEADING(T2 T1@SUB T3) USE_NL(T2 T1@SUB T3) */
t2.c1, t2.c2, t3.c3
FROM SUBQUERY_T2 T2,
SUBQUERY_T3 T3
WHERE t2.c2 = :b1
AND t2.c1 >= :b2
AND t2.c1 <= :b3
AND EXISTS ( SELECT /*+ QB_NAME(SUB) */
'x'
FROM SUBQUERY_T1 T1
WHERE t1.c4 = t2.c1
AND t1.c5 = t2.c2
AND t1.c4 = t3.c1
AND t1.c5 = t3.c2 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 130 0.06 0.10 0 12616 0 1923
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 132 0.06 0.10 0 12616 0 1923
Rows Row Source OPERATION
------- ---------------------------------------------------
1923 FILTER (cr=12616 pr=0 pw=0 time=94301 us)
1923 TABLE ACCESS BY INDEX ROWID SUBQUERY_T3 (cr=12616 pr=0 …)
3847 NESTED LOOPS (cr=12378 pr=0 pw=0 time=15396 us)
1923 NESTED LOOPS (cr=8269 pr=0 pw=0 time=88503 us)
3846 INDEX RANGE SCAN SUBQUERY_T2_IDX_01 (cr=140 pr=0 …)
1923 SORT UNIQUE (cr=8129 pr=0 pw=0 time=88914 us)
123072 INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=8129 pr=0 …)
1923 INDEX RANGE SCAN SUBQUERY_T3_IDX_01 (cr=4109 pr=0 …)
トレース結果を見れば、T2テーブルで抽出された3,846件に対してT1との結合の部分をEXISTSに変更した結果抽出されたデータが1,923件と大幅に減った。したがってT3テーブルの結合連結も123,072回から1,923回に大幅減って性能が大幅に改善された。
ここまでリゾルバの役割を実行するテーブルが結合で処理されて発生するパフォーマンスの問題の原因と改善方法について調べてみた。同様の型のSQLを作成するときに、SQLの性格を綿密に把握することはもちろん、サブクエリの特性をうまく活用して、最も効率的な方法でSQLを作成する必要がある。
リゾルバテーブルの話いかがでしたか？次回は、「サブクエリ動作方式を理解すること」の最終回として、Where節のサブクエリのお話をお届けします。See You ^-^
