Discussion :

[Darcynette]

Bonjour à tous,

Je dois faire un UPDATE sur une table contenant 170000 lignes, le tout en un minimum de temps car l'UPDATE est dans une procédure stockée à utiliser par des utilisateurs.
Mon code est le suivant :

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
 
declare
debextract date := :date1;
finextract date := :date2;
date_boucle date;
tbf number;
type_jour varchar2(3);
cursor c_tbf is 
/*déclaration du curseur*/	 
begin
for c in c_tbf
loop
/*On calcule les durées en minutes*/
	tbf :=0;
	date_boucle := debextract;
	select cal_particularite into type_jour from si_calendrier_20042020 where to_date(date_boucle) = cal_datejour; 
	while date_boucle <= finextract
	/*on fait tout un tas de calcul pour avoir tbf*/
update si_proc_disponibilite 
set prd_tbf = tbf 
where rowid = c.rowid ;
end loop;
commit; 	
end;

Ce code fonctionne met compte tenu du nombre de lignes, cela met plus de 30 minutes à s'exécuter, ce qui n'est pas viable.

Est-ce que qq1 a une idée pour améliorer le script ou une autre méthode pour fait un UPDATE en masse ?

Je suis sous ORACLE 9i et je suis obligée d'utiliser une procédure stockée car elle est lancée via COGNOS qui n'accepte que ce genre d'objets.

Merci pour vos réponses
Darcynette

[Loyd1974]

Pas évident comme ça de t'aider mais les pistes que j'explorerais serait :

• Utiliser le bulk collect et le FORALL au lieu de faire du fetch et de l'update unitaire
• Essayer d'intégrer la lecture du SI_CALENDRIER_20042020 au sein de ton curseur, encore une fois pour éviter de devoir chercher des valeurs de manière unitaire
• Ne pas faire de commit pour chaque ligne que tu updates mais seulement au bout d'un certain nombre. Mais si tu travailles en tableau avec le bulk collect, alors tu peux faire un commit à la fin de ta boucle de traitement comme ici

[Darcynette]

Merci pour ces pistes de recherche !!

Je vais essayer de comprendre les fonctionnements de FORALL et BULK COLLECT pour modifier tout ca.
Pour info, la recherche sur le calendrier doit se faire pour chaque jour balayer car il permet de déterminer si le jour est dans un week end ou un jour férie, le calcul de tbf étant différent dans ce cas (merci les règles de gestion à rallonge )

Si j'ai des questions, je n'hésiterai pas à les poser

@+
Darcynette

[McM]

Peux-tu poster le code complet stp, parce que

Code :

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

1
2
3
4
5
6
7
 
LOOP 
	date_boucle := debextract;
	SELECT cal_particularite INTO type_jour 
	FROM si_calendrier_20042020 
	WHERE TO_DATE(date_boucle) = cal_datejour;
END LOOP;

tu pourrais le sortir de la boucle vu que date_boucle = debextract (=:date1)

[Darcynette]

Oups, effectivement, vu ce que j'ai posté, ce n'est pas très parlant!
Le code complet est le suivant, avec qq explications :

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
 
declare
debextract date := :date1;
finextract date := :date2;
date_boucle date;
tbf number;
type_jour varchar2(3);
cursor c_tbf is 
	   select
	   prd_famille, prd_domaine, 
	   prd_code_bm,  
	   prd_reseau , rowid
	   from si_proc_disponibilite 
	   where (prd_domaine <> 'S-TE' and prd_domaine <> 'S-EQ' ) 
	   or (prd_code_bm like 'SONO%' and prd_code_bm <> 'SONOR-');
begin
for c in c_tbf
loop
/*On calcule les durées en minutes*/
	tbf :=0;
	date_boucle := debextract;
/*Je veux récupérer pour chaque jour si c'est un jour férié ou un dimanche car le calcul est différent*/
/*Mais peut-être que ce n'est pas judicieux ou le plus simple....*/
	select cal_particularite into type_jour from si_calendrier_20042020 where to_date(date_boucle) = cal_datejour; 
/*Je fais une boucle pour calculer par jour la durée*/
	while date_boucle <= finextract
	 loop
	 case when (c.prd_domaine in ('EQVT', 'EQPT', 'S-TE') or (c.prd_domaine in ('EQ11') and c.prd_famille in ('A001', 'A002')))
	 then tbf := tbf + 24*60;
	   date_boucle := date_boucle + 1;
	else case when ((type_jour = 'JF' or (to_char(date_boucle, 'day') in ('dimanche'))) and c.prd_reseau = 'METRO') 
	  then tbf := tbf + 21*60; 
	  date_boucle := date_boucle + 1; 
   	  else tbf := tbf + 20*60; 
	  date_boucle := date_boucle+1; 
	  end case; 
	end case ;
	end loop; 
update si_proc_disponibilite 
set prd_tbf = tbf 
where prd_code_bm = c.prd_code_bm;
end loop;
commit; 	
end;

J'ai aussi une question sur l'utilisation de FORALL par rapport à mon traitement de donnée.
Apparemment FORALL n'accepte pas plusieurs instructions à l'intérieur, comment je dois faire par rapport à ma boucle WHILE ?

Merci à tous pour votre aide.
Darcynette

[McM]

Pour Forall, va voir dans les tuto, le cours de Sheik sur sql. Le FOrall est bien expliqué.

Sinon, j'avais bien raison, le select sur le calendrier est fait 1 fois par boucle pour rien : 1 seule fois avant la boucle suffit.

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
 
DECLARE
debextract DATE := :date1;
finextract DATE := :date2;
date_boucle DATE;
tbf NUMBER;
type_jour VARCHAR2(3);
CURSOR c_tbf IS 
	   SELECT prd_famille, prd_domaine, 
	   			prd_code_bm, prd_reseau
	   FROM SI_PROC_DISPONIBILITE 
	   WHERE (prd_domaine <> 'S-TE' AND prd_domaine <> 'S-EQ' ) 
	   OR (prd_code_bm LIKE 'SONO%' AND prd_code_bm <> 'SONOR-')
		 FOR UPDATE NOWAIT;
BEGIN
 
	/*Je veux récupérer pour chaque jour si c'est un jour férié ou un dimanche car le calcul est différent*/
	/*Mais peut-être que ce n'est pas judicieux ou le plus simple....*/
	SELECT cal_particularite 
	INTO type_jour 
	FROM si_calendrier_20042020 
	WHERE  cal_datejour = TO_DATE(debextract); 
 
	FOR c IN c_tbf
	LOOP
		/*On calcule les durées en minutes*/
		tbf :=0;
		date_boucle := debextract;
 
		/*Je fais une boucle pour calculer par jour la durée*/
		WHILE date_boucle <= finextract
		LOOP
			 CASE WHEN (c.prd_domaine IN ('EQVT', 'EQPT', 'S-TE') OR (c.prd_domaine IN ('EQ11') AND c.prd_famille IN ('A001', 'A002')))
			 THEN tbf := tbf + 24*60;
			 ELSE 
				CASE WHEN ((type_jour = 'JF' OR (TO_CHAR(date_boucle, 'day') IN ('dimanche'))) AND c.prd_reseau = 'METRO') 
			  		THEN tbf := tbf + 21*60; 
			  	ELSE tbf := tbf + 20*60; 
			  	END CASE; 
			 END CASE;
			date_boucle := date_boucle + 1; 
		END LOOP; 
 
		UPDATE SI_PROC_DISPONIBILITE 
		SET prd_tbf = tbf 
		WHERE CURRENT OF c_tbf;
 
	END LOOP;
COMMIT; 	
END;

Je vais voir si on peut pas tout faire en pl (la boucle de calcul de jour)