Discussion :

[blackangel_xwing]

En ce qui concerne la performance, il est préférable d'utiliser l'instruction if-then-else ou la clause where ?
Merci pour votre reponse

[Waldar]

Ça dépend.

[pachot]

Bonjour,
Une clause where peut servir à filtrer des données lors de l'accès alors qu'une condition pl/sql va lire toutes les données pour les filtrer ensuite. Donc la réponse serait en faveur du where.
Cordialement,
Franck.

[blackangel_xwing]

Je comprends que la clause where charge la mémoire avec les données filtrées tandis que l'instruction if-then else charge la mémoire avec toutes les données, c'est vrai?
Donc, le code suivant :

Code :

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

1
2
3
4
5
6
select 
  case when x > 3 then 'ok'
       else 'not ok'
  end action;	
for r in c loop 
update.. set.. where.. and  r.action = 'ok'

est meilleur que :

Code :

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

1
2
3
4
5
6
7
select 
  case when x > 3 then 'ok'
       else 'not ok'
  end action;
for r in c loop 
if r.action = 'ok'
update.. set.. where..

Merci,
blackangel

[Scriuiw]

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
select
case when x > 3 then 'ok'
else 'not ok'
end action;
for r in c loop
update.. set.. where.. and r.action = 'ok'
 
est meilleure que :
 
select
case when x > 3 then 'ok'
else 'not ok'
end action;
for r in c loop
if r.action = 'ok'
update.. set.. where..

Et dans ton deuxième cas tu mets quoi dans la clause WHERE à ton avis ???
J'ai pas l'impression que tu aies compris ta propre question !

La différence porte sur un exemple tel que :

Code :

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

1
2
3
4
5
6
7
8
FOR i IN 
  (SELECT col1, col2
  FROM ma_table)
  LOOP
 
  IF (Col2 = 0) THEN
    UPDATE ..
  END IF;

Et celui-ci :

Code :

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

1
2
3
4
5
6
7
8
 
FOR i IN 
  (SELECT col1, col2
  FROM ma_table
  WHERE Col2 = 0)
  LOOP
 
  UPDATE ..

Le 2eme sera plus performant car le nombre de lignes sélectionnées est conditionné par la clause WHERE, en résulte (normalement) un nombre de lignes moindre à traiter

[blackangel_xwing]

La situation exacte est le suivante :

j'ai créé une table log :

Code :

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

1
2
3
4
5
6
create table log as
SELECT
case when date > trunc(sysdate-20) then 'ok'
else 'not ok'
end action, *
from table_y

Je veux faire update sur la table_y en fonction de chaque cas ( et puis faire update sur log pour voir les cas traités avec succès ou échec).

Code :

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

1
2
3
for r in (select * from log) loop
if r.action = 'ok'
update table_y set.. where..

ou

Code :

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

1
2
for r in (select * from log) loop
update.. set.. where.. and r.action = 'ok'

Je voudrais savoir si le 2eme est plus performant

[FSiebert]

Bonjour,

La situation exacte est le suivante

Je trouve votre explication encore très imprécise...

Pourquoi ne mettez-vous pas la condition sur le SELECT (cela serait plus performant) ?

Expliquez VRAIMENT et COMPLÈTEMENT ce que vous voulez faire, vous aurez une réponse plus rapide et bien plus adaptée à vos besoins.

[blackangel_xwing]

J'essaie d'être plus explicite

Je ne peut pas mettre la condition sur le select parce que je necessite de faire update sur table - source en fonction de la condition date > trunc (sysdate-20); j'utilise la table log pour voir les cas traités avec succès/échec et retenir les erreurs en cas d'échec;

La première tentative a été :

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
create table log as
SELECT  y.*
from table_source y
 
for r in (select * from log) loop
begin
 
if r.date > trunc(sysdate-20) then 
	update table_source  y set y.exec ='D' where y.id = r.id
	update log set obs ='exécuté' where id = r.id
else
 
	update table_source  y set y.rej ='D' where y.id = r.id
	update log set obs ='rejeté' where id = r.id
end if ;
 
exception when others then
		update log set obs ='err' where id = r.id 
end;
end loop;

Mais j'utilisais "select case " parce que je pensais qu'il est plus performant, corrigez-moi si je me trompe ..

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
create table log as
SELECT
case when date > trunc(sysdate-20) then 'ok'
else 'not ok'
end action, *
from table_source y 
 
-- 1ère version
for r in (select * from log) loop
if r.action ='ok' then
	begin
		update table_source  y set y.exec ='D' where y.id = r.id
		update log set obs ='exécuté' where id = r.id
	exception when others then
		update log set obs ='err' where id = r.id 
    	end;
else 
	begin
		update table_source  y set y.rej ='D' where y.id = r.id
		update log set obs ='rejeté' where id = r.id
	exception when others then
		update log set obs ='err' where id = r.id 
	end;
end if;
end loop;
 
--la 2eme
for r in (select * from log) loop
begin
	update table_source  y set y.exec ='D' where y.id = r.id and r.action ='ok'
	    update log set obs ='exécuté' where id = r.id and r.action ='ok'
	update table_source  y set y.rej ='D' where y.id = r.id and r.action ='not ok'
	    update log set obs ='rejeté' where id = r.id and r.action ='not ok'
exception when others then
        update log set obs ='err' where id = r.id 
end;
end loop;

Quelle version est donc plus performante?

[McM]

Il sera toujours moins performant de faire un update qui va updater 0 ligne que de ne pas faire d'update !

Donc entre ce code qui fait 2 updates dont un qui ne va rien faire mais qu'Oracle va quand même chercher à savoir s'il y a des données

Code :

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

1
2
UPDATE table_source  y SET y.exec ='D' WHERE y.ID = r.ID AND r.action ='ok'
UPDATE table_source  y SET y.rej ='D' WHERE y.ID = r.ID AND r.action ='not ok'

et ce code qui ne fait qu'un seul update suivant une variable

Code :

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

1
2
3
4
5
6
IF r.action = 'ok' 
THEN 
	UPDATE table_source  y SET y.exec ='D' WHERE y.ID = r.ID
ELSE
	UPDATE table_source  y SET y.rej ='D' WHERE y.ID = r.ID
END IF;

C'est forcément le deuxième code.

[Scriuiw]

La version la plus performante est celle que vous n'avez pas exploitée, c'est à dire en FULL SQL.

Votre code est très facilement transposable en commande ensembliste

PS : A moins que dans votre code il n'y ait des appels à des procédures, fonctions, ou autres tests/manipulations de données que l'on ne peut voir dans votre extrait de code.

[McM]

Sauf pour traiter les erreurs au niveau ligne. Imagine qu'il y a un trigger sur table_source qui peut sortir en erreur, un seul update ne serait pas gérable.

[Scriuiw]

C'est aussi une possibilité que je n'avais pas envisagée vue la simplicité de la mise à jour.
Elle est toutefois à prendre en compte

[blackangel_xwing]

Code :

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

1
2
UPDATE table_source  y SET y.exec ='D' WHERE y.ID = r.ID AND r.action ='ok'
UPDATE table_source  y SET y.rej ='D' WHERE y.ID = r.ID AND r.action ='not ok'

Code :

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

1
2
3
4
5
6
IF r.action = 'ok' 
THEN 
	UPDATE table_source  y SET y.exec ='D' WHERE y.ID = r.ID
ELSE
	UPDATE table_source  y SET y.rej ='D' WHERE y.ID = r.ID
END IF;

Pouvez vous me dire comment traite ces deux cas les moteurs pl/sql et sql ?

La version la plus performante est celle que vous n'avez pas exploitée, c'est à dire en FULL SQL.

FULL SQL signifie à n'utiliser pas du tout pl/sql ?

Merci beaucoup

[Scriuiw]

Le moteur PL/SQL ouvre un curseur et pour chaque boucle envoi une instruction au moteur SQL pour mettre à jour la ligne correspondante.

Quand je dis FULL SQL ça n'interdit pas le PL/SQL, c'est juste qu'au lieu de faire un curseur, une boucle une instruction de mise à jour, on fait une instruction SQL qui met à jour toutes les lignes.

[mnitu]

Sauf pour traiter les erreurs au niveau ligne. Imagine qu'il y a un trigger sur table_source qui peut sortir en erreur, un seul update ne serait pas gérable.

Si, facilement, avec DML Error Logging.

Le code suivant:

Code :

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

1
2
3
4
 
...
exception when others then
		UPDATE log SET obs ='err' WHERE id = r.id

est le bug numéro 1 en Pl/SQL.

Faire du DML en boucle

Code :

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

1
2
3
4
 
For r In ... Loop
  Update ... set where r.id...
End Loop;

c’est le Pl/SQL de la grande mère !

[McM]

1: DML Error Logging c'est à partir de la 10gR2
2 : Le when others.. mouais.. .. moi je ne suis pas d'accord avec Tom Kyte.

[blackangel_xwing]

Une clause where peut servir à filtrer des données lors de l'accès alors qu'une condition pl/sql va lire toutes les données pour les filtrer ensuite.

Il sera toujours moins performant de faire un update qui va updater 0 ligne que de ne pas faire d'update !

Le moteur PL/SQL ouvre un curseur et pour chaque boucle envoi une instruction au moteur SQL pour mettre à jour la ligne correspondante.

Excusez moi pour l'insistance...
Pouvez vous me dire comment traite ces deux cas les moteurs pl/sql et sql (l'instruction if-then-else ne charge plus le moteur pl/sql) ?

Merci beaucoup

[mnitu]

...2 : Le when others.. mouais.. .. moi je ne suis pas d'accord avec Tom Kyte.

OK très bien chacun a ses gouts. Mais analysez l’exemple que j’ai extrait et dit moi vous ne trouvez pas qu’il y a un problème avec cette approche ?

[mnitu]

Chaque fois quand vous utilisez une construction similaire

Code :

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

1
2
3
4
5
6
7
8
...
FOR r IN (SELECT * FROM log) loop
begin
 
IF r.date > trunc(sysdate-20) then 
	UPDATE table_source  y SET y.exec ='D' WHERE y.id = r.id
	UPDATE log SET obs ='exécuté' WHERE id = r.id

c’est à dire un curseur qui cherche des valeurs pour alimenter par la suite une autre requête SQL vous devez vous poser des questions. En fait ce type de traitement est inefficace d’une part à cause des changements de contexte entre le moteur PL/SQL et le moteur SQL et d’autre part à cause du traitement ligne par ligne qu’il suppose.
Deux solutions sont envisageables pour améliorer ce code :

• Solution SQL : utilisez une seule requête SQL, dans votre cas un seul Update ou Merge
• Solution PL/SQL : utilisez des collections c’est à dire traitement par lot et l’instruction FORALL…

[thepierre]

oracle = machine à sql.

donc il faut oublier le pl/sql, qui n'est qu'un pis-aller pour transférer un programme fortran des années 1970 vers oracle avec un taux de paresse maximum.