Discussion :

[yrogerg_d]

Bonjour,

je n'ai rien trouvé dans la fonction recherche alors je pose ma question...

j'ai une requete (somme toute assez simple), quelques jointures etc.. rien de mechant..
une des clauses du where est :

Code :

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

AND DE.TYP_EMP = NVL(p_typ_emp, DE.TYP_EMP)

où p_typ_emp est un parametre passé au package ou se trouve la requete (un curseur)...
si le parametre n'est pas null.. le resultat est immediat...
si il est null.. là.. c'est la cata !! 10 secondes pour 20 lignes trouvées !!

du coup j'ai essayé ca :

Code :

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

AND DE.TYP_EMP = decode(p_typ_emp,NULL,de.typ_emp, p_typ_emp)

et là que ce soit null ou pas.. .c'est instantanée (pour le meme resultat qu'avec le nvl...)

quelqu'un aurait une idée ??
la base est une 10g...
merci

[Hugues_78]

Ta requête avec :

Code :

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

AND DE.TYP_EMP = NVL(p_typ_emp, DE.TYP_EMP)

est ce qu'il y a de pire pour l'optimiseur. Il s'attend à avoir toujours une valeur pour de.typ_emp... Il va donc effectuer un parcours suivant un index (vérifie ton plan d'exécution).
Quand tu lui passe NULL, il va parcourir l'index en filtrant des choses qui sont toujours identique.

Un index est un arbre récursif. La complexité de parcours d'un index est donc en N ln N (croissance forte en fonction de la taille) auquel il faut bien sur ajouter le temps de récupération des données dans la table. On n'utilise donc le parcours suivant un index que si l'interrogation ramène peu de lignes.

Pour avoir quelques chose de performant dans tous les cas, il est préférable de mettre la requête dans une variable (varchar2), puis d'ouvrir un curseur sur cette variable.

Tu va avoir un truc du genre :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
DECLARE
   req VARCHAR2(10000);
BEGIN
...
req :='select ... debut de la requête';
IF p_typ_emp IS NOT NULL THEN
   req := req || ' AND de.typ_emp = '''||p_typ_emp||''';
END IF;
req := req||' fin de la requête'

(attention aux espaces dans la construction)

Regarde la documentation PL/SQL fourni par Oracle, chapitre native dynamic sql. Tu auras tout ce qui va bien sur execute immediate ou open-for qui te permettront d'exécuter le code qui est dans la variable req

[orafrance]

que donne ceci ?

Code :

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

AND (DE.TYP_EMP = p_typ_emp OR p_typ_emp IS NULL)

[Hugues_78]

C'est effectivement une autre solution, plus rapide à mettre en oeuvre. Mais je crains un peu coté plan d'exécution (utilisation d'un éventuel index ?)

La version initiale me rappele le code d'un développeur il y a quelques années : en powerbuilder, il construisait la requête pour intérroger notre plus grosses tables... Toutes les possibilités d'interogation était écrite sous cette forme (numéro de client ou libellé de client ou date d'achat ou ...).

Lors des tests de performances avec un peu de volumétrie : full table scan systématique...
Pour aller vite dans la correction, on a tenté de patcher de la manière que tu proposes, mais l'optimiseur avait du mal à gérer la complexité de la requête obtenue (trop de or... mais c'était la version 8i à l'époque)

A tester... Ce serait bien si yrogerg_d (à tes souhaits ) nous donnait les plans d'exécutions des différentes possibilités (nvl / decode / or / remplacement de chaîne de caractères)

[yrogerg_d]

Salut

desole pour le pseudo... c un truc que traine depuis un moment...

bon.. alors pour le plan d'execution...
avec le nvl ou le decode, c'est strictement le meme plan (??!)

j'ai aussi essayé le (de.typ_emp = p_typ_emp or p_typ_emp is null)
en temps de reponse c'est identique au decode...

pas essayé de réécrire avec un ref cursor... trop de boulot je pense...
enfin si ca marche pas mieux on y pensera...

[yrogerg_d]

pour info j'ai aussi essayé COLAESCE.. ca fait comme le nvl.. ca rame !!

[remi4444]

...
du coup j'ai essayé ca :

Code :

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

AND DE.TYP_EMP = decode(p_typ_emp,NULL,de.typ_emp, p_typ_emp)

et là que ce soit null ou pas.. .c'est instantanée (pour le meme resultat qu'avec le nvl...)

quelqu'un aurait une idée ??
la base est une 10g...
merci

Que ce soit rapide avec le décode m'etonne vraiment... ça voudrais dire que le CBO de 10g traite de manière spéciale les nvl ou les décode ???

Effectivement il faudrait voir s'il y a une différence de plan...

[orafrance]

Que ce soit rapide avec le décode m'etonne vraiment... ça voudrais dire que le CBO de 10g traite de manière spéciale les nvl ou les décode ???

DECODE = OR = UNION ALL alors que NVL je ne sais pas trop comment il le gére

Hugues_78 en général, il vaut mieux utiliser la solution que tu préconises. Mais ce qui est assez facile dans les AGL (Forms, Visual, Powerbuilder, etc...) l'est moins en SQL. Ceci étant, j'ai mis OR pour gagner du temps, mais l'idéal aurait été de le transformer en UNION ALL

[remi4444]

DECODE = OR = UNION ALL alors que NVL je ne sais pas trop comment il le gére

Ben ce qui m'étonne c'est qu'il aille faire ce genre d'équivalence justement... jusqu'à présent que ce soit nvl, décode ou autre, c'est une fonction et pi c'est tout...

Si c'est le meme plan d'exéctution, il se peut qu'il y ait une histoire de cache, que ce passe-t-il si on enchaine 2 exéctutions avec nvl, puis 2 exectutions avec décode mais avec d'autres paramètres...

Autre doute qui me vient, dans l'exemple il est fait référence à une variable, donc je suppose que le chronométrage est fait au niveau de l'appel à une procédure, est-ce que ce serait pas à un autre endroit de la proc que ça coince.... (locks, calcul du curseur passé en paramètre etc...).

Pour analyser la requête, il faut l'exécutée toute seule avec une constante à la place de p_typ_emp.

[Hugues_78]

S'il y a un réel problème de performances, une solution rapide à mettre en oeuvre serait d'avoir 2 requêtes :
si p_emp est null on exécute la première sinon on exécute la seconde
(pas cool en terme de maintenance, mais ça se fait en 5mn, à la différence de la ré-écriture dans des chaînes de caractères)

Pour ton info, pl/sql accepte très bien les chaînes de caractères multilignes :

Code :

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

1
2
3
4
5
6
 
ma_variable := '
select *
from user_tables
where table_name like ''%TOTO%''
order by table_name';

J'ai bcp de proc qui fonctionne suivant ce principe. Une fois que ma requête est testée, je double tous les guillemets (' devient '') et je colle ça dans une variable avant de faire un execute immediate.

[orafrance]

mais c'est génial ça