Bonjour,

Je sollicite votre aide aujourd'hui pour m'aider à optimiser ma requete et avoir votre avis sur ma conception.
voila mon 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
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
 
SELECT ACCPUB.NO_ACPUB,
       ACCPUB.NO_PUB,
       ACCPUB.EX_PUB,
       PRODUIT.COD_PDT, PRODUIT.LIB_COUR,
       PRODUIT.COD_FAM, FAMILLE.LIB_FAM,
       SEGMENT.COD_SEG, SEGMENT.LIB_SEG,
       FOURN.COD_FRS, FOURN.LIB_FRS, PRODUIT.REF_FOUR,
       ACCPUB.COD_MAJ <> "A" AS VALIDE_POP,
       POP_PDTPOP.NIVEAU_PUB,
       ACCPUB.QTE_BLQ, 
       POP_PDTPOP.BASE100,
       SUM(NVL(POP_PREVMAG.PREVPROP, 0)) AS PREVPROP,
       SUM(NVL(POP_PREVMAG.PREVSAISIE, 0)) AS PREVSAISIE_AVANT_DATE_BUTOIR,
       SUM(NVL(POP_PREVMAG.PREVSAISIE, CASE WHEN POP_PREVMAG.STATUTSTOCK=1 THEN NVL(POP_PREVMAG.PREVPROP, 0) ELSE 0 END)) AS PREVSAISIE_APRES_DATE_BUTOIR,
       SUM(NVL(SAS_PREVAPPRO.PREVISIONAPPRO, 0)) AS PREVCORRIGEE_AVANT_DATE_BUTOIR,
       SUM(NVL(SAS_PREVAPPRO.PREVISIONAPPRO, NVL(POP_PREVMAG.PREVSAISIE,  CASE WHEN POP_PREVMAG.STATUTSTOCK=1 THEN NVL(POP_PREVMAG.PREVPROP, 0) ELSE 0 END))) AS PREVCORRIGEE_APRES_DATE_BUTOIR,
         CASE WHEN ACCPUB.QTE_BLQ != 0 THEN ROUND((SUM(NVL(POP_PREVMAG.PREVSAISIE, 0))/ ACCPUB.QTE_BLQ) * 100, 2) ELSE NULL END AS PREVSAISIE_SUR_BASE100_AVANT_DATE_BUTOIR,
       CASE WHEN ACCPUB.QTE_BLQ != 0 THEN ROUND((SUM(NVL(POP_PREVMAG.PREVSAISIE, CASE WHEN POP_PREVMAG.STATUTSTOCK=1 THEN NVL(POP_PREVMAG.PREVPROP, 0) ELSE 0 END))/ ACCPUB.QTE_BLQ) * 100, 2) ELSE NULL END AS PREVSAISIE_SUR_BASE100_APRES_DATE_BUTOIR,
       FMK_PARAM_PDT_SAS.CODE AS ETAT_SAS,
       SAS_PDT.EXPOSITION,
       SAS_PDT.SPECIFIQUE,
       SAS_PDT.ID_PARAM_TYPE_PLAN,
       CASE WHEN (NVL(ACCPUB.UNIT_CDE, 0) = 0) THEN NVL(PRODUIT.PCB, 0) ELSE ACCPUB.UNIT_CDE END AS PCB,
       GAM_ACT.ROLE_PDT
FROM  ACCPUB
         LEFT JOIN PUB ON ACCPUB.NO_PUB = PUB.NO_PUB AND ACCPUB.EX_PUB = PUB.EX_PUB
         LEFT JOIN GAM_ACT
         ON ACCPUB.COD_PDT = GAM_ACT.COD_PDT
               AND GAM_ACT.DAT_DEB = (SELECT MAX(dat_deb) FROM GAM_ACT g1 WHERE g1.cod_pdt  = GAM_ACT.cod_pdt
                                                                                AND g1.role_pdt = GAM_ACT.role_pdt
                                                                                AND g1.dat_deb <= PUB.DAT_DEB
                                                                                AND (g1.dat_fin IS NULL OR g1.dat_fin >= PUB.DAT_FIN)),
      PRODUIT,
      FAMILLE,
      SEGMENT,
      FOURN,
      POP_CAMP_PUB,
      FMK_ETAT_HIST AS FMK_ETAT_HIST_PUB,
      POP_PDTPOP
            LEFT JOIN SAS_PDT
 
                LEFT JOIN FMK_ETAT_HIST AS FMK_ETAT_HIST_PDT_SAS ON SAS_PDT.ID_ETAT_HIST = FMK_ETAT_HIST_PDT_SAS.ID
                LEFT JOIN FMK_PARAM AS FMK_PARAM_PDT_SAS ON FMK_ETAT_HIST_PDT_SAS.ID_ETAT = FMK_PARAM_PDT_SAS.ID
            ON POP_PDTPOP.NO_ACPUB = SAS_PDT.NUMEROACCORDPUB
            LEFT JOIN POP_PREVMAG
                LEFT JOIN SAS_PREVAPPRO
                ON (POP_PREVMAG.NO_ACPUB = SAS_PREVAPPRO.NUMEROACCORDPUB AND POP_PREVMAG.COD_MAG = SAS_PREVAPPRO.COD_MAG)
            ON POP_PDTPOP.NO_ACPUB = POP_PREVMAG.NO_ACPUB
WHERE 1 = 1
      -- Jointure ACCPUB -> PRODUIT
      AND ACCPUB.COD_PDT = PRODUIT.COD_PDT
      -- Jointure PRODUIT -> FAMILLE
      AND PRODUIT.COD_FAM = FAMILLE.COD_FAM
      -- Jointure PRODUIT -> SEGMENT
      AND PRODUIT.COD_MAR = SEGMENT.COD_SEG
      -- Jointure PRODUIT -> FOURN
      AND PRODUIT.COD_FRS = FOURN.COD_FRS
      -- Jointure PUB -> POP_CAMP_PUB
      AND (PUB.NO_PUB = POP_CAMP_PUB.NO_PUB AND PUB.EX_PUB = POP_CAMP_PUB.EX_PUB)
      -- Jointure POP_CAMP_PUB -> FMK_ETAT_HIST_PUB
      AND POP_CAMP_PUB.ID_ETAT_HIST = FMK_ETAT_HIST_PUB.ID
 
      AND ACCPUB.NO_ACPUB = POP_PDTPOP.NO_ACPUB
      AND FMK_ETAT_HIST_PUB.ID_ETAT = 611
      AND ACCPUB.COD_DEST='MA'
A votre avis est ce qu'on peut optimiser cette requete ? sachat que je voudrai si c'est possible éviter de la décomposer car mon application qui utilise cette vue est compliqué.

Merci beaucoup d'avance de votre aide