Zenn
👏

【Oracle Database】SQLのWITH句の使用法と使いどころ

2021/02/01に公開
SQL
Oracle
with
tech

WITH句の役割

WITH句は標準SQL規格でも定義されています（SQL99以降）[1]。従って、SQL Server、PostgreSQL、MySQL(MariaDB)でも使用可能です。

SELECT文などDMLの冒頭で、一時的なVIEWもしくはPROCEDUREを定義するのに使用し、SQLの可読性を向上させるメリットがあります。使い方次第では処理速度の向上も期待できます。

Oracle Database（以下、Oracleと略）では、次のことができます。

  1. DMLと共にテンポラリなVIEWを定義する
  2. DMLと共にテンポラリなPROCEDURE, FUNCTIONを定義する

アプリ開発の場面では使用機会は少ないと思います（そもそも自由に作れるはずなので）。WITH句が活躍する場面は恐らく、データ抽出業務といったような、ユーザーがDBに対して参照権限だけを与えられている時だと思います。

参照権限しかない場合は当然、VIEWやストアドの作成ができません。そういう場合、WITH句を知っておくと便利です。

使い方

1. VIEWを作る

SQLは以下の通り。

WITH V_MANAGER AS (SELECT *
                     FROM EMP
                    WHERE JOB = 'MANAGER')  
SELECT *
  FROM V_MANAGER

結果は、こうなります。

テンポラリなVIEWの定義

ちなみに、Oracle Live SQLは無料で利用でき、Oracleでは有名なサンプルデータ（HRとかEMPとか）も簡単に用意できますので（※アカウントは無料で作成可能）、練習をしたい方はどうぞ。

・Oracle Live SQL

2. PROCEDURE, FUNCTIONを作る

FUNCTIONをWITH句で定義しておけば副問合せを書かなくてよいので、本体のSELECT文が簡潔に書けます。

ただ、副問合せでは一般的に言えることですが、元のSELECT文のレコード数が膨大な場合、処理速度の遅延が起こります。なので、使い所には注意が必要です。

FUNCITIONを使う場合は、以下のように書きます。

WITH
  --採用日が87年以降かどうかを判定するFUNCTION
  FUNCTION F_HIREDATE(DT IN DATE)
  RETURN VARCHAR2
  AS
  BEGIN
    RETURN CASE WHEN TO_CHAR(DT,'YY') > 86 THEN '○'
           ELSE '×'
           END;
  END;
  
 --本体のSELECT文
 SELECT ENAME    AS "名前"
       ,HIREDATE AS "採用日"
       ,F_HIREDATE(HIREDATE) AS "87年以降"
   FROM EMP

結果は、こんな感じ。

WITH句でFUNCTIONを定義

WITH使用の効果： 内部的なVIEW作成で応答速度の改善が期待できる

WITH句でVIEWを作成すると、DBサーバー側ではテンポラリなテーブルを作成する動きをします（オプティマイザが判断するので絶対に作成されるとは限りません）。

本体のDMLで複数回同じVIEWを参照する時とか、副問合せで1レコード毎に同一のVIEWを複数回参照する場合などで、パフォーマンスの向上が期待できます[2]。

以下はあくまで例ですが、本体のSELECT文は、V_EMPのVIEWを複数回参照しています。実行計画を見ると、内部的にテンポラリなテーブルが作成されていることが分かります。

WITH
  V_EMP AS (
  SELECT JOB, SUM(SAL) AS SAL_SUM
    FROM EMP
   GROUP BY JOB)
   
SELECT * FROM V_EMP WHERE JOB = 'MANAGER'
UNION ALL
SELECT * FROM V_EMP WHERE JOB = 'CLERK'

実行計画は、下の通り。
SYS_TEMP_0FD9D68B3_229C55が内部的に作成されるテンポラリテーブルです。

これは、副問合せの場合でも同じ効用が期待できます（SQLの効率については必ず実行計画を見て判断しましょうね）。

PROCEDURE, FUNCTION使用時はパフォーマンスに注意が必要

区分コードの変換などに使えますが、N+1問題と同様のことが起こります（副問合せも同様）。

コードの可読性は上がりますが性能面では非効率的な場合がありますので、注意が要ります。区分マスタを持ってくるだけであれば、結合処理のほうが効率が良いはずです。

ただ、SQLチューニングは実行計画を見ながら判断する必要があるので、その都度検証したほうが確実です。

他

.NETのEntyty Frameworkでは、WITH句はサポートしてなさそうです。
PythonのSQL Alchemyは、未確認です（マニュアルを見ると、サポートしてるっぽく見えるが）。

ただ、そもそも、ORMならインラインVIEWを作成することでWITHと同じことができる場合があるので、気にしなくてよいかもしれません。

脚注

  1. 「オブジェクト指向、Javaを取り入れた新しい業界標準「SQL99」詳細解説」(@IT)
    https://www.atmarkit.co.jp/fnetwork/tokusyuu/01sql99/sql99_1b.html ↩︎

  2. 津島博士のパフォーマンス講座　 第11回 良いSQLについて（2）
    https://blogs.oracle.com/otnjp/tsushima-hakushi-11 ↩︎

Discussion