Top > SQL 入門 > WITH

WITH 句 / 共通テーブル式 (Common Table Expression : SQL-99)

WITH 句は、副問い合わせに名前を付ける句である。この名前を付けられた副問い合わせは、「インラインビュー」 または 「一時表」として使用される。
同じインラインビューを何度も SQL の中に記述するよりも WITH 句で問い合わせに名前付けを行なうことで視認性が高まり、場合によりパフォーマンスが向上する可能性がある。

特に WITH では一時表を作ることがあるという特殊性(※)から実行計画に Merge Join Cartesian(直積)が出てビックリすることがあるかもしれない。

しかし、これは「大きな表と小さな表の結合」+「大きな表と別な小さな表の結合」の検索処理より
「小さな表同士の直積結合」+「直積した表と大きな表の結合」を選択した方が高速であるとオプティマイザが算出した結果なので涼しい顔で対応しよう。

(※) 非公式な MATERIALIZE、INLINE ヒント句をコストは高いが件数は常に少ないと把握しているブロックにつけて比較してみる。

WITH 句 の構文

構文 〜 Oracle 11g

WITH
  query_name AS ( select col [, col2 …] 〜 )

構文 Oracle 11g Release2 カラムの別名と再帰がサポートされている。

WITH
  query_name [ ( col1_alias [,col2_alias …] ) ] AS ( select col [, col2 …] 〜 )
  [
    SEARCH { DEPTH | BREADTH }  FIRST BY
           col1_alias [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, col2_alias …]
     SET ordering_col
  ] [
    CYCLE col1_alias [,col2_alias …]
     SET cycle_mark_c_alias TO cycle_value DEFAULT no_cycle_value
  ]

DEPTH : 深さ優先探索 | BREADTH : 幅優先探索 
cycle_value : サイクルしているときの値 | no_cycle_value : サイクルしていないときの値

WITH と インラインビュー

同じインラインビューを同一 SQL 内で多用した場合と WITH 句を使用した場合の違いは WITH 句では、その結果を一時表として保存される可能性がある点である。
言い換えると WITH の結果セットをもつワークテープルがあるかのように振舞う場合がありインラインビューよりパフォーマンスが良くなる可能性がある。

明示的に「SQL 問合せ結果キャッシュ」のヒントを利用することで、コストは高いが件数の少ない問い合わせの結果をオンメモリのキャッシュ化して効率よくすることも可能。こちらはインラインビューでも可能な方法である。

RESULT_CACHE ヒント Oracle 11g

WITH 
 mysubquery AS  {
   select /*+ RESULT_CACHE */ col from table where ...
 }
 …

WITH 使用例

  1. one という名前の副問い合わせを select 1 from dual で作る。
  2. ones_next という名前の副問い合わせを one の問い合わせを使って作る。(※)

(※) one と ones_next の記述順序は逆にできず 「ORA-32031: WITH句の問合せ名の参照が無効です。」 となる。

主問い合わせで ones_next を参照する

WITH
  one AS (
    select 1 one_col1, 10 one_col2 from dual
  ),
  ones_next AS (
    select one_col1 + 1 from one
   )
select * from ones_next;
 
ONE_COL1+1
----------
         2

簡単な再帰例 (≠ループ処理) / Oracle 11g Release2 以降

ones_next が 自分自身 ones_next を再帰的に参照する例

WITH ones_next (col) AS (
  select 1 from dual
  union all
  select 1 + col from ones_next where col < 5
)
select * from ones_next;
 
       COL
----------
         1
         2
         3
         4
         5

今年の曜日付きカレンダーを作る Oracle 12c

WITH calendar_dy (dd, dy) AS ( /* 'yyyy' の部分をすべて 'mm' にすれば今月分になる */
  select trunc(sysdate, 'yyyy'), to_char(trunc(sysdate, 'yyyy'), 'dy') from dual
  union all
  select dd+1,to_char(dd+1,'dy') from calendar_dy where trunc(dd,'yyyy')=trunc(dd+1,'yyyy')
)
select * from calendar_dy;
 
DD	   DY
---------- ------------
2017-01-01 日
2017-01-02 月
2017-01-03 火
.....

Oracle 11g において 上の記述は SYSDATE と DATE 型の データタイプコード が異なるためか
「ORA-01790: 式には対応する式と同じデータ型を持つ必要があります」 となる。
書き換え例

WITH dn(n) as (
  select 0 from dual union all
  select n + 1 from dn
  where n < add_months(trunc(sysdate, 'yyyy'), 12) - trunc(sysdate, 'yyyy') - 1
)
select trunc(sysdate, 'yyyy') + n from dn;

Oracle 10g で再帰を使うと「ORA-32033: 列はサポートされていません。」 とエラーになる。

 


副問い合わせ 関連

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