Modification / Optimisation requete

**Emmanuel Lecoester** · 27/03/2007, 10h54

Bonjour messieurs dames,

J'ai deux tables

mvt (id_mvt, id_affaire, mt_mvt)
reg (id_reg, id_mvt, id_affaire, mt_reg)

Le but est de récupérer pour une affaire ses montants dans mvt mais aussi les montants dans reg sachant qu'un mouvement peut avoir 0 ou plusieurs reglements

Première idée :

Code :

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

1
2
3
4
5
select id_affaire, sum(mt_mvt), sum(mt_reg)
from mvt left outer reg on mvt.id_mvt = reg.id_mvt
                               and mvt.id_affaire = reg.id_affaire
where mvt.id_affaire = :pid
group by id_affaire ;

Sauf que là mes montants dans mvt sont multipliés par le nombre d'enregistrements de reg. => pas bien

Deuxième idée :

Code :

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

1
2
3
4
5
6
7
8
9
10
select id_affaire, (select sum(mt_mvt) 
                        from mvt
                        where mvt.id_affaire = :pid) mt_mvt,
                       (select sum(mt_reg) 
                        from reg
                        where reg.id_affaire = :pid
                        and exists (select 1 
                                       from mvt 
                                       where mvt.id_mvt = reg.id_mvt)) mt_reg
from dual ;

Là çà fonctionne mais on remarque qu'on passe deux fois sur la table mvt (c'est uen table à 20 millions d'enreg et reg à 100 millions). => requete un peu lente

Ma question : existe-t-il une méthode pour faire une sorte de "rupture" sur mvt.id_affaire du genre sum(mt_mvt distinct id_affaire)

Merci d'avance.

**McM** · 27/03/2007, 12h08

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT id_affaire, SUM(mt_mvt), SUM(mt_reg)
FROM (
	SELECT id_mvt, id_affaire, mt_mvt,
	                    (SELECT SUM(mt_reg) 
	                     FROM REG
	                     WHERE reg.id_affaire = mvt.id_affaire 
			AND reg.id_mvt 	= mvt.id_mvt) mt_reg
 
	FROM MVT
	WHERE mvt.id_affaire = :pid
	)
GROUP BY id_affaire

**Emmanuel Lecoester** · 27/03/2007, 13h24

Merci pour ta proposition, je compare les explain et je vous tiens au courant.

**Emmanuel Lecoester** · 27/03/2007, 14h16

Celà fonctionne, l'explain semble correct mais le temps d'exécution est supérieur pour récupérer toutes mes affaires (11 minutes sans rendre la main) au temps de la deuxième idée (3 minutes) :s

**salim11** · 27/03/2007, 14h43

Salut,

J'ai une petite remarque sur la solution de MCM

Code :

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

1
2
3
4
5
 
(SELECT SUM(mt_reg) 
	                     FROM REG
	                     WHERE reg.id_affaire = mvt.id_affaire 
			AND reg.id_mvt 	= mvt.id_mvt) mt_reg

A la place on met ca

Code :

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

1
2
3
4
5
6
 
(SELECT SUM(mt_reg) 
	                     FROM REG
	                     WHERE reg.id_affaire = mvt.id_affaire 
			AND reg.id_mvt 	= mvt.id_mvt
            and mvt.id_affaire = :pid) mt_reg

**Emmanuel Lecoester** · 27/03/2007, 15h43

Oui salim11 mais le but est de faire cette requete pour toute les affaires donc la solution de MCM me convient en terme syntaxique

**orafrance** · 27/03/2007, 15h49

la fonction analytique SUM me parait bien utile dans ce cas

**salim11** · 27/03/2007, 15h55

Bonjour ,

Je n'ai pas bien compris une chose, dans la requette dans la clause where
WHERE mvt.id_affaire = :pid donc tu vas chercher les montants mt_mvt et mt_reg de l'affaire :pid, Alors la mettre dans la clause where et dans la clause de la sous-requette pour moi ca va te donner uniquement les montant d'une affaire bien preçise

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT id_affaire, SUM(mt_mvt), SUM(mt_reg)
FROM (
	SELECT id_mvt, id_affaire, mt_mvt,
	                    (SELECT SUM(mt_reg) 
	                     FROM REG
	                     WHERE reg.id_affaire = mvt.id_affaire 
			AND reg.id_mvt 	= mvt.id_mvt) mt_reg
	
	FROM MVT
	WHERE mvt.id_affaire = :pid	)
GROUP BY id_affaire

**McM** · 27/03/2007, 16h13

Je me souviens que j'avais fait ceci pour une question de perf :

Code :

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

1
2
3
4
5
6
SELECT id_affaire, SUM(mt_mvt) / DECODE(COUNT(reg.idreg), 0,1, COUNT(reg.irreg)), 
				SUM(mt_reg)
FROM mvt LEFT OUTER reg ON mvt.id_mvt = reg.id_mvt
                        AND mvt.id_affaire = reg.id_affaire
WHERE mvt.id_affaire = :pid
GROUP BY id_affaire

**salim11** · 27/03/2007, 16h26

Salut ,

Si on essaie ce code

Code :

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

1
2
3
4
5
6
7
 
SELECT   id_affaire, SUM (mt_mvt) mt_mvt, (SELECT SUM (mt_reg)
                                             FROM reg
                                            WHERE :pid = id_affaire) mt_reg
    FROM mvt
   WHERE mvt.id_affaire = :pid
GROUP BY id_affaire

**McM** · 27/03/2007, 16h30

J'y avais pensé, mais si un REG correspond à une affaire sans MVT, c'est pas bon.
C'est pour ça qu'il fait un EXISTS dans sa seconde requete.

**salim11** · 27/03/2007, 16h39

salut,

J'ai compris, mais on peux rajouter une condition
id_mvt is not null, comme ca on va éliminer les reglements qui n'ont pas de mouvements

Code :

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

1
2
3
4
5
6
7
8
 
SELECT   id_affaire, SUM (mt_mvt) mt_mvt, (SELECT SUM (mt_reg)
                                             FROM reg
                                            WHERE :pid = id_affaire and 
                                             id_mvt is not null ) mt_reg
    FROM mvt
   WHERE mvt.id_affaire = :pid
GROUP BY id_affaire

**Emmanuel Lecoester** · 27/03/2007, 16h53

Envoyé par salim11

salut,

J'ai compris, mais on peux rajouter une condition
id_mvt is not null, comme ca on va éliminer les reglements qui n'ont pas de mouvements

Code :

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

1
2
3
4
5
6
7
8
 
SELECT   id_affaire, SUM (mt_mvt) mt_mvt, (SELECT SUM (mt_reg)
                                             FROM reg
                                            WHERE :pid = id_affaire and 
                                             id_mvt is not null ) mt_reg
    FROM mvt
   WHERE mvt.id_affaire = :pid
GROUP BY id_affaire

Dans ce cas là on prend tous les reg même des autres mouvements de l'affaire.

**Emmanuel Lecoester** · 27/03/2007, 16h55

Envoyé par McM

Je me souviens que j'avais fait ceci pour une question de perf :

Code :

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

1
2
3
4
5
6
SELECT id_affaire, SUM(mt_mvt) / DECODE(COUNT(reg.idreg), 0,1, COUNT(reg.irreg)), 
				SUM(mt_reg)
FROM mvt LEFT OUTER reg ON mvt.id_mvt = reg.id_mvt
                        AND mvt.id_affaire = reg.id_affaire
WHERE mvt.id_affaire = :pid
GROUP BY id_affaire

Je croyais que c'étais bon mais

Code :

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

SUM(mt_mvt) / DECODE(COUNT(reg.idreg), 0,1, COUNT(reg.irreg))

Avec :
mvt reg
100 50
100 50
200 200

donne (100+100+200) / 3 => 400/3 => 133 alors qu'il me faut 300
Il faudrait

Code :

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

SUM(mt_mvt /DECODE(COUNT(reg.idreg), 0,1, COUNT(reg.irreg)))

Sauf que là oracle n'aime pas trop

**Emmanuel Lecoester** · 27/03/2007, 16h57

Envoyé par orafrance

la fonction analytique SUM me parait bien utile dans ce cas

Je suis preneur de toute piste car j'ai beau lire le cours sur les fonctions analytiques je n'y comprend toujours rien

**salim11** · 27/03/2007, 16h58

Salut,

il va faire la somme de tous les réglements de différents mouvements (non null) pour une affaire

**Emmanuel Lecoester** · 28/03/2007, 09h40

OK. merci pour l'explication mais la requete ne passe pas le parseur : "not a single group function" sur la somme du reg

Code :

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

1
2
3
4
5
6
7
8
 
SELECT   id_affaire, SUM (mt_mvt) mt_mvt, (SELECT SUM (mt_reg)
                                             FROM reg
                                            WHERE :pid = id_affaire AND 
                                             id_mvt IS NOT NULL ) mt_reg
    FROM mvt
   WHERE mvt.id_affaire = :pid
GROUP BY id_affaire

**Emmanuel Lecoester** · 28/03/2007, 10h28

J'ai tenté les fonctions analytiques mais sans succès

Code :

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

1
2
3
4
5
6
7
 
SELECT id_affaire, 
    SUM(mt_mvt)  OVER (PARTITION BY reg.id_affaire) mt_mvt,
    SUM(mt_reg) OVER (PARTITION BY reg.id_affaire) mt_reg
FROM mvt LEFT OUTER reg ON mvt.id_mvt = reg.id_mvt
                        AND mvt.id_affaire = reg.id_affaire
WHERE mvt.id_affaire = :pid

Bien sur le montant des reglement est correct mais pas celui des mouvement car il somme toutes les lignes de id_affaire alors que je voudrait qu'il somme toutes les lignes avec le id_mvt distinct :s

Une fois de plus je n'ai pas de bol avec cette approche

**salim11** · 28/03/2007, 14h42

Salut,

Je pense il faut utiliser le group by rollup ou group by cube

essaie ce code

Code :

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

1
2
3
4
5
6
 
select mvt.id_affaire,mvt.id_mvt,reg.id_reg , sum(mt_mvt),sum(reg.mt_reg)
from mvt LEFT OUTER join   reg ON mvt.id_mvt = reg.id_mvt
                        AND mvt.id_affaire = reg.id_affaire
where  mvt.id_affaire = :pid
group by rollup(mvt.id_affaire,mvt.id_mvt,reg.id_reg)

Il me donne le résultat suivant
la somme par id_affaire , id_affaire-id_mvt-id_reg, id_affaire-id_mvt

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
ID_AFFAIRE  ID_MVT  ID_REG   SUM(MT_MVT)	SUM(REG.MT_REG)
 
10	      1	     1	        30	               1000
10	      1	     2	        30	               4000
10	      1	     3	        30	               5000
10	      1		        90	               10000
10	      2	     4	        60	               10000
10	      2		        60                     10000
10			       150	               20000
			       150                     20000

**Emmanuel Lecoester** · 28/03/2007, 16h41

Envoyé par salim11

Salut,

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
ID_AFFAIRE  ID_MVT  ID_REG   SUM(MT_MVT)	SUM(REG.MT_REG)
 
10	      1	     1	        30	               1000
10	      1	     2	        30	               4000
10	      1	     3	        30	               5000
10	      1		        90	               10000
10	      2	     4	        60	               10000
10	      2		        60                     10000
10			       150	               20000
			       150                     20000

Merci beaucoup pour ton investissement mais celà ne répond pas encore à mon besoin

. Voilà ce que je souhaiterais en me basant sur ton cas

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
ID_AFFAIRE  ID_MVT  ID_REG   SUM(MT_MVT)	SUM(REG.MT_REG)
 
10	      1	     1	        30	               1000
10	      1	     2	          	               4000
10	      1	     3	          	               5000
10	      1		        30	               10000
10	      2	     4	        60	               10000
10	      2		        60                     10000
10			       90	               20000
			       90                     20000