SQLチューニング（第４回）「サブクエリと性能問題の理解」(2/8)

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

SQLチューニング第４回は、「サブクエリと性能問題理解」に関しての8回シリーズの第２回目になります。今回は、サブクエリーの典型的な２パターンに関する説明です。

2.1.1 サブクエリーに対する基本内容を理解すること

2.1.1.2 サブクエリの使用パターンに対して調べてみよう

サブクエリは場合により多様な形式で作成できるので、この中ではすべてのパターンを網羅するよりは、しばしば使われる二種類のパターンだけを用いてサブクエリの概念に対して説明する。

◎ 使用パターン[1]

SELECT  *
  FROM  emp
 WHERE  sal > ( SELECT AVG( sal )
                       FROM emp )

◎ 使用パターン[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ヒントにブロック名を指定）。

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処理)したということがわかる。

いかがでしたでしょうか？次回は、サブクエリの動作方式の１回目「FILTER動作方式」についてです。ご期待ください。それでは、See you　^-^

SQLチューニング（第４回）「サブクエリと性能問題の理解」(2/8)

2.1.1 サブクエリーに対する基本内容を理解すること

2.1.1.2 サブクエリの使用パターンに対して調べてみよう

◎ 使用パターン[1]

◎ 使用パターン[2]

◎ テスト[1]. サブクエリの基本的な特性調べてみること

◎ テスト[2].サブクエリを結合（ビュー）で実行されるように誘導

前の記事

次の記事

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

人気記事ランキング

タグ一覧

SQLチューニング（第４回）「サブクエリと性能問題の理解」(2/8)

2.1.1 サブクエリーに対する基本内容を理解すること

2.1.1.2 サブクエリの使用パターンに対して調べてみよう

◎ 使用パターン[1]

◎ 使用パターン[2]

◎ テスト[1]. サブクエリの基本的な特性調べてみること

◎ テスト[2].サブクエリを結合（ビュー）で実行されるように誘導

前の記事

次の記事

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

人気記事ランキング

タグ一覧

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