Discussion :

[claire13]

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]

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]

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]

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]

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?

[Waldar]

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.

[orafrance]

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

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...