Discussion :

[McFoggy]

Bonsoir tout le monde,

Je suis confronté à un problème d'évaluation de fonctions déterministes dans des clauses where de select.

Le problème est que ces fonctions , bien que déterministes, et appelées avec des constantes sont selon les cas évaluées autant de fois que de nuplets de ma requête alors que je m'attendrai à ce qu'elles soient évaluées entre 1 et N fois au maximum, N étant le nombre de fois où elles apparaissent dans ma requête.

J'ai essayé de simplifier mon problème au maximum, que je vous soumet ci-après.
Si quelqu'un pouvait m'expliquer les comportements que je décris ci-dessous et éventuellement me donner des solutions de contournement, j'apprécierai grandement.

A la fin du post, je décris également les solutions que j'ai trouvées/envisagées mais qui ne me satisfont pas à l'heure actuelle.

Voici, un script pour reproduire le problème (problème se situant partout où apparaissent les messages -- Moults appels à F_VALUE):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
DROP TABLE T_TESTFUNC;
 
CREATE TABLE T_TESTFUNC(PK NUMBER(10) PRIMARY KEY, N NUMBER(10));
 
BEGIN
	 FOR I IN 1..1000
	 LOOP
	 	 INSERT INTO T_TESTFUNC(PK) VALUES (I);
	 END LOOP;
	 UPDATE T_TESTFUNC SET N = MOD(PK, 100);
END;
 
COMMIT;
 
CREATE FUNCTION F_VALUE(V NUMBER) RETURN NUMBER DETERMINISTIC IS
BEGIN
	 DBMS_OUTPUT.PUT_LINE('Call:' || V);
	 RETURN V;
END F_VALUE;
/
 
SET SERVEROUTPUT ON SIZE 1000000
 
SELECT PK
FROM T_TESTFUNC
WHERE N > F_VALUE(10);
 
-- Moults appels à F_VALUE   
 
SELECT PK
FROM T_TESTFUNC
WHERE PK > F_VALUE(10);
 
-- 1 seul appel à F_VALUE  
 
CREATE INDEX T_TESTFUNC_N_IDX ON T_TESTFUNC(N);
 
SELECT PK
FROM T_TESTFUNC
WHERE N > F_VALUE(10);
 
-- 1 seul appel à F_VALUE  
 
SELECT PK
FROM T_TESTFUNC
WHERE PK > F_VALUE(10);
 
-- 1 seul appel à F_VALUE  
 
SELECT PK
FROM T_TESTFUNC
WHERE 
	  PK > F_VALUE(10)
	  AND N > F_VALUE(10)
 
-- Moults appels à F_VALUE  
 
SELECT PK
FROM T_TESTFUNC
WHERE 
	  PK > F_VALUE(10)
	  OR N > F_VALUE(10)
 
- 1 seul appel à F_VALUE

A ces problèmes j'entrevoit 3 solutions:

• faire marcher la fonction déterministe
• utiliser la syntaxe (SELECT F_VALUE(10) FROM DUAL) à la place de F_VALUE(10)
• effectuer un appel coté client (quand c'est possible) pour pré-calculer la valeur de F_VALUE(10)

La première solution serait bien évidemment la meilleure pour moi, parceque ces fonctions, que j'ai simplifié ici pour l'exemple, sont appelées depuis du code JAVA, PL.SQL, C++ et sont relativement complèxes et donc centralisées sur la base.

D'avance merci à tous ceux/celles qui pourront m'aider.

[McFoggy]

Bonsoir,

Après avoir cherché en vain des solutions, voici la réponse officielle d'Oracle après ouverture d'un TAR. Il faut utiliser la syntaxe :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

(SELECT F_VALUE(10) FROM DUAL)

Selon moi celà n'est qu'une réponse pour noyer le poisson, je ne vois pas toujours pas pourquoi Oracle n'arrive pas à mettre en cache l'appel à la fonction, mais on en restera là.