[Oracle] 副問合せのファクタリング（WITH query_name句）

よく似た複数の表を同じ条件で抽出し、その結果を UNION や JOIN でくっつけたりするような複雑な SQL を書いていると、複数の箇所に全く同じサブクエリが登場することがあります。

SELECT 'S1' AS TNAME, PNO, SALES
  FROM S1
  WHERE PNO IN (SELECT PNO FROM PD WHERE STATUS = 'S')
UNION
SELECT 'S2' AS TNAME, PNO, SALES
  FROM S2
  WHERE PNO IN (SELECT PNO FROM PD WHERE STATUS = 'S');

このように記述すると全く同じであってもそれぞれ別個のサブクエリとして処理されてしまいあまり効率がよくありません。


こんなときは WITH query_name句を使ってサブクエリに名前をつけ、その名前を使ってサブクエリを参照することによって、サブクエリの実行が１回で済み、効率よくアクセスすることができます。

WITH P AS (SELECT PNO FROM PD WHERE STATUS = 'S')
SELECT 'S1' AS TNAME, S1.PNO, S1.SALES
  FROM S1
  WHERE S1.PNO IN (SELECT PNO FROM P)
UNION
SELECT 'S2' AS TNAME, S2.PNO, S2.SALES
  FROM S2
  WHERE S2.PNO IN (SELECT PNO FROM P);

WITH query_name句を使って名前をつけられたサブクエリの結果は内部的に一時表として保持され、その一時表が使い回されるようですね。

この程度の SQL だと大した効果はないかもしれませんが、もっと複雑な SQL だとパフォーマンスが数倍、数十倍に向上することもありますので、同じサブクエリが何回も出てくるなと思ったら試してみるべきかと思います。