Oracle 11g Release 2, clause WITH récursive et Fibonacci

Oracle 11.2 introduit la possibilité de créer une récursion dans la clause WITH d’un ordre SELECT. D’aucuns diront qu’Oracle ne fait qu’implémenter le standard pour éviter la syntaxe propriétaire avec sa clause « CONNECT ». En fait cette fonctionnalité permet également de résoudre des problèmes plus simplement. Cet article illustre cette nouveauté en proposant une solution pour créer ce qui est sans doute la plus célèbre des suites: la suite de Fibonacci.

Pour commencer…

Pour bien commencer, consultez la documentation et en particulier les sections qui suivent:

• Pour une description de cette nouvelle fonctionnalité: « Oracle® Database SQL Language Reference 11g Release 2 (11.2) subquery_factoring_clause et la description associée« 
• Pour un ensemble d’exemples: « Recursive Subquery Factoring: Examples« 

Comme vous pouvez le constater le premier exemple qui vient à l’esprit est le parcours d’arbre. Autrement dit, une requête que vous écrivez comme ceci jusqu’en 11.1:

col text format a40
col mgr format 9999

select rpad(' ',2*(level-1))||empno||': '||ename text,
       mgr
  from scott.emp
       connect by prior empno=mgr
       start with job='PRESIDENT'
 order siblings by ename;

TEXT                          MGR
----------------------------- -----
7839: KING
  7698: BLAKE                 7839
    7499: ALLEN               7698
    7900: JAMES               7698
    7654: MARTIN              7698
    7844: TURNER              7698
    7521: WARD                7698
  7782: CLARK                 7839
    7934: MILLER              7782
  7566: JONES                 7839
    7902: FORD                7566
      7369: SMITH             7902
    7788: SCOTT               7566
      7876: ADAMS             7788

Execution Plan
----------------------------------------------------------
Plan hash value: 763482334

-----------------------------------------------------------------------------
| Id  | Operation                               | Name | Rows  | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |      |    14 |   4    (25)|
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|      |       |            |
|   2 |   TABLE ACCESS FULL                     | EMP  |    14 |   3     (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("MGR"=PRIOR "EMPNO")
       filter("JOB"='PRESIDENT')

En 11.2, vous pouvez donc obtenir le même résultat avec une autre syntaxe ET un plan d’exécution différent:

col text format a40
col mgr format 9999

with empl (empno, ename, xlevel, mgr) as
  (select empno, ename, 1, mgr
     from scott.emp
    where job='PRESIDENT'
   union all
   select e.empno, e.ename, empl.xlevel+1, e.mgr
     from scott.emp e, empl
    where e.mgr=empl.empno)
  search depth first by ename set ord
select rpad(' ',2*xlevel)||empno||': '||ename text,
       mgr
  from empl;

TEXT                       MGR
-------------------------  -----
  7839: KING
    7698: BLAKE             7839
      7499: ALLEN           7698
      7900: JAMES           7698
      7654: MARTIN          7698
      7844: TURNER          7698
      7521: WARD            7698
    7782: CLARK             7839
      7934: MILLER          7782
    7566: JONES             7839
      7902: FORD            7566
    7369: SMITH             7902
      7788: SCOTT           7566
    7876: ADAMS             7788

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3907725112

-----------------------------------------------------------------------------
| Id  | Operation                               | Name | Rows  | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |      |    25 |    8   (25)|
|   1 |  VIEW                                   |      |    25 |    8   (25)|
|   2 |   UNION ALL (RECURSIVE WITH) DEPTH FIRST|      |       |            |
|*  3 |    TABLE ACCESS FULL                    | EMP  |     3 |    3    (0)|
|*  4 |    HASH JOIN                            |      |    22 |    4   (25)|
|   5 |     RECURSIVE WITH PUMP                 |      |       |            |
|*  6 |     TABLE ACCESS FULL                   | EMP  |    13 |    3    (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("JOB"='PRESIDENT')
   4 - access("E"."MGR"="EMPL"."EMPNO")
   6 - filter("E"."MGR" IS NOT NULL)

Implémenter une suite géométrique

Mais sortons un peu de la documentation en implémentant une suite géométrique. Pour éviter les erreurs « ORA-01426: numeric overflow » ou « ORA-32044: cycle detected while executing recursive WITH query », vous pouvez limiter le nombre de récursions avec une colonne « depth » comme ci-dessous :

col val2 format 9999

with x (val2, depth) as
  (select 1, 1
     from dual
   union all
   select 2*val2, depth+1
     from x where
    depth<10)
select val2 from x;

 VAL2
-----
    1
    2
    4
    8
   16
   32
   64
  128
  256
  512

Implémenter la suite de Fibonacci

Vous pouvez également utiliser des récursions plus complexes qu’une simple multiplication d’une colonne. L’exemple ci-dessous implémente, par exemple, la suite de Fibonacci :

col val1 format 9999

with x (val1, val2, depth) as
  (select 1, 1, 1
     from dual
   union all
   select val1+val2, val1, depth+1
     from x
    where depth<10)
select val1 from x;

VAL1
----
   1
   2
   3
   5
   8
  13
  21
  34
  55
  89

Cette nouvelle extension syntaxique permet donc d’exprimer différemment certaines requêtes et en l’occurrence de référencer la ligne précédente simplement. Reste à apprécier l’intérêt pour vos applications; si vous voulez en savoir plus, lisez le très bon article de Luca Jellema sur le sujet. Et restez branché sur le blog pour d’autres exemples des nouveauté d’Oracle 11.2 dans les jours qui viennent. D’ici là, abonnez-vous au blog Easyteam par E-Mail, Twitter et RSS et soyez patient…