2021.10.06
SQLチューニング（第15回）「スカラーサブクエリの理解と効率的なSQLの作成」(5/5)
今回は、スカラーサブクエリと結合に関しての2回目・スカラーサブクエリの理解と効率的なSQLの作成の最終回です。
3.1.2.2 スカラーサブクエリと結合の関係から見るSQL性能問題
以下の二つのSQL中SCALAR_SQL[1]はFrom句にSCALAR_T1テーブルだけ指定し、SCALAR T2,SCALAR_T3テーブルはスカラーサブクエリを使うように作成した。 また、SCALAR SQL[2]はSCALAR_T1,SCALAR_T2,SCALAR_T3テーブルを全部結合した後データを抽出するようにSQLを作成した。
一般的にSQLを作成する時ある作成方法が性能上有利かを知ることができる公式は存在しない。 しかし処理データ量、テーブルやカラムの制約条件、インデックス構成有無などを考慮しなかったSQL作成は性能問題を発生させることがあるので注意しなければならない。
以下の例題を通じてSCALAR_SQL[1]とSCALAR_SQL[2]がどんな状況で性能問題が発生するのか、そして発生した性能問題をどのように改善するのが効率的なのか調べてみる。
SCALAR_SQL[1]
SELECT t1.c1,
t1.c2,
t1.c3,
(SELECT t2.C3
FROM SCALAR_T2 T2
WHERE t2.c1 = t1.c1) AS t2_c3,
(SELECT t3.C3
FROM SCALAR_T3 T3
WHERE t3.c1 = t1.c1) AS t3_c3
FROM SCALAR_T1 T1
ORDER BY t1.c1, t1.c2 ;
SCALAR_SQL[2]
SELECT ROWNUM rnum,
X.*
FROM (
SELECT /*+ USE_NL(T1 T2 T3) */
t1.c1,
t1.c2,
t1.c3,
t2.c3 AS t2_c3,
t3.c3 AS t3_c3
FROM SCALAR_T1 T1,
SCALAR_T2 T2,
SCALAR_T3 T3
WHERE t1.c1 = t2.c1(+)
AND t1.c1 = t3.c1(+)
ORDER BY t1.c1, t1.c2 ) X
WHERE ROWNUM <= 10 ;
SCALAR_SQL[1]トレース結果
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 33335 4.18 4.20 0 3003406 0 500000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 33337 4.18 4.20 0 3003406 0 500000
Rows Row Source Operation
------- ---------------------------------------------------
500000 TABLE ACCESS BY INDEX ROWID SCALAR_T2 (cr=1501114 pr=0 pw=0 time=1597402 us)
500000 INDEX RANGE SCAN SCALAR_T2_IDX_01 (cr=1001114 pr=0 …)
500000 TABLE ACCESS BY INDEX ROWID SCALAR_T3 (cr=1501114 pr=0 pw=0 time=1640391 us)
500000 INDEX RANGE SCAN SCALAR_T3_IDX_01 (cr=1001114 pr=0 …)
500000 SORT ORDER BY (cr=3003406 pr=0 pw=0 time=3775664 us)
500000 TABLE ACCESS FULL SCALAR_T1 (cr=1178 pr=0 pw=0 time=37 us)
SCALAR_SQL[2]トレース結果
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 2 4.41 4.44 0 3003406 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 4.41 4.45 0 3003406 0 10
Rows Row Source Operation
------- ---------------------------------------------------
10 COUNT STOPKEY (cr=3003406 pr=0 pw=0 time=4448209 us)
10 VIEW (cr=3003406 pr=0 pw=0 time=4448205 us)
10 SORT ORDER BY STOPKEY (cr=3003406 pr=0 pw=0 time=4448202 us)
500000 NESTED LOOPS OUTER (cr=3003406 pr=0 pw=0 time=4500101 us)
500000 NESTED LOOPS OUTER (cr=1502292 pr=0 pw=0 time=2000090 us)
500000 TABLE ACCESS FULL SCALAR_T1 (cr=1178 pr=0 pw=0 time=46 us)
500000 TABLE ACCESS BY INDEX ROWID SCALAR_T2 (cr=1501114 pr=0 …)
500000 INDEX RANGE SCAN SCALAR_T2_IDX_01 (cr=1001114 pr=0 …)
500000 TABLE ACCESS BY INDEX ROWID SCALAR_T3 (cr=1501114 pr=0 pw=0 time=1929888)
500000 INDEX RANGE SCAN SCALAR_T3_IDX_01 (cr=1001114 pr=0 …)
① SCALAR_SQL[1]およびSCALAR_SQL[2]のSQL説明および改善方向の導き出し
(1) SCALAR_SQL[1] SQL説明
・ SCALAR_T1 50万件抽出
・ SCALAR_T2とSCALAR_T3 (スカラーサブクエリ)を50万回インデックス スキャン反復実行
・ 最終50万件抽出(多くのデータ抽出)
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
改善方向 ： SCALAR_T2,SCALAR_T3データ照会方式をスカラーサブクエリで結合に変更した以後Hash Joinに誘導
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー(2) SCALAR_SQL[2] SQL説明
・ SCALAR_T1 50万件抽出
・ SCALAR_T2,SCALAR_T3 50万番インデックス スキャン反復実行(Nested Loops Join) (Outer Joinで、C1価格はUniqueある値である)
・ 最終10件抽出(少ないデータ抽出)
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
改善方向 ： SCALAR_T2,SCALAR_T3データ照会方式を結合でスカラーサブクエリでに変更
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーSCALAR_SQL[1]とSCALAR_SQL[2]のトレース結果を見れば、二種類の方法とも性能問題を有している。 したがってSCALAR_SQL[1]とSCALAR_SQL[2]の性能問題を解決できる方法に対して調べてみる。
② 非効率スカラーサブクエリは結合に変更しよう
一般的に配置プログラム(多くのデータ抽出)はスカラーサブクエリを使わないようにSQLを作成することが性能に有利な場合が多い。 SCALAR_SQL[1]の場合SCALAR_T1テーブルのすべてのデータを抽出(50万件)し、SCALAR_T2、SCALAR_T3テーブル照会をスカラーサブクエリで実行するようにSQLが作成されている。 したがってSCALAR_T2とSCALAR_T3のインデックスを実行する回数が抽出データと同一となり、50万回ずつ実行(C1カラムはUnique値を有している)して性能問題を発生させている。
このように最終抽出件数が多くて、スカラーサブクエリ実行回数が多い場合、スカラーサブクエリをOuter Joinに変更して、Hash Joinに変更すれば性能を改善することができる。
ただし、スカラーサブクエリを結合に変更する時は以下の制約事項を必ず守らなければならない。
・ 制約事項1 ： Outer Joinに変更する(常に結合に成功するならばOuter Joinで処理しなくても関係ない).
・ 制約事項2 ： スカラーサブクエリをOuter Joinに変更した後、結合連結カラムの値はUniqueでなければならない。
それでは、SCALAR_SQL[1]の[改善前SQL]と[改善後SQL]のSQLとそれぞれのトレース結果を確認してみよう。
[改善前SQL]
SQL制約事項:T1,T2,T3テーブルのC1カラムは全部Unique下だ。
SELECT t1.c1,
t1.c2,
t1.c3,
(SELECT t2.C3
FROM SCALAR_T2 T2
WHERE t2.c1 = t1.c1) AS t2_c3,
(SELECT t3.c3
FROM SCALAR_T3 T3
WHERE t3.c1 = t1.c1) AS t3_c3
FROM SCALAR_T1 T1
ORDER BY t1.c1, t1.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 33335 4.18 4.20 0 3003406 0 500000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 33337 4.18 4.20 0 3003406 0 500000
Rows Row Source Operation
------- ---------------------------------------------------
500000 TABLE ACCESS BY INDEX ROWID SCALAR_T2 (cr=1501114 pr=0 pw=0 time=1597402 us)
500000 INDEX RANGE SCAN SCALAR_T2_IDX_01 (cr=1001114 pr=0 pw=0 time=1036382 us)
500000 TABLE ACCESS BY INDEX ROWID SCALAR_T3 (cr=1501114 pr=0 pw=0 time=1640391 us)
500000 INDEX RANGE SCAN SCALAR_T3_IDX_01 (cr=1001114 pr=0 pw=0 time=1066722 us)
500000 SORT ORDER BY (cr=3003406 pr=0 pw=0 time=3775664 us)
500000 TABLE ACCESS FULL SCALAR_T1 (cr=1178 pr=0 pw=0 time=37 us)
[改善後SQL]
SELECT /*+ LEADING(T1) USE_HASH(T1 T2 T3) */
t1.c1,
t1.c2,
t1.c3,
t2.c3 AS t2_c3,
t3.c3 AS t3_c3
FROM SCALAR_T1 T1,
SCALAR_T2 T2,
SCALAR_T3 T3
WHERE t1.c1 = t2.c1(+)
AND t1.c1 = t3.c1(+)
ORDER BY t1.c1, t1.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 33335 1.49 1.43 0 3166 0 500000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 33337 1.49 1.44 0 3166 0 500000
Rows Row Source Operation
------- ---------------------------------------------------
500000 SORT ORDER BY (cr=3166 pr=0 pw=0 time=1048167 us)
500000 HASH JOIN RIGHT OUTER (cr=3166 pr=0 pw=0 time=945559 us)
500000 TABLE ACCESS FULL SCALAR_T3 (cr=1031 pr=0 pw=0 time=43 us)
500000 HASH JOIN OUTER (cr=2135 pr=0 pw=0 time=242954 us)
500000 TABLE ACCESS FULL SCALAR_T1 (cr=1178 pr=0 pw=0 time=32 us)
500000 TABLE ACCESS FULL SCALAR_T2 (cr=957 pr=0 pw=0 time=31 us)
——————————————————————————————————————————-Note ： 繰り返し実行されたスカラーサブクエリをOuter Joinに変更した後、Hash Joinで処理してインデックスに対する反復的な探索が除去されて性能が改善された。 ———————————————————————————————————————————-
③ 結合をスカラーサブクエリに変更しよう
今までの内容を見れば、あたかもSQL性能のためにはスカラーサブクエリを使ってはいけないように感じる。 それで以下のような質問に対してはどうだろうか？
Q : "常にスカラーサブクエリを使うのは不利なのか?"
A : "違う。 スカラーサブクエリを使うことが有利な時もある。"
Q : "そのような場合はいつか?"この質問に対する答えを探すために、まず以下の[改善前SQL]のSQL方式とトレース結果を先に見ることにしよう。
[改善前SQL]
SQL制約事項:T1,T2,T3テーブルのC1カラムは全部Unique下だ。
SELECT ROWNUM rnum,
X.*
FROM (
SELECT /*+ LEADING(T1) USE_NL(T1 T2 T3) */
t1.c1,
t1.c2,
t1.c3,
t2.c3 AS t2_c3,
t3.c3 AS t3_c3
FROM SCALAR_T1 T1,
SCALAR_T2 T2,
SCALAR_T3 T3
WHERE t1.c1 = t2.c1(+)
AND t1.c1 = t3.c1(+)
ORDER BY t1.c1, t1.c2
) X
WHERE ROWNUM <= 10 --->テーブル結合を全部処理した以後10件だけ抽出
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 2 4.41 4.44 0 3003406 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 4.41 4.45 0 3003406 0 10
Rows Row Source Operation
------- ---------------------------------------------------
10 COUNT STOPKEY (cr=3003406 pr=0 pw=0 time=4448209 us)
10 VIEW (cr=3003406 pr=0 pw=0 time=4448205 us)
10 SORT ORDER BY STOPKEY (cr=3003406 pr=0 pw=0 time=4448202 us)
500000 NESTED LOOPS OUTER (cr=3003406 pr=0 pw=0 time=4500101 us)
500000 NESTED LOOPS OUTER (cr=1502292 pr=0 pw=0 time=2000090 us)
500000 TABLE ACCESS FULL SCALAR_T1 (cr=1178 pr=0 pw=0 time=46 us)
500000 TABLE ACCESS BY INDEX ROWID SCALAR_T2 (cr=1501114 pr=0 pw=0 time=1730258)
500000 INDEX RANGE SCAN SCALAR_T2_IDX_01 (cr=1001114 pr=0 pw=0 time=959592 us)
500000 TABLE ACCESS BY INDEX ROWID SCALAR_T3 (cr=1501114 pr=0 pw=0 time=1929888)
500000 INDEX RANGE SCAN SCALAR_T3_IDX_01 (cr=1001114 pr=0 pw=0 time=888715 us)[改善前SQL]はSCALAR_T1,SCALAR_T2,SCALAR_T3テーブルの結合を処理したデータを整列して、ROWNUM <= 10条件で最終データを10件抽出する。 ところでSCALAR _T2とSCALAR_T3テーブルは抽出データに比べてとても多くのI/O処理量で実行されている。 その理由はSQLにUSE_NL(T1 T2 T3)ヒントがあってNested Loops Joinで実行されるが、最終10件を抽出する前に処理するデータがとても多く、反復的なインデックス スキャンによりI/O処理量が大量に発生したのである。 したがってNested Loops Join代わりにHash Joinで実行するように、ヒントをUSE_HASH(T1 T2 T3)に変更すればSQLの性能が改善されることを予想することができる。 次の[改善後SQL]のヒント適用SQLとトレース結果を確認してみよう。
[改善後SQL] 非効率的なUSE_NLヒントをUSE_HASHに変更
SELECT ROWNUM rnum, X.*
FROM ( SELECT /*+ LEADING(T1) USE_HASH(T1 T2 T3) */
t1.c1, t1.c2, t1.c3,
t2.c3 AS t2_c3,
t3.c3 AS t3_c3
FROM SCALAR_T1 T1,
SCALAR T2 T2,
SCALAR_T3 T3
WHERE t1.c1 = t2.c1(+)
AND t1.c1 = t3.c1(+)
ORDER BY t1.c1, t1.c2
) X WHERE ROWNUM <= 10
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 2 1.02 1.03 0 3166 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.02 1.03 0 3166 0 10
Rows Row Source Operation
------- ---------------------------------------------------
10 COUNT STOPKEY (cr=3166 pr=0 pw=0 time=1033875 us)
10 VIEW (cr=3166 pr=0 pw=0 time=1033869 us)
10 SORT ORDER BY STOPKEY (cr=3166 pr=0 pw=0 time=1033867 us)
500000 HASH JOIN RIGHT OUTER (cr=3166 pr=0 pw=0 time=916786 us)
500000 TABLE ACCESS FULL SCALAR_T3 (cr=1031 pr=0 pw=0 time=50 us)
500000 HASH JOIN OUTER (cr=2135 pr=0 pw=0 time=222808 us)
500000 TABLE ACCESS FULL SCALAR_T1 (cr=1178 pr=0 pw=0 time=34 us)
500000 TABLE ACCESS FULL SCALAR_T2 (cr=957 pr=0 pw=0 time=29 us)[改善前SQL]に比べて性能が非常に改善された。 ところが最終抽出データが10件に過ぎいが、まだSCALAR_T1 ～ SCALAR_T3テーブルの全体データを処理するので非効率が相変らず存在している。
反面、[改善後SQL]のSCALAR_T2とSCALAR_T3テーブルはOuter Joinで実行されて、結合連結カラムのデータがUniqueとなるので、SCALAR_T1で10件を抽出した後実行するように変更してもデータの完全性を影響なく、SQLの性能を改善すると予想される。
この場合[改善前SQL]の性能を改善するために、SCALAR_T2とSCALAR_T3を最終抽出データである10件に対してだけ実行されるように変更しなければならない。 このような改善方法は大きく二種類がある。 一番目の方法は、SCALAR_T1で10件抽出後、SCALAR_T2とSCALAR_T3テーブルをNested Loops Joinで実行するように変更することである。 そして二番目の方法は、最初の方法のNested Loops Join代わりにスカラーサブクエリでに変更することである。
[改善前SQL]に対する改善方法で、二つの方法ともに使えるが、以下の四種類条件を満足するSQLは結合よりスカラーサブクエリが効率的だと考える。
- 最終抽出件数が少なくて最終抽出件数回実行しても性能上問題がない。
- テーブル結合がOuter Joinで、結合連結カラム値の種類が少ない。
- WHERE節の条件もOuterで抽出データに影響を及ぼさない(例:T2.C2(+) = ‘A’).
- テーブルの実行位置を変えてNested Loops Joinに誘導するのが難しい場合(例:インライン ビュー内でグルーピングを実行するSQL).
もちろん、[改善前SQL]の性能改善方法で二つの方法ともに適用できるが、この章ではスカラーサブクエリーに対する内容を扱うので、SCALAR_T2,SCALAR_T3をスカラーサブクエリでに変更して性能を改善してみるようにする。
[最終改善後SQL] スカラーサブクエリ活用
SELECT ROWNUM rnum, X.*,
(SELECT t2.C3
FROM scalar_t2 t2
WHERE t2.c1 = x.c1) AS t2_c3,
(SELECT t3.C3
FROM scalar_t3 t3
WHERE t3.c1 = x.c1) AS t3_c3
FROM ( SELECT t1.c1, t1.c2, t1.c3
FROM scalar_t1 t1
ORDER BY t1.c1, t1.c2 ) X
WHERE ROWNUM <= 10
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 2 0.10 0.09 0 1242 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.10 0.09 0 1242 0 10
Rows Row Source Operation
------- ---------------------------------------------------
10 TABLE ACCESS BY INDEX ROWID SCALAR_T2 (cr=32 pr=0 pw=0 time=98 us)
10 INDEX RANGE SCAN SCALAR_T2_IDX_01 (cr=22 pr=0 pw=0 time=63 us)
10 TABLE ACCESS BY INDEX ROWID SCALAR_T3 (cr=32 pr=0 pw=0 time=50 us)
10 INDEX RANGE SCAN SCALAR_T3_IDX_01 (cr=22 pr=0 pw=0 time=38 us)
10 COUNT STOPKEY (cr=1178 pr=0 pw=0 time=97697 us)
10 VIEW (cr=1178 pr=0 pw=0 time=97693 us)
10 SORT ORDER BY STOPKEY (cr=1178 pr=0 pw=0 time=97690 us)
500000 TABLE ACCESS FULL SCALAR_T1 (cr=1178 pr=0 pw=0 time=41 us)
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーNote.スカラーサブクエリと結合の使用基準
[1]抽出データが少ない場合(オンライン プログラム)? スカラーサブクエリ使用OK!
→ 結合をスカラーサブクエリでに変更するのを考慮
[2]抽出データが多い場合(バッチプログラム)? スカラーサブクエリ使用NO!
→ スカラーサブクエリを結合に変更後Hash Joinで実行するのを考慮 ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
④ スカラーサブクエリの結合連結条件にはTable Aliasを必ず指定しよう
スカラーサブクエリは普通From句のテーブルとの結合条件が存在するが、この時、連結条件で使われた二つのカラム名が同じ場合ならば、Table Aliasを必ず明確に指定しなければならない。 なぜならAliasを指定しない場合、結合条件で使おうとしていた本来の意図とは違ってサブクエリ内で単独条件で実行されて、深刻な問題が発生する可能性があるためである。
例えば、スカラーサブクエリの結合条件が“id = id”であるが、Aliasを指定しないならば、サブクエリ内の自身のカラムと比較をすることになる。 したがって実際的には“id is not null”条件と解釈されることになる。 この場合スカラーサブクエリ内に“id = id”と結合接続条件のほか、他の効率的な条件がないならば、深刻な性能問題を誘発することになる。
実際の事例を通じて調べてみる。
[性能問題SQL]
SELECT b.seedseq ,
b.seednm ,
(
SELECT count(*)
FROM t_ime01mt a
WHERE a.seedseq = seedseq
AND a.status='8'
) AS cpncnt01
FROM t_ise01mt b
WHERE b.seedseq = :b1
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 2 0.11 0.14 1 6435 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.11 0.14 1 6435 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=6433 r=0 w=0 time=142491 us)
97153 TABLE ACCESS FULL T_IME01MT (cr=6433 r=0 w=0 time=134725 us)
1 TABLE ACCESS BY INDEX ROWID T_ISE01MT (cr=2 r=1 w=0 time=50 us)
1 INDEX UNIQUE SCAN XPKT_ISE01MT (cr=1 r=0 w=0 time=10 us)
[性能問題SQL]はスカラーサブクエリのT_IME01MTテーブルがFull Table Scanで実行されて非効率が発生している。 まずスカラーサブクエリがなぜFull Table Scanで実行されたのか点検してみよう。
Full Table Scanに影響を及ぼす要件
・ 発生要因[1]： 結合連結条件のデータ タイプ不一致
・ 発生要因[2]： 結合カラムに適切なインデックスが存在しない
発生要因[1]を点検してみれば、結合連結条件のカラムであるSEEDSEQはスカラーサブクエリのテーブルとFrom句のテーブルに存在し、データ タイプはNUMBERで同一である。 したがってデータ タイプ不一致によるFull Table Scanはない。
発生要因[2]を点検してみれば、スカラーサブクエリテーブルであるT_IME01MTにXIE2T_IME01MTインデックスが存在する。 したがって適切なインデックスが存在せず発生するFull Table Scanはない。
それならスカラーサブクエリのT_IME01MTテーブルはなぜ Full Table Scanで実行されたのだろうか? その理由はOptimizerが[性能問題SQL]を以下のように解釈したためである。
SELECT b.seedseq ,
b.seednm ,
(
SELECT count(*)
FROM t_ime01mt a
--WHERE a.seedseq = seedseq
WHERE a.seedseq = a.seedseq [内部的にはa.seedseq is not nullでだけ実行される]
AND a.status='8'
) AS cpncnt01
FROM t_ise01mt b
WHERE b.seedseq = :b1すなわちオプティマイザがスカラーサブクエリの結合条件であるSEEDSEQ値がFrom句のT_ISE01MTテーブルのSEEDSEQ値でないスカラーサブクエリT_IME01MTテーブルのSEEDSEQ値と解釈することによって、内部的にA.SEEDSEQ IS NOT NULLで処理され、これによってFull Table Scanで実行されたのである。 この性能問題はSQL自らの解釈が変わってデータ完全性まで毀損することがあるのでスカラーサブクエリ作成する時留意しなければならない。それでは、SQL作成意図に合うようにスカラーサブクエリの結合連結条件にTable Aliasを追加した後SQLの性能を確認してみよう。
[改善後SQL]
SELECT b.seedseq ,
b.seednm ,
(
SELECT count(*)
FROM t_ime01mt a
WHERE a.seedseq = b.seedseq ---> Table Alias追加
AND a.status='8'
) AS cpncnt01
FROM t_ise01mt b
WHERE b.seedseq = :b1
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 2 0.00 0.01 117 121 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 117 121 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=119 r=117 w=0 time=13423 us)
44741 INDEX RANGE SCAN XIE2T_IME01MT (cr=119 r=117 time=9931 us)
1 TABLE ACCESS BY INDEX ROWID T_ISE01MT (cr=2 r=0 w=0 time=18 us)
1 INDEX UNIQUE SCAN XPKT_ISE01MT (cr=1 r=0 w=0 time=7 us)前のトレース内容を見れば、スカラーサブクエリで処理したデータが多いが(SEEDSEQ,STATUSから抽出されるデータが多いこと) Index Range Scanで実行され、I/O発生量も119ブロックで正常なインデックス スキャンで処理されたということがわかる。 このようにスカラーサブクエリ作成時ささいな失敗(Table Alias脱落)が深刻な問題を起こすことがあるということに対して調べてみた。 したがってスカラーサブクエリを作成する時結合連結カラムに対して必ずTable Aliasを明示してSQLを作成するようにしなければならない。
今までスカラーサブクエリーによる性能問題と各性能問題に適合した改善法案に対して調べてみた。 スカラーサブクエリの性能問題はスカラーサブクエリの特性を理解して、SQLパターンに合うようにスカラーサブクエリを結合で、結合をスカラーサブクエリでに変更することになじんでこそ性能に最も有利な改善方法を導き出すことができた。 したがってこれらの記述した内容を理解して、多様な類型のSQLに応用してみるように願う。
いかがでしたでしょうか？今回で第３章「スカラーサブクエリの理解と効率的なSQLの作成」は終了となります。次回からは、第４章「WITH節理解と効率的なSQLの作成」になります。ご期待ください。
See you ^^
