Plusieurs boucles sur le résultat d'un curseur

**claire13** · 08/04/2009, 13h21

bonjours,

J'aimerais afficher mes partners, et pour chacun le nb de ses factures, de ses types...
J'ai réalisé ce code:

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
declare
-- cursor c1: liste des partner potentiellement dupliqué
       cursor c1 is
              select * from temp_partner_duplicates;
       part temp_partner_duplicates%rowtype;
 
-- cursor c2: nb de type pour 1 partner       
       cursor c2 (code varchar2)is
              select count(*) from partner_types pt where pt.partner_code=code;
       res Number;
       res2 Number;
-- cursor c3: nb de factures pour un partner dans les 3 derniers mois
       cursor c3(code varchar2) is
              select count(*) 
              from sales_inv_headers sih
              where sih.partner_code=code
              and sih.inv_date>add_months(sysdate,-3);
 
 
begin
     for part in c1
     loop
         open c2(part.partner_code);
         open c3(part.partner_code);
              fetch c2 into res;
              fetch c3 into res2;
 
              dbms_output.put_line(part.partner_code||';'||part.dup_partner_code||';'||res||';'||res2);
              close c2;
              close c3;
     end loop;
end;

Il marche mais c'est très long

Comment faire pour l'améliorer?
merci d'avance
claire

**McM** · 08/04/2009, 14h04

Ferme l'autre Post (partie SQL) s'il te plait, ça évitera que quelqu'un réponde là bas.

Sinon pour ton code : Un curseur qui ne ramène qu'une ligne (exemple c2 et c3) n'a pas lieu d'être. Utilises plutôt un SELECT standard.

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
DECLARE
 
	res NUMBER;
	res2 NUMBER;
 
BEGIN
	--liste des partner potentiellement dupliqué
	FOR r IN (SELECT p.partner_code, p.dup_partner_code
			FROM TEMP_PARTNER_DUPLICATES p)
	LOOP
		-- nb de type pour 1 partner       
		SELECT COUNT(*) 
		INTO res
		FROM PARTNER_TYPES
		WHERE partner_code = r.partner_code;
 
		-- nb de factures pour un partner dans les 3 derniers mois
		SELECT COUNT(*) 
		INTO res2
		FROM SALES_INV_HEADERS
		WHERE partner_code = r.partner_code
		AND inv_date > ADD_MONTHS(SYSDATE,-3);
 
		DBMS_OUTPUT.PUT_LINE(r.partner_code||';'||r.dup_partner_code||';'||res||';'||res2);
 
	END LOOP;
END;

Et bien sur, une seule requête évite les aller-retours pl <-> base

Code :

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

1
2
3
4
5
6
7
8
9
10
 
BEGIN
	FOR r IN (SELECT p.partner_code, p.dup_partner_code, 
			(SELECT COUNT(*) FROM PARTNER_TYPES WHERE partner_code = p.partner_code) AS nb_type,
			(SELECT COUNT(*) FROM SALES_INV_HEADERS WHERE partner_code = p.partner_code AND inv_date > ADD_MONTHS(SYSDATE,-3)) AS nb_fact
		FROM TEMP_PARTNER_DUPLICATES p)
	LOOP
		DBMS_OUTPUT.PUT_LINE(r.partner_code||';'||r.dup_partner_code||';'||r.nb_type||';'||r.nb_fact);
	END LOOP;
END;

**claire13** · 08/04/2009, 14h10

merci beaucoup pour ta réponse.
J'étais en train de tester avec du sql pure.
Je vous montre ma requête (je pense que j'ai un sérieux problèmes d'optimisation

)

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
select tpd.partner_code,a.NB_type,b.Nb_ad,c.Nb_EDI,d.Nb_INTTRA,
       tpd.dup_partner_code,abis.Nb_type,bbis.Nb_ad,cbis.Nb_EDI,dbis.Nb_INTTRA
from temp_partner_duplicates tpd
     left join
          (select pt.partner_code,count(*)as Nb_type
          from partner_types pt
          group by pt.partner_code)a
     on tpd.partner_code=a.partner_code
     left join
          (select pa.partner_code,count(*)as Nb_ad
          from partners_addresses pa
          group by pa.partner_code)b
     on tpd.partner_code=b.partner_code
     left join
          (select ept.partner_code,count(*) as Nb_EDI
          from edi_partner_translation ept
          where ept.edi_partner_code not in('0000034582','0001718903') 
          group by ept.partner_code)c
     on tpd.partner_code=c.partner_code
     left join
          (select ept.partner_code,count(*) as Nb_INTTRA
          from edi_partner_translation ept
          where ept.edi_partner_code in('0000034582','0001718903') 
          group by ept.partner_code)d
     on tpd.partner_code=d.partner_code
-- Calcul pour les partner duplicate
     left join
          (select pt.partner_code,count(*)as Nb_type
          from partner_types pt
          group by pt.partner_code)abis
     on tpd.dup_partner_code=abis.partner_code
     left join
          (select pa.partner_code,count(*)as Nb_ad
          from partners_addresses pa
          group by pa.partner_code)bbis
     on tpd.dup_partner_code=bbis.partner_code
     left join
          (select ept.partner_code,count(*) as Nb_EDI
          from edi_partner_translation ept
          where ept.edi_partner_code not in('0000034582','0001718903') 
          group by ept.partner_code)cbis
     on tpd.dup_partner_code=cbis.partner_code
     left join
          (select ept.partner_code,count(*) as Nb_INTTRA
          from edi_partner_translation ept
          where ept.edi_partner_code in('0000034582','0001718903') 
          group by ept.partner_code)dbis
     on tpd.dup_partner_code=dbis.partner_code
 
where tpd.country='VN'

mais ça marche quand meêm (et pas trop long)
Ma question est: vaut-il mieux utiliser pl/sql? ou cela revient au même d'utiliser sql?

merci encore, je vais de ce pas fermer la discussion sur l'autre forum.
claire

**Waldar** · 08/04/2009, 15h51

Votre requête peut encore s'améliorer.
Vous avez quelques sous-requêtes identiques que vous pouvez factoriser avec WITH (quelle est votre version d'Oracle) ?

Vos scannez deux fois la même table avec deux conditions complémentaires, autant scinder le résultat en scannant une seule fois la table.

Et enfin il faut gérer les jointures externes en traitant les nulls :

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
WITH ptype AS
(SELECT pt.partner_code, count(*) AS Nb_type
 FROM partner_types pt
 GROUP BY pt.partner_code),
    pcode AS
(SELECT pa.partner_code, count(*) AS Nb_ad
 FROM partners_addresses pa
 GROUP BY pa.partner_code),
    ptrns AS
(SELECT ept.partner_code,
  sum(case when ept.edi_partner_code NOT IN ('0000034582','0001718903') THEN 1 ELSE 0 END) AS Nb_EDI,
  sum(case when ept.edi_partner_code     IN ('0000034582','0001718903') THEN 1 ELSE 0 END) AS Nb_INTTRA
 FROM edi_partner_translation ept
 GROUP BY ept.partner_code)
SELECT
    tpd.partner_code,
    coalesce(a1.Nb_type, 0) as Nb_type_part
    coalesce(b1.Nb_ad, 0) as Nb_ad_part,
    coalesce(c1.Nb_EDI, 0) as Nb_EDI_part,
    coalesce(c1.Nb_INTTRA, 0) as Nb_INTTRA_part,
    tpd.dup_partner_code,
    coalesce(a2.Nb_type, 0) as Nb_type_dup,
    coalesce(b2.Nb_ad, 0) as Nb_ad_dup,
    coalesce(c2.Nb_EDI, 0) as Nb_EDI_dup,
    coalesce(c2.Nb_INTTRA, 0) as Nb_INTTRA_dup
FROM
    temp_partner_duplicates tpd
    LEFT OUTER JOIN ptype a1
      ON tpd.partner_code = a1.partner_code
    LEFT OUTER JOIN pcode b1
      ON tpd.partner_code = b1.partner_code
    LEFT OUTER JOIN ptrns c1
      ON tpd.partner_code = c1.partner_code
-- Calcul pour les partner duplicate
    LEFT OUTER JOIN ptype a2
      ON tpd.dup_partner_code = a2.partner_code
    LEFT OUTER JOIN pcode b2
      ON tpd.dup_partner_code = b2.partner_code
    LEFT OUTER JOIN ptrns c2
      ON tpd.dup_partner_code = c2.partner_code
WHERE
    tpd.country = 'VN'

**claire13** · 08/04/2009, 16h11

C'est génial, je devais attendre 10 min et maintenant j'attends plus que 2 min

C'est dingue ce que l'on peut faire quand on cannait bien SQL!!

merci bcp
En fait, ma version est la 9.02.
Je vais décortiquer ta requête pour bien la comprendre.

une dernière question, quel est l'intérêt d'utiliser PL/SQL à la place de SQL?

**orafrance** · 08/04/2009, 16h50

Envoyé par claire13

Ma question est: vaut-il mieux utiliser pl/sql? ou cela revient au même d'utiliser sql?

Il vaut mieux toujours privilégier le SQL quand c'est possible. Ramener x lignes c'est toujours plus rapide que ramener x fois une ligne

Envoyé par claire13

une dernière question, quel est l'intérêt d'utiliser PL/SQL à la place de SQL?

PL/SQL est un langage procédural permettant de traiter les données et adapter le traitement en conséquence avec des boucles, des conditions, des gestions d'erreurs, etc...

**Waldar** · 08/04/2009, 16h51

Si vous êtes en Oracle 9i, mettez le hint /*+ materialize */ après le select (je ne suis pas certain de moi s'il faut le mettre dans les sous-requêtes du with ou le select global, essayez).
Vérifier toutefois dans le plan d'exécution combien de scan de vos tables vous avez (avec et sans), l'idée étant de ne les parcourir qu'une seule fois.

Avec PL/SQL, on rentre vraiment dans la programmation et on peut faire beaucoup beaucoup beaucoup plus de choses qu'en SQL pur.

Néanmoins, comme ce sujet le prouve, c'est souvent une bonne chose d'en faire le plus possible en SQL pur et de compléter en PL/SQL ce qui n'est pas faisable ou qui donne de trop mauvais temps de traitements.

**mnitu** · 08/04/2009, 18h01

D'une manière générale éviter d'ajouter des hints dans vos requêtes. Et quand il s’agit des hints non-documentés il faut les proscrire, sauf pour des amusements (tests, forums, etc.).
Materialize hint vs rownum

**claire13** · 09/04/2009, 13h36

Bonjour,

merci beaucoup pour toutes vos réponses. J'ai fais quelques recherches sur "hint" et donc l'optimisation des requêtes. J'avoue que je ne comprends pas très bien encore.
J'ai finalisé ma requête sur votre modèle (sans ajouter hint car je n'ai pas encore compris) et voici mes résultats: (je remets la requête pour infos):

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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
-- Nombre de types
WITH ptype AS
(SELECT pt.partner_code, count(*) AS Nb_type
 FROM partner_types pt
 GROUP BY pt.partner_code),
-- Nombre d'adresses
    pcode AS
(SELECT pa.partner_code, count(*) AS Nb_ad
 FROM partners_addresses pa
 GROUP BY pa.partner_code),
    ptrns AS
-- Nombre de edi/INTTRA
(SELECT ept.partner_code,
  sum(case when ept.edi_partner_code NOT IN ('0000034582','0001718903') THEN 1 ELSE 0 END) AS Nb_EDI,
  sum(case when ept.edi_partner_code     IN ('0000034582','0001718903') THEN 1 ELSE 0 END) AS Nb_INTTRA
 FROM edi_partner_translation ept
 GROUP BY ept.partner_code),
-- Nombre de factures sur les 3 derniers mois
       pfact AS
(select pp.partner_code,count(*) as Nb_factures
        from sales_inv_headers sih,partner_profile pp
        where sih.inv_date>add_months(sysdate,-3)
        and sih.partner_code=pp.partner_code
        and pp.partner_status is null
        group by pp.partner_code),
       pbook AS
-- Nombre de booking sur les 3 derniers mois 
(select ja.partner_code,count(*) as Nb_booking
          from sce.job_addresses ja,sce.job_headers jh
          where ja.job_reference=jh.job_reference
          and jh.available_date>add_months(sysdate,-3)
          and jh.job_status<>'9'
          group by ja.partner_code),
        pquot AS
-- Nombre de quotations sur les 3 derniers mois          
(select p.partner_code,count(*)as Nb_quo
          from quo_header qh, partners p
          where qh.crea_date > add_months(sysdate,-3)
          and (qh.rate_req_uid = p.partner_uid
              or qh.other_party_uid = p.partner_uid)
          group by p.partner_code),
          pcrm AS
-- Nombre de crm sur les 3 derniers mois
(select c.partner_code,count(*) as Nb_crm
       from crm_actions c
       where c.starting_date between add_months(sysdate,-3) and sysdate
       group by c.partner_code)          
SELECT
    tpd.partner_code,
    coalesce(a1.Nb_type, 0) AS Nb_type_part,
    coalesce(b1.Nb_ad, 0) AS Nb_ad_part,
    coalesce(c1.Nb_EDI, 0) AS Nb_EDI_part,
    coalesce(c1.Nb_INTTRA, 0) AS Nb_INTTRA_part,
    coalesce(d1.Nb_factures,0) AS Nb_fact_part,
    coalesce(e1.Nb_booking,0) AS Nb_book_part,
    coalesce(f1.Nb_quo,0) AS Nb_quo_part,
    coalesce(g1.Nb_crm,0) AS Nb_crm_part,
    tpd.dup_partner_code,
    coalesce(a2.Nb_type, 0) AS Nb_type_dup,
    coalesce(b2.Nb_ad, 0) AS Nb_ad_dup,
    coalesce(c2.Nb_EDI, 0) AS Nb_EDI_dup,
    coalesce(c2.Nb_INTTRA, 0) AS Nb_INTTRA_dup,
    coalesce(d2.Nb_factures,0) AS Nb_fact_dup,
    coalesce(e2.Nb_booking,0) AS Nb_book_dup,
    coalesce(f2.Nb_quo,0) AS Nb_quo_dup,
    coalesce(g2.Nb_crm,0) AS Nb_crm_dup
FROM
    temp_partner_duplicates tpd
    LEFT OUTER JOIN ptype a1
      ON tpd.partner_code = a1.partner_code
    LEFT OUTER JOIN pcode b1
      ON tpd.partner_code = b1.partner_code
    LEFT OUTER JOIN ptrns c1
      ON tpd.partner_code = c1.partner_code
     LEFT OUTER JOIN pfact d1
      ON tpd.partner_code = d1.partner_code
     LEFT OUTER JOIN pbook e1
      ON tpd.partner_code = e1.partner_code
     LEFT OUTER JOIN pquot f1
      ON tpd.partner_code = f1.partner_code
     LEFT OUTER JOIN pcrm g1
      ON tpd.partner_code = g1.partner_code
-- Calcul pour les partner duplicate
    LEFT OUTER JOIN ptype a2
      ON tpd.dup_partner_code = a2.partner_code
    LEFT OUTER JOIN pcode b2
      ON tpd.dup_partner_code = b2.partner_code
    LEFT OUTER JOIN ptrns c2
      ON tpd.dup_partner_code = c2.partner_code
    LEFT OUTER JOIN pfact d2
      ON tpd.dup_partner_code = d2.partner_code
    LEFT OUTER JOIN pbook e2
      ON tpd.dup_partner_code = e2.partner_code
    LEFT OUTER JOIN pquot f2
      ON tpd.dup_partner_code = f2.partner_code
    LEFT OUTER JOIN pcrm g2
      ON tpd.dup_partner_code = g2.partner_code
WHERE
    tpd.country = 'VN'

Pour faire le point, j'interroge 10 tables et voici une copie écran des statistiques sorties grâce à Oracle:

physical reads 1405197
physical writes 17600
table scans (short tables) 24
table scans (long tables) 6
table scan rows gotten 36140494
table scan blocks gotten 1059744
table fetch by rowid 5277
sorts (memory) 2
sorts (disk) 0
sorts (rows) 2234238
session logical reads 11384145
CPU used by this session 15352

Est-ce que l'on peut considéré que ma requête est optimisée?
J'ai vu aussi que l'on pouvait "visualiser le plan d'exécution de la requête". Est-ce que ça correspond à mes données ci-dessus?
Enfin, si vous avez un bon lien en ce qui concerne l'optimisation, je suis preneuse.

merci encore
claire

**mnitu** · 09/04/2009, 14h07

Envoyé par claire13

...
Est-ce que l'on peut considéré que ma requête est optimisée?
J'ai vu aussi que l'on pouvait "visualiser le plan d'exécution de la requête". Est-ce que ça correspond à mes données ci-dessus?

Si le temps qu’elle prend est acceptable par rapport à vos besoins, oui elle est optimisée, sinon il y a toujours quelque chose à faire pour l’améliorer. Exemple : utiliser des vue matérialisée pour les cumuls (count(*)) à la place du calcul à la demande (ce qui sort un peu du relationnel).

...
Enfin, si vous avez un bon lien en ce qui concerne l'optimisation, je suis preneuse.

Oracle® Database Performance Tuning Guide

Il y a en a aussi des tutoriels sur ce site.

**claire13** · 09/04/2009, 16h24

merci beaucoup.
Je me penche sur le problème.
bonne fin de journée
claire

Plusieurs boucles sur le résultat d'un curseur

PL/SQL Oracle

Discussions similaires

Partager

Partager