2つの副問い合わせの違い IN 条件か EXISTS 条件か

NOT IN (〜) と NOT EXISTS (〜) では多くの場合 NOT EXISTS の方がチューニングとして高速になるのは有名である。 しかし、IN ( <副問い合わせ> ) と EXISTS ( SELECT 〜) でお互いに置き換え可能な場合、果たしてどちらを使うのか? どちらでも同じなのか?

IN (<副問い合わせ>) の場合

〜2つの表の フィルタ処理〜

IN ( 〜 ) は副問い合わせ結果を親の問い合わせへの「入力データ」として組み込まれて実行計画される。 副問い合わせでの結果を親の問い合わせに取り込んで処理をすると考えることができる。

この過程が理解できると NOT IN ( <副問い合わせ> )が、 NOT EXISTS と比べて「入力データ」が巨大で、いかにコストが高い処理か想像することができる。
しかし、遅いと厳しく指摘される所はバージョンアップ毎に確実に改善されており、NOT IN であってもオプティマイザにより ANTI JOIN という方式で実行計画される場合もある。常に運用環境と同じ環境で実行計画を確認することが重要である。

EXISTS (<副問い合わせ>) の場合

〜 親表を駆動表としたセミ結合(半結合)処理 〜

EXISTS ( 〜 ) は相関副問い合わせであるという前提のもとに実行計画される。(必須ではない)
相関副問い合わせとは、親の問い合わせで処理された各レコードを副問い合わせにおいて評価するということを意味する。
ちなみに、EXISTS の中の 副問い合わせには、'SELECT * FROM 〜'を使用するより 'SELECT 1 FROM 〜' などの固定項目を使用した方が早い(CBOでは過去形の可能性が大)。ただこれを知らない人がみると ???...何だこれ? になるので要注意なうえ、既に時代遅れの技に近いかもしれない。 駆動表(外部表) は CBO によって動的に決定されるため条件により変動する。

まとめと指針

パフォーマンスチューニングにおいて、すべての副問い合わせを EXISTS に変更するという Tips は誤った認識である。ただし、EXIST による相関副問い合わせは オプティマイザ にとってチューニングしやすいのは パフォーマンス・チューニングマニュアルから確かな事である。

  • 副問い合わせの選択性が高い*1場合にはIN (<副問い合わせ>)
  • 親問い合わせの選択性が高く、副問い合わせのコストも低い場合には、EXISTS( <副問い合わせ> )
    を選択する。

多くの副問い合わせは最適化されると親表と結合する SQL に変換できる。CBO では IN でも EXISTS でも、その実行計画は親表と結合するプランに最適化されて 2つは近似もしくは全くの同一になることがある。Oracle 9i

その場合、再考して親表に結合する SQL に常に書き換えるべきかどうか?、、、私の答えは いいえ。
全体のパフォーマンスに貢献しない SQL の書き換えには、ほとんど存在価値がない。 逆に SQL の動作を理解するのに邪魔になることがあり、メンテナンス性を低下する可能性がある。 見易さ重視でオプティマイザが的確に副問合せをフラット化ができるのなら任せるスタンスである。また SQL では表現できない副問合せフラット化の実行計画も存在する。
たとえ熟練したプログラマであっても、のちに担当するメンテナンス要員に同じスキルレベルを保証できているかを最大の関心事にし、プロジェクトの全景を充分に理解した上で書き換えるか判断する。

ただし、要件を思慮のないまま言葉どおりに記述した副問い合わせやインラインビューの乱用は、別の意味で問題(プログラマが思考停止している)なので全く質が異なります。
さらに、別の意味でやっかいなのが×××が高速であると書かれているのを見て短絡的に書き換えしている状態。 その多くは実行計画の存在を知らず、見ず、そして読めずの状態でチューニング作業をしていると発生する。

Tips だけを寄せ集めて未検証で納得し、本質を後回しにすると結局遠回りすることになります。 チューニングする場合には必ず実行計画を理解することから始めます。それが最短コースです。(先を急ぎ過ぎている時期は誰にでもあると思います)
そして情報サイトの都合よくお膳立てされている高速化のトピックを鵜呑みにすると痛い目にあいます。特にこのサイトで書かれていること ;)

同様によくミスリーディングされている常識 ⇒ ROWID スキャンは常に最も高速か?

NOT IN と NOT EXISTS では結果も異なる場合がある

NOT IN と NOT EXISTS の結果の違い

副問い合わせ最適化 関連ヒント

  • STATISTICS_LEVEL 初期化パラメータ
    統計情報をモニタリングの自動化を制御する。
  • UNNEST / NO_UNNEST ヒント
    UNNEST ヒントは副問い合わせの中で使用し、親問い合わせに結合しようと試みる。(ネストされた副問合せのネスト解除)
    NO_UNNEST ヒントは、副問い合わせのネスト解除を許可しない。(EXISTS と IN の処理速度が顕著に現れるのでデバッグと検証用として扱う)
    SELECT * FROM table1 WHERE EXISTS ( SELECT /*+ NO_UNNEST */ * FROM table2 〜 )
    参考: Oracle 8、8i、9i、10g の違い
  • PUSH_SUBQ / NO_PUSH_SUBQ ヒント
    副問い合わせを実行計画の初期段階で評価する(PUSH_SUBQ ≒ IN)、評価を後回しにする(NO_PUSH_SUBQ ≒ EXISTS)。これらのヒントにより擬似的に EXISTS と IN の違いをより一層吸収することができる(未検証)。


2つの副問い合わせの違いの関連トピックス

日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ Oracle Web セミナー

*1 検索結果レコード数が少ない