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…
