FILTER（相関サブクエリ）

何を表すか: EXISTS / NOT EXISTS / IN （サブクエリ形式）を SEMI/ANTI JOIN に変換できなかった場合に、外側クエリの各行に対してサブクエリをループ実行する操作。ループが発生するため行数が多いと非常に遅い。

  • 読み方: FILTER ノードの下に 2 つの子がある場合、上の子が外側（ループ行数）、下の子がループ内で実行されるサブクエリ
  • 問題パターン: 外側の Rows が多い（数万以上）× サブクエリが毎回実行 = N×M の処理量。10 万行で 10 万回サブクエリ実行が発生することも
  • 改善策: EXISTS を JOIN（HASH JOIN SEMI / NESTED LOOPS SEMI）に書き換えるか、HASH JOIN に変換されるようにオプティマイザが判断できる統計情報を整える
  • 閾値・注意点: 内側サブクエリの結果がバインド変数に依存する場合はキャッシュされることがある（filter predicate に記載）
  • 関連: Predicate Information の filter 条件 / ALLSTATS の Starts 列（ループ回数）

VIEW（インラインビュー・マージ・非マージ）

何を表すか: インラインビュー（FROM 句のサブクエリ）または名前付きビューをマテリアライズするか、外部クエリとマージするかを示す。

表示パターン意味注意点
VIEW（名前あり）ビューが非マージ（マテリアライズ）されて実行されるビューの結果が一時的に生成されてから外側フィルタが適用。フィルタが内側に押し込まれない可能性あり
（Operation 内に VIEW がない）ビューが外部クエリにマージされて最適化された通常は望ましい状態。結合条件や WHERE 条件が内側に適用される
VIEW（PUSHED PREDICATE）外部のフィルタ条件がビュー内部に Push Down された効率的。大幅な行絞り込みが内側で発生する

マージと非マージの判断

  • GROUP BY・DISTINCT・集計関数・ROWNUM を含むビューはマージ不可（非マージ固定）
  • マージ可能でも統計情報の誤りで非マージが選ばれることがある → ヒント MERGE / NO_MERGE で強制可能

COUNT STOPKEY / ROWNUM フィルタ

COUNT STOPKEY: WHERE ROWNUM <= N 等の上限指定で N 行取得後に処理を打ち切る最適化。Plan Table に COUNT(STOPKEY) として出現。

  • 読み方: STOPKEY があれば全件スキャン不要。ページネーションクエリで適切にヒットしているか確認
  • 問題パターン: ORDER BY と ROWNUM を組み合わせたページネーションで SORT ORDER BY が STOPKEY なしになっている → ORDER BY がソート全件完了後に ROWNUM でフィルタするため遅い。正しい書き方（インラインビューで先に ORDER BY してから外側で ROWNUM）を確認

スカラーサブクエリのキャッシュと問題

SELECT 句に書くスカラーサブクエリ（SELECT (SELECT MAX(x) FROM t2 WHERE t2.id = t1.id) FROM t1）は実行計画に明示的なノードとして現れないことが多いですが、内部的に行ごとに実行されます。

  • キャッシュ機能: Oracle は同一パラメータのスカラーサブクエリの結果をセッションレベルでキャッシュする（最大 256 エントリ）。キャッシュ効率が高い場合は許容範囲
  • 問題パターン: 外側の行数が多く、かつ引数のカーディナリティが高い（キャッシュが効かない）場合に N 回サブクエリ実行が発生
  • 改善策: スカラーサブクエリを FROM 句のインラインビューに移動し、HASH JOIN で結合する形に書き換える

CONNECT BY（階層クエリ）

何を表すか: CONNECT BY PRIOR を使った階層構造の展開。ツリー構造（組織図・部品表・カテゴリ階層等）を再帰的に辿る操作。

  • 読み方: CONNECT BY （NO FILTERING WITH SW (UNIQUE)） などのバリアントが出現。階層の深さと各レベルのデータ量が処理量を決める
  • 問題パターン: 階層が深い（数十段以上）、または各ノードの子が多数（ファンアウトが大きい）場合に爆発的に行数が増加
  • 閾値・注意点: CONNECT BY NOCYCLE なしで循環参照があると無限ループ（ORA-01436）
  • 関連: 12c 以降は再帰 WITH 句（WITH … AS (… UNION ALL …)）で CONNECT BY を代替可能

WITH 句（TEMP TABLE TRANSFORMATION）

WITH 句（CTE: Common Table Expression）は Oracle オプティマイザがインラインビューとしてマージするか、一時テーブルとしてマテリアライズするかを選択します。

  • TEMP TABLE TRANSFORMATION: WITH 句の結果を一時テーブルに書き出して再利用する最適化。計画内に LOAD AS SELECT や TEMP TABLE TRANSFORMATION が出現
  • 正常パターン: 同じ CTE が複数箇所で参照される場合にマテリアライズして再利用コストを削減
  • 問題パターン: 1 回しか参照されない CTE がマテリアライズされると一時テーブルへの書込み + 読込みのオーバーヘッドが発生
  • ヒント: /*+ MATERIALIZE */ または /*+ INLINE */ で強制可能