Oracle Database 11g Release 2に関する10の重要なこと – askTom Live -
Point4：　Recursive Subquery Factoring　【再帰的副問合せのファクタリング】

（ 目次 ）

4.Recursive Subquery Factoring　【再帰的副問合せのファ クタリング】

Oracle Database 11g R2では、従来のCONNECT BY句による再帰的問合せに加え、ANSI SQLに準拠したWITH句を使用した再帰的問合せが使えるようになりました。
SQL初心者にとっては、CONNECT BY句よりも簡単だと思いますが、長年CONNECT BY句を使ってきた方は、慣れるまでやや戸惑うかもしれません。
しかし、WITH句は従来のCONNECT BY句以上に幅広い機能を備えています。

具体的な使い方を見ていきましょう。
ここでは、WITH句を使って従業員の上下関係を階層構造で表してみます。
この再帰的問合せは、常に2つの部分から成り立ち、それらの間にUNION ALLが入ります。
最初の部分は、CONNECT BY句でのSTART WITHの部分にあたるものです。これは階層の最上位を指します。
まず1行目のとおり、SELECTの代わりにWITH句を使い、データセット「emp_data」を指定します。
3行目では、mgrがNULLの従業員を探します。その結果、表empではKINGであることがわかります。これがレコード1です。
レコード1がわかったら、そのレコードを次の問合せに入力します。
そして、UNION ALL文を実行し、最初の問合せでわかったマネジャーの下で働く全従業員を探します。
しかし、これらすべてのレコードを副問合せに入力するわけではありません。2回目に副問合せを実行する際は、KINGの直属の部下の下で働く従業員、すなわち階層の3層目にいる人を探します。これをデータがなくなるまで繰り返すと、結果を出力します。1層目はKINGで、その下にいるのがBLAKE、CLARK、JONESです。この3人がわかったら、次の階層に進みます。これを繰り返すわけです。

ops$tkyte%ORA11GR2> with emp_data(ename,empno,mgr,l)
  2  as
  3  (select ename, empno, mgr, 1 lvl from emp where mgr is null
  4   union all
  5   select emp.ename, emp.empno, emp.mgr, ed.l+1
  6     from emp, emp_data ed
  7    where emp.mgr = ed.empno
  8  )
  9  SEARCH DEPTH FIRST BY ename SET order_by
 10  select l,
 11         lpad('*',2*l,'*')||ename nm
 12    from emp_data
 13   order by order_by
 14  /


L NM
---------- --------------------
         1 **KING
         2 ****BLAKE
         3 ******ALLEN
         3 ******JAMES
         3 ******MARTIN
         3 ******TURNER
         3 ******WARD
         2 ****CLARK
         3 ******MILLER
         2 ****JONES
         3 ******FORD
         4 ********SMITH
         3 ******SCOTT
         4 ********ADAMS

14 rows selected.

再帰WITH句は、データの生成にも使えます。
下記のコードをご覧ください。
まず、「data」というデータを指定します。
表dualから、最初の数値として1をUNION ALL文に入力します。すると、1は5未満なので1が加えられ、次の行として2が得られます。次の問合せでも、2は5未満なので1が加えられて3が得られます。これを繰り返して、5行のデータを生成します。
これでtimeseries値が得られ、2つの日付の間の日付がわかり、問合せのほかの部分に使うことができます。

ops$tkyte%ORA11GR2> with data(r)
  2  as
  3  (select 1 r from dual
  4   union all
  5   select r+1 from data where r < 5
  6  )
  7  select r, sysdate+r
  8    from data;

         R SYSDATE+R
---------- ---------
         1 08-OCT-09
         2 09-OCT-09
         3 10-OCT-09
         4 11-OCT-09
         5 12-OCT-09

もう1つ、おもしろい使い方をご紹介しましょう。
この問合せを使って、「数独」のパズルを解くこともできます。アントン・シファー（Anton Scheffer）という開発者が、「『数独』の空欄に入る数値をSQLで解いてみた」のだそうです（ http://technology.amis.nl/blog/6404/oracle-rdbms-11gr2-solving-a-sudoku-using-recursive-subquery-factoring参照）。たいへん興味深い使い方です。もちろん、皆さんが運用しているシステムで数独を解くような機会はまずないでしょうが、この問合せで何ができるかという観点ではおもしろいと思います。

この新しい機能についてもっと詳しく知りたい方は、「Oracle Database SQL言語リファレンス, 11gリリース2（11.2）」の19章「SQL文：SAVEPOINTからUPDATE」をお読みください。

▲ ページTOPに戻る

Thomas(Tom) Kyte (トム・カイト）
2000年にAsk Tomブログ( http://asktom.oracle.com ) を開設して以来10年にわたり、全世界のオラクル技術者のありとあらゆる質問に答え、データベース技術の活用を世に広めてきた世界的に有名なエバンジェリスト。