SQLチューニング（第8回）「サブクエリ動作方式を理解すること」(6/8)

2025-06-09 05:53Oracle技術情報

今回は、「サブクエリを活用した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 )
実行方式	テーブルAでデータ抽出抽出されたデータSORT演算テーブルBでデータ抽出抽出されたデータSORT演算 2番と4番データ比較後最終データ抽出	テーブルAでデータ抽出 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の比較

テーブル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 ^-^

SQLチューニング（第8回）「サブクエリ動作方式を理解すること」(6/8)

2.1.3. サブクエリを活用したSQL性能改善

2.1.3.1. 非効率的なMINUSの代わりにNOT EXISTSを使おう

前の記事

次の記事

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします

人気記事ランキング

タグ一覧

SQLチューニング（第8回）「サブクエリ動作方式を理解すること」(6/8)

2.1.3. サブクエリを活用したSQL性能改善

2.1.3.1. 非効率的なMINUSの代わりにNOT EXISTSを使おう

前の記事

次の記事

他社に頼らず自社でデータベースを監視・運用をしませんか？MaxGaugeがサポートします

人気記事ランキング

タグ一覧

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします