[PL/SQL] Optimisation d'une requête (like?)

Version imprimable

Bonjour à tous !

Voilà près de 3 ans que je n’ai rien posté (je sévissais plutôt sur le forum Delphi, hé oui, les questions débiles, c’était moi ! :oops:) mais je n’ai jamais cessé de visiter ce forum et d’apprendre ! :king:

Etant complètement newbie en PL/SQL et ayant un besoin urgemment urgent pour le boulot, je me permets de venir chercher vos lumières sur une question d’optimisation de requête SQL :

J’ai défini un curseur comme ceci :
Code:

1 2 3 4 5 6 7 8 9 CURSOR MonCurseur IS SELECT distinct T1_ch1, T1_ch2, T1_ch3 --- 3 champs de la table T1 FROM T1, T2, T3, T4 --- 4 tables WHERE T2_ch1 like param1 || '_' --- jointures diverses entre les champs des tables and T3_ch1 = param2 and T4_ch2 = T3_ch2 and T1_ch2 = T4_ch2 and T2_ch3 = T1_ch3;
Les paramètres « param1 » et « param2 » sont des variables globales (je sais c’est pas beau !).
Le champ «T2_ch1» doit prendre la forme suivante : ‘CE01x’ où x peut prendre les valeurs 0, 1, 2 à 9, d’où le

Code:

like param1 || ‘_’

où param1 est un varchar2.

Mon programme et cette requête marchent très bien, mais le hic c’est que ma requête est super gourmande (temps d’exécution 800ms à 3 secondes) et elle est appelée 18 000 fois (pour chaque couple (param1,param2)) : mon programme tourne du coup pendant plus de 2 heures !

A première vue et après quelques tests, c’est la clause « where/like » qui serait coûteuse.
Qu’en pensez-vous ?

J’ai essayé d’optimiser avec « where/in » pas très concluant :
Code:

1 2 3 4 5 6 7 8 9 10 WHERE T2_ch1 in (param1|| '0', param1 || '1', param1 || '2', param1 || '3', param1 || '4', param1 || '5', param1 || '6', param1 || '7', param1 || '8', param1 || '9')
Existe-t-il un moyen d’optimiser considérablement ma requête ?
Suis-je à côté de la plaque ? (c'est tout à fait possible aussi :aie: )

Etant novice sur ce sujet, toute aide serait la bienvenue et je vous en remercie d’avance ! :lol:

Quelques infos au cas où :
Je travaille sous Toad 7.4, Oracle 9i.
- seul le champ « T3_ch1 » n’est pas indexé
Tailles approximatives des tables :
- T1 : 75 000
- T2 : 15 000
- T3 : 30 000
- T4 : 30 000
Ce qui n'est pas énorme non? :|

Le distinct est pas top.
Que donne l'explain plan de ta requete et de celle-ci ?

Code:

1
2
3
4
5
6
7
8
9
10
SELECT T1_ch1, T1_ch2, T1_ch3 --- 3 champs de la table T1
	FROM T1
	WHERE EXISTS (SELECT 1
			FROM T2, T3, T4
			WHERE T2_ch1 LIKE param1 || '_'
			AND T3_ch1 = param2	
			AND T4_ch2 = T3_ch2
			AND T2_ch3 = T1_ch3
			AND T4_ch2 = T1_ch2
			)

En reprenant les diverses jointures, on s'apperçoit que les 3 tables T2-T3-T4 sont toutes liées à T1

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT T1_ch1, T1_ch2, T1_ch3 --- 3 champs de la table T1
FROM T1
WHERE EXISTS (SELECT 1
		FROM T2
		WHERE T2_ch1 LIKE param1 || '_'
		AND T2_ch3 = T1_ch3
		)
AND EXISTS (SELECT 1
	FROM T3
	WHERE T3_ch1 = param2	
	AND T3_ch2 = T1_ch2
	)
AND EXISTS (SELECT 1
	FROM T4
	WHERE T4_ch2 = T1_ch2
	)

30/05/2007, 01h50
elyo66

Merci pour tes réponses McM ! :D
Je ne sais pas ce qu'est un "explain plan" mais je suppose que c'est une option dans Toad? :?
Je m'y mets dès demain matin au boulot... :zzz:
30/05/2007, 02h13
Ora92_IDS

Bonsoir
Explain Plan donne le shema d'execution d'une requete, a vrai dire c'est une table stocke ds la Base de Donnees s'appelle Plan_table,cette table s'alimente une fois tu execute une requete.
30/05/2007, 10h07
McM

Sous toad, dans le sql editor :
Icone : ambulance
ou Menu Sql Editor / Explain Plan Current SQL (CTRL+E)

Cool, merci pour vos réponses ! :D
Grâce à l'explain plan, j'ai pu voir que c'était plutôt l'accès au champ non indexé qui était coûteux, et non l'utilisation du "like" : j'ai un "TABLE ACCESS FULL" sur T3_ch1.

J'ai essayé avec ta requête McM :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 SELECT T1_ch1, T1_ch2, T1_ch3 --- 3 champs de la table T1 FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE T2_ch1 LIKE param1 || '_' AND T2_ch3 = T1_ch3 ) AND EXISTS (SELECT 1 FROM T3 WHERE T3_ch1 = param2 AND T3_ch2 = T1_ch2 ) AND EXISTS (SELECT 1 FROM T4 WHERE T4_ch2 = T1_ch2 )
Et j'obtiens :
http://img408.imageshack.us/img408/3...ainplanth6.jpg
En tout cas, le fait de sectionner avec des "exists "c'est très intéressant si ma requête ne renvoie pas toujours des résultats, mais ce ne sera jamais le cas normalement.

C'est donc vraiment le champ "T3_ch1" qui pose problème : la requête doit potentiellement parcourir toute la table! Or je suis obligée de passer par ce champ là...
Si j'extrais de ma requête la partie touchant à la table T3 comme ceci :
Code:

1 2 3 4 5 6 7 CURSOR MonCurseur IS SELECT T1_ch1, T1_ch2, T1_ch3 FROM T1, T2, T4 WHERE T2_ch1 like param1 || '_' and T4_ch2 = resultat_RequeteT3 and T1_ch2 = T4_ch2 and T2_ch3 = T1_ch3;
avec RequeteT3 :
Code:

1 2 SELECT T3_ch2 from T3 where T3_ch1 = param2
Est-ce que ça peut optimiser mon programme ? :?
Voyez-vous d'autres solutions ?
Merci d'avance ! :mrgreen:

30/05/2007, 11h54
McM

Il y a une grosse différence entre

Code:

SELECT distinct FROM T1, T2, T3, T4

et

Code:

SELECT FROM T1 WHERE exits (select 1 from T2) ... AND exists (select 1 from T4)

Dans le premier cas, à moins que ta clause where ne ramène qu'une seule ligne par table, c'est un produit cartésien.
Vu que tu as mis un distinct, c'est ce que je pense.
Et là, le tri nécessaire au Distinct n'est pas bon du tout.

Avec des exists, tu n'as plus ce problème.

Pour le table access full, crées un index sur T3_ch1 ou T3_ch2, ou mieux (T3_ch1, T3_ch2) ou (T3_ch2, T3_ch1), ça dépend de ta table.
30/05/2007, 12h10
elyo66

Ah je comprends mieux, merci McM !
J'utilise ta requête avec les "exists" du coup.

Pour la table "T3" qui est en accès full, malheureusement je n'ai rien le droit de toucher car ça impacterait trop de choses au niveau de la base. On m'a expliqué qu'en gros je n'ai aucune lattitude possible sur les tables : je ne peux en aucun cas modifier leur structure...

En tout cas je vous remercie pour votre aide ! et à moins que vous n'ayez de nouvelles idées d'ici vendredi, je clôturerai cette discussion :cry:
30/05/2007, 12h20
McM

C'est pas la structure de la table qu'il faut modifier, mais un index à créer !
30/05/2007, 13h58
elyo66

Pardon je m'exprime mal !
Ce que je veux dire c'est qu'on m'a bien stipulé que je n'avais rien le droit de modifier au niveau de la base, notamment que je ne peux pas ajouter d'index ! :cry:
En tout cas merci pour ton aide ! si t'as d'autres choses qui te passent par la tête je suis preneuse... :help:
30/05/2007, 14h10
McM

Yop, c'est pas la première fois que j'entends ça.

Sinon pour l'opti, faut voir au niveau complet et pas juste le curseur afin de voir si une opti ailleur n'est pas faisable pour limiter les 18000 boucles.
30/05/2007, 15h05
elyo66

Oui j'ai essayé d'optimiser en amont dans mon programme, mais le champ "T3_ch1" qui pose problème change à chaque itération, donc la boucle appelle bien 18 000 fois la requête avec param2 différent à chaque fois :oops:
31/05/2007, 17h59
dyvim

Si ton curseur est long, tu peux peut être l'accélérer avec un traitement en "BULK COLLECT". Ca peut faire la même chose qu'un curseur mais plus vite...
A condition que tu n'ai qu'une seule instruction dans ton curseur par contre.
01/06/2007, 13h35
elyo66

Salut Dyvim !
Aurais-tu un exemple? je vais essayer de trouver des infos sur les "bulk collect"... En tout cas, merci pour le tuyau ! :D

Un petit exemple...
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 CREATE OR REPLACE PROCEDURE fast_proc (p_array_size IN PLS_INTEGER DEFAULT 100) IS TYPE ARRAY IS TABLE OF all_objects%ROWTYPE; l_data ARRAY; CURSOR c IS SELECT * FROM all_objects; BEGIN OPEN c; LOOP FETCH c BULK COLLECT INTO l_data LIMIT p_array_size; FORALL i IN 1..l_data.COUNT INSERT INTO t2 VALUES l_data(i); EXIT WHEN c%NOTFOUND; END LOOP; CLOSE c; END fast_proc; /
Attention, cela ne marche que si tu joues la même instruction pour chacune des lignes du curseur...
Si c'est le cas, tu gagneras en performance par rapport à un curseur car au lieu de jouer 1000 ou 10 000 fois la même instruction, il n'en jouera qu'une ;) plus complexe...