Discussion :

[sabdoul]

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]

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]

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]

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]

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]

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