Optimiser une requête SQL

**sabdoul** · 14/06/2013, 12h53

Bonjour, J'ai une sql qui me donne des résultats erronés comme s'il additionnait ou multipliait le résultat plusieurs fois car les valeurs retournées sont trop élevées. or en regardant ma syntaxe je la trouvait logique.
mais on m'a dit que je multipliait le résultat primaire par le nombre de ligne de la table stock pour chaque lien.
voici la requete:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT e.CODE_PRODUIT "Code Produit",e.DESIGNATION "Designation",
to_char(NVL(round(sum(t.QTE_VEN)),0),'999G999G999G999', 'nls_numeric_characters='', ''') "Qte Vendue",
to_char(NVL(round(sum(t.VALEUR_VENTE)),0) ,'999G999G999G999', 'nls_numeric_characters='', ''') "C.A.H.T",
to_char(NVL( round(sum(t.MARGE_HT)),0) ,'999G999G999G999', 'nls_numeric_characters='', ''') "Marge HT",
to_char(NVL(round(sum(s.QUANTITE) ),0),'999G999G999G999', 'nls_numeric_characters='', ''') "Qte Stock",
to_char(NVL(round(sum(s.VALEUR_PRIX_REV) ),0),'999G999G999G999', 'nls_numeric_characters='', ''') "Valeur du stock"
FROM PRODUIT e
LEFT OUTER JOIN VENTE_JOUR t
ON(e.CODE_PRODUIT=t.CODE_PRODUIT AND t.DATE_GENERATION BETWEEN '". $_POST['date'] . "' AND '" . $_POST['date1'] . "')
LEFT OUTER JOIN STOCK s
ON(e.CODE_PRODUIT=s.CODE_PRODUIT AND s.DATE_GENERATION ='". $_POST['date1'] . "' )
GROUP BY e.CODE_PRODUIT,e.DESIGNATION ORDER BY NVL(round(sum(t.VALEUR_VENTE)),0) DESC;

donc on m'a preconnisé cette requete avec un CTE

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
WITH T AS
(
SELECT e.CODE_PRODUIT "Code Produit",e.DESIGNATION "Designation",
		 to_char(NVL(round(sum(t.QTE_VENDUE)),0),'999G999G999G999',  'nls_numeric_characters='', ''') "Qte Vendue",
		 to_char(NVL(round(sum(t.VALEUR_VENTE)),0) ,'999G999G999G999',  'nls_numeric_characters='', ''') "C.A.H.T",
		 to_char(NVL( round(sum(t.MARGE_HT)),0) ,'999G999G999G999',  'nls_numeric_characters='', ''') "Marge HT",
		 to_char(round((sum(t.VALEUR_VENTE)-sum(t.VALEUR))*100/nullif(sum(t.VALEUR_VENTE),0),2),'999G999G999G999D99',  'nls_numeric_characters='', ''') "TAUX"
FROM PRODUIT e
     LEFT OUTER JOIN VENTE_JOUR t
          ON(e.CODE_PRODUIT=t.CODE_PRODUIT) AND t.DATE_GENERATION BETWEEN '". $_POST['date'] . "' AND '" . $_POST['date1'] . "'
GROUP BY e.CODE_PRODUIT,e.DESIGNATION ORDER BY NVL(round(sum(t.VALEUR_VENTE)),0) DESC
)
SELECT T.*,
       to_char(NVL(round(sum(s.QUANTITE) ),0),'999G999G999G999',  'nls_numeric_characters='', ''') "Qte Stock",
	   to_char(NVL(round(sum(s.VALEUR_PRIX_REV) ),0),'999G999G999G999',  'nls_numeric_characters='', ''') "Valeur du stock"
FROM   T
       LEFT OUTER JOIN STOCK s 
            ON(e.CODE_PRODUIT=s.CODE_PRODUIT AND s.DATE_GENERATION ='". $_POST['date1'] . "' )
GROUP  BY T."Code Produit", T."Designation", T."Qte Vendue", T."C.A.H.T", T."Marge HT", T."TAUX"
ORDER BY NVL("C.A.H.T", 0) DESC

Cependant cette requête mais énormément de temps s'afficher(2 à 3mn) pour pratiquement 3000 produits hors le nombre de produit augmentera donc d'où ma question comment optimisé cette requête CTE ou existe t-il un autre moyen soit en améliorant ma première requête ou une autre requête sql pouvant résoudre mon problème

. merci de vos aident
Je precise que mon SGBDR est ORACLE

**skuatamad** · 14/06/2013, 13h30

Regarde : Afficher le plan d’exécution avec les stats de chaque étape pour nous présenter des informations nécessaires.

Par ailleurs

Code :

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

t.DATE_GENERATION BETWEEN '". $_POST['date'] . "' AND '" . $_POST['date1'] . "'

devrait probablement utiliser les variables de liaison sinon il faut utiliser to_date pour éviter les conversions implicites.

**sabdoul** · 14/06/2013, 16h20

merci de me repondre.Voici les statistiques:

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
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
SQL_ID  4gn6qcvaw4agm, child number 0
-------------------------------------
WITH T AS/*+ GATHER_PLAN_STATISTICS */ ( SELECT e.CODE_PRODUIT "Code
Produit",e.DESIGNATION "Designation",
to_char(NVL(round(sum(t.QTE_VENDUE)),0),'999G999G999G999',
'nls_numeric_characters='', ''') "Qte Vendue",
to_char(NVL(round(sum(t.VALEUR_VENTE)),0) ,'999G999G999G999',
'nls_numeric_characters='', ''') "C.A.H.T",    to_char(NVL(
round(sum(t.MARGE_HT)),0) ,'999G999G999G999',
'nls_numeric_characters='', ''') "Marge HT",
to_char(round((sum(t.VALEUR_VENTE)-sum(t.VALEUR))*100/nullif(sum(t.VALEU
R_VENTE),0),2),'999G999G999G999D99',  'nls_numeric_characters='', ''')
"TAUX" FROM PRODUIT1 e      LEFT OUTER JOIN VENTE_JOUR t
ON(e.CODE_PRODUIT=t.CODE_PRODUIT) AND t.DATE_GENERATION BETWEEN
'01/06/2013' AND '09/06/2013' GROUP BY e.CODE_PRODUIT,e.DESIGNATION
ORDER BY NVL(round(sum(t.VALEUR_VENTE)),0) DESC ) SELECT T.*,
to_char(NVL(round(sum(s.QUANTITE) ),0),'999G999G999G999',
'nls_numeric_characters='', ''') "Qte Stock",
to_char(NVL(round(sum(s.VALEUR_PRIX_REV) )
 
Plan hash value: 3137523657
 
--------------------------------------------------------------------------------
--------
| Id  | Operation                     | Name       | E-Rows |  OMem |  1Mem | Us
ed-Mem |
--------------------------------------------------------------------------------
--------
|   0 | SELECT STATEMENT              |            |        |       |       |
       |
|   1 |  SORT ORDER BY                |            |   2512 |   690K|   486K|  6
13K (0)|
|   2 |   HASH GROUP BY               |            |   2512 |  1049K|   933K| 12
47K (0)|
|*  3 |    HASH JOIN RIGHT OUTER      |            |   2512 |  2613K|  1130K| 37
75K (0)|
|*  4 |     TABLE ACCESS FULL         | STOCK      |    864 |       |       |
       |
|   5 |     VIEW                      |            |   2512 |       |       |
       |
|   6 |      SORT ORDER BY            |            |   2512 |   478K|   448K|  4
24K (0)|
|   7 |       HASH GROUP BY           |            |   2512 |   998K|   939K| 12
76K (0)|
|   8 |        VIEW                   |            |     19M|       |       |
       |
|   9 |         HASH GROUP BY         |            |     19M|    47M|  3695K|
67M (0)|
|* 10 |          HASH JOIN RIGHT OUTER|            |     19M|    20M|  3873K|
24M (0)|
|* 11 |           TABLE ACCESS FULL   | VENTE_JOUR |    153K|       |       |
       |
|  12 |           TABLE ACCESS FULL   | VENTES     |    382K|       |       |
       |
--------------------------------------------------------------------------------
--------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T"."Code Produit"="S"."CODE_PRODUIT")
   4 - filter("S"."DATE_GENERATION"=TO_DATE(' 2013-06-09 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))
  10 - access("CODE_PRODUIT"="T"."CODE_PRODUIT")
  11 - filter(("T"."DATE_GENERATION"<=TO_DATE(' 2013-06-09 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "T"."DATE_GENERATION">=TO_DATE(' 201
3-06-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when
:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system leve
l
 
 
57 ligne(s) sÚlectionnÚe(s).

**skuatamad** · 14/06/2013, 16h36

Le hint est mal placé, je le mettrais dans le dernier select appelé, comme ceci :

Code :

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

1
2
3
....
SELECT /*+ GATHER_PLAN_STATISTICS */ T.*,
.....

**sabdoul** · 14/06/2013, 21h13

Salut, j'étais absent voici les statistiques après le

Code :

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

SELECT /*+ GATHER_PLAN_STATISTICS */ 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
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
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
SQL_ID  6absk87znxndt, child number 0
-------------------------------------
WITH T AS ( SELECT e.CODE_PRODUIT "Code Produit",e.DESIGNATION
"Designation",    to_char(NVL(round(sum(t.QTE_VENDUE)),0),'999G999G999G9
99',  'nls_numeric_characters='', ''') "Qte Vendue",
to_char(NVL(round(sum(t.VALEUR_VENTE)),0) ,'999G999G999G999',
'nls_numeric_characters='', ''') "C.A.H.T",    to_char(NVL(
round(sum(t.MARGE_HT)),0) ,'999G999G999G999',
'nls_numeric_characters='', ''') "Marge HT",
to_char(round((sum(t.VALEUR_VENTE)-sum(t.VALEUR))*100/nullif(sum(t.VALEU
R_VENTE),0),2),'999G999G999G999D99',  'nls_numeric_characters='', ''')
"TAUX" FROM PRODUIT1 e      LEFT OUTER JOIN VENTE_JOUR t
ON(e.CODE_PRODUIT=t.CODE_PRODUIT) AND t.DATE_GENERATION BETWEEN
'01/06/2013' AND '09/06/2013' GROUP BY e.CODE_PRODUIT,e.DESIGNATION
ORDER BY NVL(round(sum(t.VALEUR_VENTE)),0) DESC ) SELECT /*+
GATHER_PLAN_STATISTICS */ T.*,        to_char(NVL(round(sum(s.QUANTITE)
),0),'999G999G999G999',  'nls_numeric_characters='', ''') "Qte Stock",
   to_char(NVL(round(sum(s.VALEUR_PRIX_REV)
 
Plan hash value: 3137523657
 
--------------------------------------------------------------------------------
----------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows | A-Rows |
  A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------
----------------------------------------------------------
|   0 | SELECT STATEMENT              |            |      1 |        |   2970 |0
0:02:20.27 |   16880 |  16864 |       |       |          |
|   1 |  SORT ORDER BY                |            |      1 |   2512 |   2970 |0
0:02:20.27 |   16880 |  16864 |   690K|   486K|  613K (0)|
|   2 |   HASH GROUP BY               |            |      1 |   2512 |   2970 |0
0:02:20.25 |   16880 |  16864 |  1049K|   933K| 1276K (0)|
|*  3 |    HASH JOIN RIGHT OUTER      |            |      1 |   2512 |  58027 |0
0:02:20.21 |   16880 |  16864 |  2613K|  1130K| 3758K (0)|
|*  4 |     TABLE ACCESS FULL         | STOCK      |      1 |    864 |  58908 |0
0:00:00.84 |    9860 |   9855 |       |       |          |
|   5 |     VIEW                      |            |      1 |   2512 |   2970 |0
0:02:19.29 |    7020 |   7009 |       |       |          |
|   6 |      SORT ORDER BY            |            |      1 |   2512 |   2970 |0
0:02:19.29 |    7020 |   7009 |   478K|   448K|  424K (0)|
|   7 |       HASH GROUP BY           |            |      1 |   2512 |   2970 |0
0:02:19.26 |    7020 |   7009 |   998K|   939K| 1276K (0)|
|   8 |        VIEW                   |            |      1 |     19M|    198K|0
0:02:18.98 |    7020 |   7009 |       |       |          |
|   9 |         HASH GROUP BY         |            |      1 |     19M|    198K|0
0:02:18.90 |    7020 |   7009 |    47M|  3695K|   67M (0)|
|* 10 |          HASH JOIN RIGHT OUTER|            |      1 |     19M|     74M|0
0:00:51.22 |    7020 |   7009 |    20M|  3873K|   24M (0)|
|* 11 |           TABLE ACCESS FULL   | VENTE_JOUR |      1 |    153K|    197K|0
0:00:01.33 |    1934 |   1928 |       |       |          |
|  12 |           TABLE ACCESS FULL   | VENTES     |      1 |    382K|    497K|0
0:00:00.44 |    5086 |   5081 |       |       |          |
--------------------------------------------------------------------------------
----------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T"."Code Produit"="S"."CODE_PRODUIT")
   4 - filter("S"."DATE_GENERATION"=TO_DATE(' 2013-06-09 00:00:00', 'syyyy-mm-dd
 hh24:mi:ss'))
  10 - access("CODE_PRODUIT"="T"."CODE_PRODUIT")
  11 - filter(("T"."DATE_GENERATION"<=TO_DATE(' 2013-06-09 00:00:00', 'syyyy-mm-
dd hh24:mi:ss') AND
              "T"."DATE_GENERATION">=TO_DATE(' 2013-06-01 00:00:00', 'syyyy-mm-d
d hh24:mi:ss')))

merci d'avance de me repondre

**sabdoul** · 16/06/2013, 12h55

Salut, je n'ai pas encore de reponse? en ensperant avoir une aide je vient de faire une autre remarque les CTE etant comme une vue temporaire, j'ai cree une vue avec la CTE

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 WITH T AS
(
SELECT e.CODE_PRODUIT "Code Produit",e.DESIGNATION "Designation",
		 to_char(NVL(round(sum(t.QTE_VENDUE)),0),'999G999G999G999',  'nls_numeric_characters='', ''') "Qte Vendue",
		 to_char(NVL(round(sum(t.VALEUR_VENTE)),0) ,'999G999G999G999',  'nls_numeric_characters='', ''') "C.A.H.T",
		 to_char(NVL( round(sum(t.MARGE_HT)),0) ,'999G999G999G999',  'nls_numeric_characters='', ''') "Marge HT",
		 to_char(round((sum(t.VALEUR_VENTE)-sum(t.VALEUR))*100/nullif(sum(t.VALEUR_VENTE),0),2),'999G999G999G999D99',  'nls_numeric_characters='', ''') "TAUX"
FROM PRODUIT e
     LEFT OUTER JOIN VENTE_JOUR t
          ON(e.CODE_PRODUIT=t.CODE_PRODUIT) AND t.DATE_GENERATION BETWEEN '". $_POST['date'] . "' AND '" . $_POST['date1'] . "'
GROUP BY e.CODE_PRODUIT,e.DESIGNATION ORDER BY NVL(round(sum(t.VALEUR_VENTE)),0) DESC
)

c'est à dire cette vue

Code :

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

1
2
3
4
5
6
7
8
9
10
11
create or replace view AS
 
SELECT e.CODE_PRODUIT "Code Produit",e.DESIGNATION "Designation",
		 to_char(NVL(round(sum(t.QTE_VENDUE)),0),'999G999G999G999',  'nls_numeric_characters='', ''') "Qte Vendue",
		 to_char(NVL(round(sum(t.VALEUR_VENTE)),0) ,'999G999G999G999',  'nls_numeric_characters='', ''') "C.A.H.T",
		 to_char(NVL( round(sum(t.MARGE_HT)),0) ,'999G999G999G999',  'nls_numeric_characters='', ''') "Marge HT",
		 to_char(round((sum(t.VALEUR_VENTE)-sum(t.VALEUR))*100/nullif(sum(t.VALEUR_VENTE),0),2),'999G999G999G999D99',  'nls_numeric_characters='', ''') "TAUX"
FROM PRODUIT e
     LEFT OUTER JOIN VENTE_JOUR t
          ON(e.CODE_PRODUIT=t.CODE_PRODUIT) AND t.DATE_GENERATION BETWEEN '". $_POST['date'] . "' AND '" . $_POST['date1'] . "'
GROUP BY e.CODE_PRODUIT,e.DESIGNATION ORDER BY NVL(round(sum(t.VALEUR_VENTE)),0) DESC

et ensuite j'ai fait une jointure avec la deuxieme

Code :

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

1
2
3
4
5
6
7
8
SELECT T.*,
       to_char(NVL(round(sum(s.QUANTITE) ),0),'999G999G999G999',  'nls_numeric_characters='', ''') "Qte Stock",
	   to_char(NVL(round(sum(s.VALEUR_PRIX_REV) ),0),'999G999G999G999',  'nls_numeric_characters='', ''') "Valeur du stock"
FROM   T
       LEFT OUTER JOIN STOCK s 
            ON(e.CODE_PRODUIT=s.CODE_PRODUIT AND s.DATE_GENERATION ='". $_POST['date1'] . "' )
GROUP  BY T."Code Produit", T."Designation", T."Qte Vendue", T."C.A.H.T", T."Marge HT", T."TAUX"
ORDER BY NVL("C.A.H.T", 0) DESC

Et là je constate que ma requette s'execute en moins de 5 seconde. d'où ma question oracle ne passe bien avec les CTE ou cmment regler cela. merci

**skuatamad** · 17/06/2013, 10h49

Il y a un problème dans l'affichage du plan :
Il y a 3 tables accédées: STOCK, VENTE_JOUR, VENTES alors que dans la requête il n'y a pas la table VENTES mais une table PRODUIT.
Sinon les stats ne semblent pas à jour, regarde DBMS_STATS pour les recalculer.
Combien y a t'il de lignes dans les tables STOCK et VENTE_JOUR ?
Y a t'il des index sur les dates ?

c'est à dire cette vue

Je doute que la vue créée contienne les variables php $_POST.

je n'ai pas encore de reponse?

C'est un forum d'entre aide bénévole, et le WE il peut y avoir moins de contributions.

**Mohamed.Houri** · 17/06/2013, 12h09

Bonjour

1) quelle est la version d'Oracle utilisée
2) pouvez-vous poster le plan d'exécution avec la vue créée (create or replace view)
3) voici votre plan formaté:

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
 
Plan hash value: 3137523657
 
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows | A-Rows |A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |      1 |        |   2970 |00:02:20.27 |   16880 |  16864 |       |       |          |
|   1 |  SORT ORDER BY                |            |      1 |   2512 |   2970 |00:02:20.27 |   16880 |  16864 |   690K|   486K|  613K (0)|
|   2 |   HASH GROUP BY               |            |      1 |   2512 |   2970 |00:02:20.25 |   16880 |  16864 |  1049K|   933K| 1276K (0)|
|*  3 |    HASH JOIN RIGHT OUTER      |            |      1 |   2512 |  58027 |00:02:20.21 |   16880 |  16864 |  2613K|  1130K| 3758K (0)|
|*  4 |     TABLE ACCESS FULL         | STOCK      |      1 |    864 |  58908 |00:00:00.84 |    9860 |   9855 |       |       |          |
|   5 |     VIEW                      |            |      1 |   2512 |   2970 |00:02:19.29 |    7020 |   7009 |       |       |          |
|   6 |      SORT ORDER BY            |            |      1 |   2512 |   2970 |00:02:19.29 |    7020 |   7009 |   478K|   448K|  424K (0)|
|   7 |       HASH GROUP BY           |            |      1 |   2512 |   2970 |00:02:19.26 |    7020 |   7009 |   998K|   939K| 1276K (0)|
|   8 |        VIEW                   |            |      1 |     19M|    198K|00:02:18.98 |    7020 |   7009 |       |       |          |
|   9 |         HASH GROUP BY         |            |      1 |     19M|    198K|00:02:18.90 |    7020 |   7009 |    47M|  3695K|   67M (0)|
|* 10 |          HASH JOIN RIGHT OUTER|            |      1 |     19M|     74M|00:00:51.22 |    7020 |   7009 |    20M|  3873K|   24M (0)|
|* 11 |           TABLE ACCESS FULL   | VENTE_JOUR |      1 |    153K|    197K|00:00:01.33 |    1934 |   1928 |       |       |          |
|  12 |           TABLE ACCESS FULL   | VENTES     |      1 |    382K|    497K|00:00:00.44 |    5086 |   5081 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   3 - access("T"."Code Produit"="S"."CODE_PRODUIT")
   4 - filter("S"."DATE_GENERATION"=TO_DATE(' 2013-06-09 00:00:00', 'syyyy-mm-ddhh24:mi:ss'))
  10 - access("CODE_PRODUIT"="T"."CODE_PRODUIT")
  11 - filter(("T"."DATE_GENERATION"<=TO_DATE(' 2013-06-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "T"."DATE_GENERATION">=TO_DATE(' 2013-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

C'est l'opération n° 9 HASH GROUP BY qui semble consommer le plus de temps 02:18.90.
Pourquoi?
Il y a déjà une forte différence entre E-Rows et A-Rows pour cette opération. Ce qui pourrait conduire le CBO a choisir un chemin non optimal. Avec le nouveau plan d'exécution (qui dure quelques secondes), on pourrait avoir une meilleure vue

**sabdoul** · 18/06/2013, 11h02

Merci de me répondre, excusez moi de vous répondre tardivement j'étais un peu souffrant, pour repondre a skuatamad effectivement la table produit est une vue créée à partir de la table ventes. aussi la vue créee avec les variables php $_POST sont bien créée car elle ne prenne que les valeurs des variables php $_POST qui sont ensuite envoyés a la BDD.
mais l'inconvenient c'est de toujours récréer cette vue avant de faire une jointure. car j'aurais aimé utiliser une CTE qui est comme une vue temporaire ou n'est-il pas possible de régler la multipliction des lignes primaires par le nombre de ligne lorsque je fais une jointure externe c'est à dire ma première requette qui me donnait des valeurs élevées

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT e.CODE_PRODUIT "Code Produit",e.DESIGNATION "Designation",
to_char(NVL(round(sum(t.QTE_VEN)),0),'999G999G999G999', 'nls_numeric_characters='', ''') "Qte Vendue",
to_char(NVL(round(sum(t.VALEUR_VENTE)),0) ,'999G999G999G999', 'nls_numeric_characters='', ''') "C.A.H.T",
to_char(NVL( round(sum(t.MARGE_HT)),0) ,'999G999G999G999', 'nls_numeric_characters='', ''') "Marge HT",
to_char(NVL(round(sum(s.QUANTITE) ),0),'999G999G999G999', 'nls_numeric_characters='', ''') "Qte Stock",
to_char(NVL(round(sum(s.VALEUR_PRIX_REV) ),0),'999G999G999G999', 'nls_numeric_characters='', ''') "Valeur du stock"
FROM PRODUIT e
LEFT OUTER JOIN VENTE_JOUR t
ON(e.CODE_PRODUIT=t.CODE_PRODUIT AND t.DATE_GENERATION BETWEEN '". $_POST['date'] . "' AND '" . $_POST['date1'] . "')
LEFT OUTER JOIN STOCK s
ON(e.CODE_PRODUIT=s.CODE_PRODUIT AND s.DATE_GENERATION ='". $_POST['date1'] . "' )
GROUP BY e.CODE_PRODUIT,e.DESIGNATION ORDER BY NVL(round(sum(t.VALEUR_VENTE)),0) DESC;

.

concernant les questions de Mohamed.Houri ma version d'oracle une version 11g express.
pour le plan d'exécution je ne comprend pas bien ce que vous voulez dire par là.
sinon j'ai une fonction que j'ai créer qui se charge de créer la vue avant que je ne fasse une jointure. cependant si c'est pour les les stastiques de la créetion de la vue les voici:

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
SQL> create or replace view T as /*+ GATHER_PLAN_STATISTICS */
  2  SELECT e.CODE_PRODUIT "Code Produit",e.DESIGNATION "Designation",
  3              to_char(NVL(round(sum(t.QTE_VENDUE)),0),'999G999G999G999',  'nl
s_numeric_characters='', ''') "Qte Vendue",
  4              to_char(NVL(round(sum(t.VALEUR_VENTE)),0) ,'999G999G999G999',
'nls_numeric_characters='', ''') "C.A.H.T",
  5              to_char(NVL( round(sum(t.MARGE_HT)),0) ,'999G999G999G999',  'nl
s_numeric_characters='', ''') "Marge HT",
  6              to_char(round((sum(t.VALEUR_VENTE)-sum(t.VALEUR))*100/nullif(su
m(t.VALEUR_VENTE),0),2),'999G999G999G999D99',  'nls_numeric_characters='', ''')
"TAUX"
  7  FROM PRODUIT1 e
  8       LEFT OUTER JOIN VENTE_JOUR t
  9            ON(e.CODE_PRODUIT=t.CODE_PRODUIT) AND t.DATE_GENERATION between '
01/06/2013' and '09/06/2013'
 10  GROUP BY e.CODE_PRODUIT,e.DESIGNATION ORDER BY NVL(round(sum(t.VALEUR_VENTE
)),0) DESC;
 
Vue crÚÚe.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
SQL_ID  9r2qc5a9jnnfv, child number 0
 
An uncaught error happened in prepare_sql_statement : ORA-01403: aucune donnÚe t
 
rouvÚe
 
 
NOTE: cannot fetch plan for SQL_ID: 9r2qc5a9jnnfv, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_p
 
lan)
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
 
 
8 ligne(s) sÚlectionnÚe(s).

Merci de vos aident

**skuatamad** · 18/06/2013, 13h21

produit est une vue créée à partir de la table ventes

Peut on voir le script de création de la vue.

Par ailleurs est il possible de connaitre les nombres de lignes des tables impliquées STOCK, VENTES et VENTE_JOUR ?
Y a t'il des index sur les dates ?
Il faudrait réexécuter la requête avec le hint GATHER_PLAN_STATISTICS après avoir recalculé les stats.

**Mohamed.Houri** · 18/06/2013, 14h20

pour le plan d'exécution je ne comprend pas bien ce que vous voulez dire par là.

Vous avez écrit qu'après avoir créé la vue T votre requête

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT T.*,
       to_char(NVL(round(sum(s.QUANTITE) ),0),'999G999G999G999',  'nls_numeric_characters='', ''') "Qte Stock",
	   to_char(NVL(round(sum(s.VALEUR_PRIX_REV) ),0),'999G999G999G999',  'nls_numeric_characters='', ''') "Valeur du stock"
FROM   T
       LEFT OUTER JOIN STOCK s 
            ON(e.CODE_PRODUIT=s.CODE_PRODUIT AND s.DATE_GENERATION ='". $_POST['date1'] . "' )
GROUP  BY T."Code Produit", T."Designation", T."Qte Vendue", T."C.A.H.T", T."Marge HT", T."TAUX"
ORDER BY NVL("C.A.H.T", 0) DESC

s'est exécutée en 5 secondes.

Pourriez-vous alors poster le plan d’exécution correspondant (toujours avec E-Rows et A-Rows)

**pachot** · 18/06/2013, 16h26

Bonjour,

Il ne faut pas abandonner votre première requête si vite.
D'abord parce que c'est celle qui vous paraît la plus facile à comprendre. Ensuite parce qu'elle vous paraît donner le meilleurs performances.
Et de toute façon si vous ne comprenez pas d'où viennent les résultats erronés vous retrouverz le même problème plus tard.

Vous avez des résultats erronés parce que la jointure avec les ventes donne plusieurs lignes par produit. C'est normal d'additioner les infos de vente, mais il ne faut prendre qu'une seule fois les infos de stock.

Lançez la requête sans group by, pour un produit et vous verrez le stock répété sur chaque ligne. Vous ne devez pas le sommer.

Remplacez sum(s.QUANTITE) et sum(s.VALEUR_PRIX_REV) par max(s.QUANTITE) et max(s.VALEUR_PRIX_REV). Vous pouvez choisir min,max ou avg c'est pareil: il y a la même valeur dans chaque group du group by.

Maintenant à propos des perfs. Vu que vous allez voir toutes les ventes afin de déterminer la quantité vendue, et pour tous les produits, ce n'est pas anormal de faire des FULL SCAN. Vous avez 3000 produits et ça va augmenter. Le nombre de ventes sur chaque produit va augmenter aussi.

Cependant cette requête mais énormément de temps s'afficher(2 à 3mn) pour pratiquement 3000 produits

Vous affichez 3000 lignes à l'écran ? Alors 2 minutes c'est rien par rapport au temps pour les lire (et pour aller chercher des lunettes parce que 3000 lignes sur un écran...). Si par contre il s'agit de n'afficher en premier que la première page des 10 produits avec les meilleures vantes, alors il y a d'autres moyens de faire la requête, en FIRST_ROWS par exemple.

Cordialement,
Franck.

**Mohamed.Houri** · 18/06/2013, 17h20

Franck,

En regardant le plan d’exécution, je remarque qu’il fait (opérations 9-10-11-12) une jointure suivie par une agrégation. (HASH JOIN + HASH GROUP BY). L’opération HASH JOIN RIGHT OUTER, au lieu d’éliminer des données elle en augmente en passant à 74 millions tout en laissant le soin à l'opération HASH GROUP BY d'éliminer la presque totalité des 74 millions de lignes pour n'en laisser que 198K lignes. C'est justement cette élimination qui consomme le plus de temps.

Il serait peut-être préférable de passer dans ce cas à une opération inverse : GROUP BY suivi d’un HASH JOIN. C’est pour cette raison que j’ai demandé à avoir le ’’bon’’ plan d’exécution pour voir si cette inversion y a eu lieu.

Quant au mode FIRST_ROWS, il faut, si cela convient dans ce cas, plutôt lui préférer le hint FIRST_ROWS_10

**pachot** · 18/06/2013, 23h39

Bonjour Mohamed,

Envoyé par Mohamed.Houri

L’opération HASH JOIN RIGHT OUTER, au lieu d’éliminer des données elle en augmente en passant à 74 millions

Oui. Il faudrait voir la définition de la vue PRODUIT sur la table VENTE. Il doit y avoir un DISTINCT ou GROUP BY dedans puis qu'on fait la jointure seulement avec le produit. Et si c'est le cas, on devrait le voir dans le plan avant la jointure et non après. Peut-être que dans PRODUIT il y a plusieurs lignes par produit, et dans ce cas c'est une clause de jointure qui manque.

**pacmann** · 19/06/2013, 09h02

Salut,

Il y a du coup surtout un problème soit de modèle, soit de requête.
Pourquoi produits = ventes ?
Et pourquoi aucun filtre ni group by n'est directement appliqué sur cette table vente au niveau de la vue ?

Idéalement, il faut une vraie table produit. A défaut, inclure le group by dans la vue produit pour qu'elle représente réellement les produits...

**sabdoul** · 19/06/2013, 11h58

Salut, merci a tous de me repondre, pour repondre d'abord a skuatamad ,voici la requete de la création de la vue

Code :

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

1
2
3
4
create or replace view produit1(code_produit,designation) as
select distinct code_produit,max(designation)
from ventes 
group by code_produit;

j'ai un

Code :

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

max(designation)

au niveau de la vue car il y'a une legère différence entre les designations des différents sucursales par conséquent plusieurs désignations possible pour un seul code de produit. car la base de données est une base qui doit centraliser les données de plusieurs surcusales. pour le nombres de lignes impliquées STOCK, VENTES et VENTE_JOUR elle varie en fontion des variables et il n'y pas d'index sur les dates et aussi pour repondre a Mohamed.Houri voici les statistiques avec le hint après la requete

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
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
3031 ligne(s) sÚlectionnÚe(s).
 
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
SQL_ID  bb5a526kfk40j, child number 0
-------------------------------------
SELECT /*+ hint GATHER_PLAN_STATISTICS */T.*,
to_char(NVL(round(sum(s.QUANTITE) ),0),'999G999G999G999','nls_numeric_ch
aracters='', ''') "QteStock",     to_char(NVL(round(sum(s.VALEUR_PRIX_RE
V) ),0),'999G999G999G999','nls_numeric_characters='', ''')
"Valeurdustock" FROM T        LEFT OUTER JOIN STOCK s
ON(T."Code Produit"=s.CODE_PRODUIT AND s.DATE_GENERATION ='09/06/2013'
) GROUP  BY T."Code Produit", T."Designation", T."Qte Vendue",
T."C.A.H.T", T."Marge HT", T."TAUX" ORDER BY NVL("C.A.H.T", 0) DESC
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
Plan hash value: 3464146926
 
--------------------------------------------------------------------------------
 
------------------------------------------------------
 
| Id  | Operation                 | Name       | Starts | E-Rows | A-Rows |   A-
 
Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
 
--------------------------------------------------------------------------------
 
------------------------------------------------------
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
|   0 | SELECT STATEMENT          |            |      1 |        |   3031 |00:00
 
:02.78 |   26722 |  26711 |       |       |          |
 
|   1 |  SORT ORDER BY            |            |      1 |   2652 |   3031 |00:00
 
:02.78 |   26722 |  26711 |   690K|   486K|  613K (0)|
 
|   2 |   HASH GROUP BY           |            |      1 |   2652 |   3031 |00:00
 
:02.76 |   26722 |  26711 |  1049K|   933K| 1263K (0)|
 
|*  3 |    HASH JOIN OUTER        |            |      1 |  55709 |  85067 |00:00
 
:02.79 |   26722 |  26711 |   951K|   951K| 1279K (0)|
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
 
|   4 |     VIEW                  | T          |      1 |   2652 |   3031 |00:00
 
:01.34 |   11475 |  11468 |       |       |          |
 
|   5 |      HASH GROUP BY        |            |      1 |   2652 |   3031 |00:00
 
:01.33 |   11475 |  11468 |   998K|   939K| 1272K (0)|
 
|*  6 |       HASH JOIN OUTER     |            |      1 |    223K|    234K|00:00
 
:01.75 |   11475 |  11468 |   778K|   778K| 1328K (0)|
 
|   7 |        VIEW               | PRODUIT1   |      1 |   3017 |   3031 |00:00
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
:00.85 |    8396 |   8392 |       |       |          |
 
|   8 |         HASH GROUP BY     |            |      1 |   3017 |   3031 |00:00
 
:00.85 |    8396 |   8392 |  1057K|   940K| 2712K (0)|
 
|   9 |          TABLE ACCESS FULL| VENTES     |      1 |    687K|    820K|00:00
 
:00.24 |    8396 |   8392 |       |       |          |
 
|* 10 |        TABLE ACCESS FULL  | VENTE_JOUR |      1 |    223K|    234K|00:00
 
:00.70 |    3079 |   3076 |       |       |          |
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
|* 11 |     TABLE ACCESS FULL     | STOCK      |      1 |  82997 |  86156 |00:00
 
:00.82 |   15247 |  15243 |       |       |          |
 
--------------------------------------------------------------------------------
 
------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T"."Code Produit"="S"."CODE_PRODUIT")
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
   6 - access("E"."CODE_PRODUIT"="T"."CODE_PRODUIT")
  10 - filter(("T"."DATE_GENERATION"<=TO_DATE(' 2013-06-09 00:00:00', 'syyyy-mm-
 
dd hh24:mi:ss') AND
 
              "T"."DATE_GENERATION">=TO_DATE(' 2013-06-01 00:00:00', 'syyyy-mm-d
 
d hh24:mi:ss')))
 
  11 - filter("S"."DATE_GENERATION"=TO_DATE(' 2013-06-09 00:00:00', 'syyyy-mm-dd
 
 hh24:mi:ss'))
 
 
 
39 ligne(s) sÚlectionnÚe(s).

**sabdoul** · 19/06/2013, 12h08

La remarque de pachot me parait interressante car c'est cette première requette que j'aimerais utiliser cependant en faisant des Remplacements de sum(s.QUANTITE) et sum(s.VALEUR_PRIX_REV) par max(s.QUANTITE) et max(s.VALEUR_PRIX_REV) est-ce que je n'obtinedrai que la valeur maximun des quantités et non la somme idem pour les VALEUR_PRIX_REV. aussi pour l'affichage des 3000 produits à l'ecran oui cela est trop mais ce qui a été demandé car ils veulent avoir les stats de tous les produits et une fenêtre de recherche permetant d'afficher les lignes qu'on veut en reduisant ainsi le nombre de produit afficher a l'écran.

Aussi comme indiquer dans le premier post il y'a effectivement plusieurs lignes de designations par produit. voici la vue

Code :

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

1
2
3
4
create or replace view produit(code_produit,designation) as
select distinct code_produit,max(designation)
from ventes 
group by code_produit;

**sabdoul** · 19/06/2013, 13h15

Envoyé par Mohamed.Houri

Franck,

En regardant le plan d’exécution, je remarque qu’il fait (opérations 9-10-11-12) une jointure suivie par une agrégation. (HASH JOIN + HASH GROUP BY). L’opération HASH JOIN RIGHT OUTER, au lieu d’éliminer des données elle en augmente en passant à 74 millions tout en laissant le soin à l'opération HASH GROUP BY d'éliminer la presque totalité des 74 millions de lignes pour n'en laisser que 198K lignes. C'est justement cette élimination qui consomme le plus de temps.

Il serait peut-être préférable de passer dans ce cas à une opération inverse : GROUP BY suivi d’un HASH JOIN. C’est pour cette raison que j’ai demandé à avoir le ’’bon’’ plan d’exécution pour voir si cette inversion y a eu lieu.

Quant au mode FIRST_ROWS, il faut, si cela convient dans ce cas, plutôt lui préférer le hint FIRST_ROWS_10

comment je pourrai inverser l'ordre des operation c'est à dire

Code :

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

GROUP BY suivi d’un HASH JOIN

car la syntaxe ne permet pas cela ou c'est moi qui ne sait pas m'y prendre

aussi je suis obligé d'effectuer des groupages du faites de la présence des fonctions agregat sum... dans le select

**skuatamad** · 19/06/2013, 13h25

Qu'est ce que ça donne écrit comme ça :

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
  WITH vj AS (
select code_produit,
       to_char(NVL(round(sum(QTE_VENDUE)),0),'999G999G999G999',  'nls_numeric_characters='', ''') "Qte Vendue",
       to_char(NVL(round(sum(VALEUR_VENTE)),0) ,'999G999G999G999',  'nls_numeric_characters='', ''') "C.A.H.T",
       to_char(NVL( round(sum(MARGE_HT)),0) ,'999G999G999G999',  'nls_numeric_characters='', ''') "Marge HT",
       to_char(round((sum(VALEUR_VENTE)-sum(VALEUR))*100/nullif(sum(VALEUR_VENTE),0),2),'999G999G999G999D99',  'nls_numeric_characters='', ''') "TAUX"
  from VENTE_JOUR
 where DATE_GENERATION BETWEEN :date AND :date1
 group by code_produit
),
       s as (
select code_produit,
       to_char(NVL(round(sum(QUANTITE) ),0),'999G999G999G999',  'nls_numeric_characters='', ''') "Qte Stock",
       to_char(NVL(round(sum(VALEUR_PRIX_REV) ),0),'999G999G999G999',  'nls_numeric_characters='', ''') "Valeur du stock"
  from STOCK
 where DATE_GENERATION = :date1
 group by code_produit 
)
select p.code_produit, p.DESIGNATION,
       vj.*, s.*
  from produit p
  left join vj on vj.code_produit = p.code_produit
  left join s  on  s.code_produit = p.code_produit
 order by NVL("C.A.H.T", 0) DESC