2021.06.23
SQLチューニング(第8回)「サブクエリ動作方式を理解すること」(6/8)
今回は、「サブクエリを活用したSQL性能改善」の一回目(全三回)です。それでは早速ない世に履いて行きましょう。
2.1.3. サブクエリを活用したSQL性能改善
2.1.3.1. 非効率的なMINUSの代わりにNOT EXISTSを使おう
どちらか一つの集合にあるデータのうち他の集合に存在しないデータ(以下 差集合)を抽出するための方法でMINUSを使用することができる。
MINUSは二つの集合間の差集合を抽出する時、SQL作成が容易で、可読性も優れておりしばしば使われる。しかしMINUSを使ったSQLはたびたび予期しない性能問題が発生することがある。多分読者の方々にも、すでにMINUSを利用して差集合を抽出するようにSQLを作成した後、性能問題によって苦労した経験がある方がいらっしゃると思う。
MINUSを使ったSQLが性能問題が発生する場合、SQLを変更しないで、性能を改善するのが容易でない。このような場合MINUS代わりにNOT EXISTSに変えたSQLで再作成して性能を改善することが可能である。
MINUSをNOT EXISTSに変えて性能を改善する状況と方法を調べてみるのに先立ち二つの演算を比較した以下の例を先に見てみることにする。
比較対象 | MINUS | NOT EXISTS |
実行SQL | SELECT … FROM A MINUS SELECT … FROM B | SELECT … FROM A WHERE NOT EXISTS ( SELECT … FROM B WHERE B.XX = A.XX ) |
実行方式 | 1. テーブルAでデータ抽出 2. 抽出されたデータSORT演算 3. テーブルBでデータ抽出 4. 抽出されたデータSORT演算 5. 2番と4番データ比較後最終データ抽出 | 1. テーブルAでデータ抽出 2. 1番で抽出したデータと サブクエリテーブルBデータと 比較後最終データ抽出 |
実行順序 | 固定(A → B) | 変更可能(A → B or B → A) |
テーブル実行方式 | テーブルAと関係なく別にデータ抽出後 SORT演算実行 | テーブルAの抽出データを利用した インデックス スキャン可能 (結合連結キー) および別途実行も可能 |
SQL性能 | 不利 1. テーブルA,Bで抽出したデータ をSORT演算時性能低下 2. テーブルAで抽出したデータが少なく、 テーブルBに何の条件もない場合 Full Table Scanで処理しなければならなく、 抽出されたデータをSORT演算しなければならない。 | 有利 適切なインデックス スキャンを実行したり Full Table Scanを実行するなど SQL性能に最も効率的な方法 を選択して適用が可能。 |
テーブル2-2を見れば、差集合を抽出するためにMINUS演算よりNOT EXISTSを使ってSQLを作成することが性能的な側面で効率的な方法だということがわかる。
理解を助けるためにテストを通じて調べてみる。
抽出対象になるデータ集合のWhere節に効率的な定数条件があって、比較対象である他の集合にはWhere節がない場合に、SQLをそれぞれMINUSとNOT EXISTSを使って作成した後、二つのSQL間における性能差を確認する。
先にMINUSで実行したトレース結果を確認する。
テスト[1]. MINUS
var b1 varchar2(10)
var b2 number
var b3 number
exec :b1 := 'A'
exec :b2 := 200000
exec :b3 := 300000
SELECT c1, c2, c3
FROM SUBQUERY_T2
WHERE c2 = :b1
AND c1 >= :b2
AND c1 <= :b3
MINUS
SELECT c4, c5, c6
FROM SUBQUERY_T1
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 13.01 16.09 15273 56207 0 1923
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 132 13.01 16.09 15273 56207 0 1923
Rows Row Source OPERATION
------- ---------------------------------------------------
1923 MINUS (cr=56207 pr=15273 pw=0 time=16095391 us)
3846 SORT UNIQUE (cr=247 pr=0 pw=0 time=7290 us)
3846 FILTER (cr=247 pr=0 pw=0 time=3906 us)
3846 TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=247 pr=0 …)
3846 INDEX RANGE SCAN SUBQUERY_T2_IDX_01 (cr=12 pr=0 …)
250000 SORT UNIQUE (cr=55960 pr=15273 pw=0 time=16054420 us)
16000000 TABLE ACCESS FULL SUBQUERY_T1 (cr=55960 pr=15273 pw=0 time=15833 us)
トレース結果を見れば、比較対象であるSUBQUERY_T1には照会条件がなくFull Table Scanで実行し、すべてのデータ(1,600万件)に対する整列作業まで実行している。 テスト[1]ではテーブル サイズが小さくて所要時間がそんなに長くかからなかったが、もし該当テーブルが非常に大きいサイズで色々なセッションが同時に実行するSQLならば深刻な性能問題が発生することになるだろう。
次にNOT EXISTSを使ったSQLの性能を確認する。
テスト[2]. NOT EXISTS
SELECT c1, c2, c3
FROM SUBQUERY_T2 T2
WHERE c2 = :b1
AND c1 >= :b2
AND c1 <= :b3
AND NOT EXISTS ( SELECT /*+ UNNEST NL_AJ */
'x'
FROM SUBQUERY_T1 T1
WHERE t1.c4 = t2.c1
AND t1.c5 = t2.c2
AND t1.c6 = t2.c3 )
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.03 0.03 0 10241 0 1923
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 132 0.03 0.03 0 10241 0 1923
Rows Row Source OPERATION
------- ---------------------------------------------------
1923 FILTER (cr=10241 pr=0 pw=0 time=30102 us)
1923 NESTED LOOPS ANTI (cr=10241 pr=0 pw=0 time=28175 us)
3846 TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=495 pr=0 pw=0 time=3888 us)
3846 INDEX RANGE SCAN SUBQUERY_T2_IDX_01 (cr=140 pr=0 …)
1923 TABLE ACCESS BY INDEX ROWID SUBQUERY_T1 (cr=9746 pr=0 …)
1923 INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=7823 pr=0 …)
NOT EXISTSを使ったSQLのトレース結果を見れば、Full Table Scanはインデックス スキャンで実行方式が変更され、また整列作業が除去され、MINUSを使った時より性能が大きく改善されたのを確認することができる。
MINUSを使ったSQLで発生する性能問題を要約すれば大きく二種類に分けられる。 一つ目は比較対象テーブルでデータ抽出する方式が非効率(Full Table Scan)なのにもかかわらず、照会条件がなくて改善をするのが難しいという点、二つ目は比較対象テーブルとして多くのデータが抽出されれば整列作業が過多に発生するということである。
テスト[2]の例題をもとに、比較対象テーブルに対するWhere節に条件がなくてFull Table Scanを実行して性能問題が発生したり、比較対象テーブルで抽出されるデータが多くて整列作業に対する負荷が過度な場合、MINUSをNOT EXISTSに変更して改善しなければならない。
ところでMINUSをNOT EXISTSに変更したSQLは、抽出されたデータ総件数がそれぞれ違う結果を抽出することができる。 MINUSを使うSQLの実行計画中SORT UNIQUEオペレーションは、MINUS演算でUnique値を抽出するということを意味する。 反面、NOT EXISTSを使ったSQLの実行計画にはSORT UNIQUEオペレーションが存在しない。 したがってSelect節に羅列されたカラムの組み合わせがUniqueでないならば、NOT EXISTSを利用したSQLは重複した値が抽出される可能性がある。 このような理由でMINUSを無条件NOT EXISTSに変更した場合、性能は改善されたがデータ整合性が毀損される現象が発生しうる危険が存在する。
一般的にMINUS使用時ほとんどのSelect節にPrimary Keyカラム(Uniqueカラム)が含まれていて、NOT EXISTSに変える場合、データが間違って抽出される問題が発生しない。 テストを進めたSQLもPrimary KeyカラムがSelect節に含まれている。 それでDISTINCT処理をしないで、単純にNOT EXISTSに変更しても整合性が毀損されない。 しかし、これは一般的な場合でありSelect節に羅列されたカラム組合がUniqueしない状況がいくらでも存在することができる。 したがってデータ整合性のためにSelect節に羅列されたカラムの組み合わせがUniqueであることを必ずチェックしなければならない。
ここでNOT EXISTSに毎回DISTINCT処理をすれば良いのではないか、なぜ常にチェックしなければならないのか? という疑問を持つ読者の方もあることと考える。しかし、無条件DISTINCTを使うのは、さらに整列作業が発生するので性能上有利でない。 これに対し対して以下で再度説明する。
まず、MINUSとNOT EXISTSを使った場合に総抽出件数が変わる状況に対して下のテストを通じて調べてみる。
SELECT *
FROM ( SELECT 1 no
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL )
MINUS
SELECT *
FROM ( SELECT 1 no
FROM DUAL ) ;
NO
---------
2
MINUS演算は重複値が除去されるので2が2件抽出されないで1件だけ抽出された。しかしMINUSをNOT EXISTSに変更して処理すればその結果はどうなろうか? 直接実行してみる。
SELECT x.*
FROM ( SELECT 1 no
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL ) x
WHERE NOT EXISTS ( SELECT 'x'
FROM (
SELECT 1 no
FROM DUAL
) y
WHERE x.no = y.no ) ;
NO
---------
2
2
同じデータが2件が抽出されて結果が変わることになる。 したがって結果値を同一にするにはDISTINCTを付与しなければならない。
SELECT DISTINCT
x.*
FROM ( SELECT 1 no
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL ) x
WHERE NOT EXISTS ( SELECT 'x'
FROM ( SELECT 1 no
FROM DUAL ) y
WHERE x.no = y.no ) ;
NO
----------
2
前でMINUSで作成されたSQLをNOT EXISTSで作成する場合、常にDISTINCTを使うのは性能上有利でないと話した。 もう少し詳しい理由を考察する。
SQLにDISTINCTを使えば、Oracleは重複した値を除去するために内部的に整列作業を実行することになる。 ところでSQLの抽出件数が少ない場合には整列作業に対する負担が少ないが、多くの場合ならば整列作業に対する負荷も大きくなる。 したがって常にDISTINCTを使うのは性能上有利でない。
したがってMINUS代わりにNOT EXISTSを使ってSQLを作成する場合DISTINCTを無条件適用するよりは、Select節に羅列されたカラムの組み合わせがUniqueであることをまず判断しなければならない。 そして必ずUniqueでない場合にだけDISTINCTを使うことにしよう。
今回は、ここまでになります。MINUSとNOT EXISTSの話、いかがでしたでしょうか?次回は、結合とサブクエリについてです。ご期待ください。See you ^-^