Par Michel SALAIS
Introduction
Lors de la création d’une fonction stockée dans Oracle, il est possible de la désigner comme DETERMINISTIC. Je vais utiliser le terme de fonctions déterministes afin de désigner ces fonctions. Nous pouvons résumer la définition d’une fonction déterministe, d’après la documentation d’Oracle, par les points suivants :
- La fonction n’a pas d’effets de bord.
- La fonction retourne la même valeur lorsqu’elle reçoit les mêmes valeurs d’arguments en deux exécutions différentes.
- La fonction ne soulève pas d’exception non traitée.
Mais la documentation dit aussi que si une fonction stockée définie comme déterministe, viole ces règles alors les conséquences ne sont pas prévisibles. Essayons donc de voir l’intérêt de ces fonctions et de sonder leur comportement. Les tests que je vais montrer ici se sont déroulés sous une version 12.2.
Ci-après la définition de la fonction utilisée :
create or replace function f_deterministic (n number) return date deterministic as begin dbms_lock.sleep(1); return sysdate + n; end f_deterministic; /
Notons d’abord que cette fonction déclarée comme déterministe ne l’est pas vraiment ! Son résultat dépendra du moment de l’exécution. Afin de pouvoir repérer de multiples exécutions de la fonction, l’appel à la procédure dbms_lock.sleep(1) force la fonction à s’endormir une seconde avant de faire appel à la fonction SYSDATE. Inutile de signaler que l’utilisateur doit avoir le droit d’exécuter le package DBMS_LOCK.
Fonctions déterministes et contrôle des mises à jour
Le déroulement de l’exécution montre qu’Oracle ne contrôle pas la conformité du code de la fonction avec sa déclaration comme DETERMINISTIC. Ceci n’est fait ni pendant la compilation, ni pendant l’exécution.
SQL> drop table t1 cascade constraints; drop table t1 cascade constraints * ERREUR à la ligne 1 : ORA-00942: Table ou vue inexistante SQL> create table t1 (c1 number); Table créée. SQL> SQL> create or replace function f_deterministic (n number) 2 return date 3 deterministic 4 as 5 begin 6 insert into t1 values (1); 7 return sysdate + n; 8 end f_deterministic; 9 / Fonction créée. SQL> SQL> declare 2 v_date date; 3 begin 4 v_date := f_deterministic(1); 5 end; 6 / Procédure PL/SQL terminée avec succès. SQL> select * from t1; C1 ---------- 1
Comportement d’une fonction déterministe dans une instruction SELECT
Dans le script suivant, les deux fonctions f_deterministic() et f_non_deterministic() sont identiques en tous points, sauf un : la fonction f_deterministic(), comme l’indique son nom, est déclarée comme DETERMINISTIC – tandis que la fonction f_non_deterministic() n’est pas déclarée telle.
SQL> alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss'; Session modifiée. SQL> SQL> /* L'exécuteur doit avoir le droit EXECUTE sur le package DBMS_LOCK directement */ SQL> create or replace function f_deterministic (n number) 2 return date 3 deterministic 4 as 5 begin 6 dbms_lock.sleep(1); 7 return sysdate + n; 8 end f_deterministic; 9 / Fonction créée. SQL> SQL> create or replace function f_non_deterministic (n number) 2 return date 3 as 4 begin 5 dbms_lock.sleep(1); 6 return sysdate + n; 7 end f_non_deterministic; 8 / Fonction créée. SQL> select f_deterministic(1), f_deterministic(1), f_non_deterministic(1), f_non_deterministic(1) 2 from dual; F_DETERMINISTIC(1) F_DETERMINISTIC(1) F_NON_DETERMINISTIC F_NON_DETERMINISTIC ------------------- ------------------- ------------------- ------------------- 19/06/2017 15:31:10 19/06/2017 15:31:11 19/06/2017 15:31:12 19/06/2017 15:31:13
SQL> select f_deterministic(1), f_deterministic(1), f_non_deterministic(1) 2 from all_tables 3 where rownum < 3; F_DETERMINISTIC(1) F_DETERMINISTIC(1) F_NON_DETERMINISTIC ------------------- ------------------- ------------------- 19/06/2017 21:37:50 19/06/2017 21:37:51 19/06/2017 21:37:52 19/06/2017 21:37:50 19/06/2017 21:37:51 19/06/2017 21:37:53
SQL>
SQL> with t as
2 (
3 select mod(rownum - 1, 3) numero
4 from dual
5 connect by rownum <= 9
6 )
7 select numero, f_deterministic(numero), f_non_deterministic(numero)
8 from t;
NUMERO F_DETERMINISTIC(NUM F_NON_DETERMINISTIC
---------- ------------------- -------------------
0 18/06/2017 17:30:18 18/06/2017 17:30:19
1 19/06/2017 17:30:20 19/06/2017 17:30:21
2 20/06/2017 17:30:22 20/06/2017 17:30:23
0 18/06/2017 17:30:24 18/06/2017 17:30:25
1 19/06/2017 17:30:20 19/06/2017 17:30:26
2 20/06/2017 17:30:22 20/06/2017 17:30:27
0 18/06/2017 17:30:24 18/06/2017 17:30:28
1 19/06/2017 17:30:20 19/06/2017 17:30:29
2 20/06/2017 17:30:22 20/06/2017 17:30:30
9 lignes sélectionnées.
Regardons d’abord ce passage:
SQL> select f_deterministic(1), f_deterministic(1), f_non_deterministic(1), f_non_deterministic(1) 2 from dual; F_DETERMINISTIC(1) F_DETERMINISTIC(1) F_NON_DETERMINISTIC F_NON_DETERMINISTIC ------------------- ------------------- ------------------- ------------------- 19/06/2017 15:31:10 19/06/2017 15:31:11 19/06/2017 15:31:12 19/06/2017 15:31:13 SQL> select f_deterministic(1), f_deterministic(1), f_non_deterministic(1) 2 from all_tables 3 where rownum < 3; F_DETERMINISTIC(1) F_DETERMINISTIC(1) F_NON_DETERMINISTIC ------------------- ------------------- ------------------- 19/06/2017 21:37:50 19/06/2017 21:37:51 19/06/2017 21:37:52 19/06/2017 21:37:50 19/06/2017 21:37:51 19/06/2017 21:37:53
Les résultats montrent que la fonction déterministe évoquée à plusieurs reprises dans la clause SELECT avec la même valeur constante est exécutée à chaque fois !
Par contre, un appel à cette même fonction déterministe avec une valeur constante durant le parcours d’une table est calculé une fois pour toutes. Comme attendu, la fonction non déterministe est exécutée une fois à chaque ligne. Une question vient à l’esprit : que se passe-t-il si la fonction utilise une colonne comme argument, et que cette colonne contient la même valeur pour plusieurs lignes ?
La réponse est bien dans notre exemple exécuté ci-dessus, dont on reprend la partie concernée ici :
SQL> with t as 2 ( 3 select mod(rownum - 1, 3) numero 4 from dual 5 connect by rownum <= 9 6 ) 7 select numero, f_deterministic(numero), f_non_deterministic(numero) 8 from t; NUMERO F_DETERMINISTIC(NUM F_NON_DETERMINISTIC ---------- ------------------- ------------------- 0 18/06/2017 17:30:18 18/06/2017 17:30:19 1 19/06/2017 17:30:20 19/06/2017 17:30:21 2 20/06/2017 17:30:22 20/06/2017 17:30:23 0 18/06/2017 17:30:24 18/06/2017 17:30:25 1 19/06/2017 17:30:20 19/06/2017 17:30:26 2 20/06/2017 17:30:22 20/06/2017 17:30:27 0 18/06/2017 17:30:24 18/06/2017 17:30:28 1 19/06/2017 17:30:20 19/06/2017 17:30:29 2 20/06/2017 17:30:22 20/06/2017 17:30:30 9 lignes sélectionnées.
Le résultat ci-dessus nous informe que l’exécution s’est déroulée de la façon suivante :
- Exécution de f_deterministic(0) –> 18/06/2017 15:31:18
- Exécution de f_non_deterministic(0) –> 18/06/2017 15:31:19
- Exécution de f_deterministic(1) –> 19/06/2017 17:30:20
- Exécution de f_non_deterministic(1) –> 19/06/2017 17:30:21
- Exécution de f_deterministic(2) –> 20/06/2017 17:30:22
- Exécution de f_non_deterministic(2) –> 20/06/2017 17:30:23
- Exécution de f_deterministic(0) –> 18/06/2017 17:30:24
- Exécution de f_non_deterministic(0) –> 19/06/2017 17:30:25
- Reprise de f_deterministic(1) –> 19/06/2017 17:30:20
- Exécution de f_non_deterministic(1) –> 19/06/2017 17:30:26
- Reprise de f_deterministic(2) –> 20/06/2017 17:30:22
- Exécution de f_non_deterministic(2) –> 20/06/2017 17:30:27
- Reprise de f_deterministic(0) –> 18/06/2017 17:30:24
- Exécution de f_non_deterministic(0) –> 19/06/2017 17:30:28
- Reprise de f_deterministic(1) –> 19/06/2017 17:30:20
- Exécution de f_non_deterministic(1) –> 19/06/2017 17:30:29
- Reprise de f_deterministic(2) –> 20/06/2017 17:30:22
- Exécution de f_non_deterministic(1) –> 19/06/2017 17:30:30
Nous pouvons dire que la fonction déterministe n’est pas généralement ré-exécutée pour la même valeur d’argument. Il se peut, cependant, simplement, qu’elle le soit. Quant à la fonction non déterministe, elle est, comme attendu, exécutée à chaque fois et – bien entendu – aucune reprise d’un calcul effectué précédemment n’est opérée.