Bonjour,

J'aimerais optimiser une requête.

Voici ma table et qqs données:

Bon, le code est trop long, je peux vous le fournir en MP.


voici maintenant ma requête :
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
130
131
132
133
134
135
136
 
SELECT 
       '01/04/2005' as Date_Calcul,
       selTot.RatedIssuers,
       selTot.RatedIssuersPonderes,
       selUp.IssuerUpgrades, 
       selUp.IssuerUpgradesPonderes, 
       selDown.IssuerDowngrades, 
       selDown.IssuerDowngradesPonderes,
       round((selUp.IssuerUpgrades-selDown.IssuerDowngrades)/selTot.RatedIssuers,3) as RatingsDrift,
       round((selUp.IssuerUpgradesPonderes-selDown.IssuerDowngradesPonderes)/selTot.RatedIssuersPonderes,3) as RatingsDriftPondere,
       round((selUp.IssuerUpgrades+selDown.IssuerDowngrades)/selTot.RatedIssuers,3) as RatingsTurnover,
       round((selUp.IssuerUpgradesPonderes+selDown.IssuerDowngradesPonderes)/selTot.RatedIssuersPonderes,3) as RatingsTurnoverPondere
 
FROM
 
(
 
SELECT count (*) as RatedIssuers, 
       sum(nvl(selAll.c_notche,1)) as RatedIssuersPonderes
FROM
 
      (SELECT * 
      FROM rdt_fs_histo_sp_w spx
      WHERE (spx.d_rating_date, spx.c_rating_agency_issuer) in              
 
            (SELECT max(spw.d_rating_date), spw.c_rating_agency_issuer 
 
            FROM   rdt_fs_histo_sp_w spw
 
            WHERE    spw.c_rating_agency_issuer in (100412,100317,100382,100794,100528,100326,100339)
            AND      spw.d_rating_date <= '01/04/2005'
 
            GROUP BY spw.c_rating_agency_issuer)
 
      AND (spx.d_previous_rating_date, spx.c_rating_agency_issuer) in
 
          (SELECT max(spu.d_previous_rating_date), spu.c_rating_agency_issuer
 
              FROM   rdt_fs_histo_sp_w spu
 
              WHERE spu.c_rating_agency_issuer in (100412,100317,100382,100794,100528,100326,100339)
              AND   spu.d_rating_date <= '01/04/2005'
              AND   spu.d_previous_rating_date <= add_months('01/04/2005',-12)
 
              GROUP BY spu.c_rating_agency_issuer)       
 
       )selAll
 
WHERE (selAll.c_pres_note='N' OR selAll.c_pres_note='A' OR selAll.c_pres_note='R')
 
)selTot,
 
 
 
( 
 
SELECT count (*) as IssuerUpgrades, 
       nvl(sum(nvl(selAll.c_notche,0)),0) as IssuerUpgradesPonderes
FROM
 
      (SELECT * 
      FROM rdt_fs_histo_sp_w spx
      WHERE (spx.d_rating_date, spx.c_rating_agency_issuer) in              
 
            (SELECT max(spw.d_rating_date), spw.c_rating_agency_issuer 
 
            FROM   rdt_fs_histo_sp_w spw
 
            WHERE    spw.c_rating_agency_issuer in (100412,100317,100382,100794,100528,100326,100339)
            AND      spw.d_rating_date <= '01/04/2005'
 
 
            GROUP BY spw.c_rating_agency_issuer)
 
      AND (spx.d_previous_rating_date, spx.c_rating_agency_issuer) in
 
          (SELECT max(spu.d_previous_rating_date), spu.c_rating_agency_issuer
 
              FROM   rdt_fs_histo_sp_w spu
 
              WHERE spu.c_rating_agency_issuer in (100412,100317,100382,100794,100528,100326,100339)
              AND   spu.d_rating_date <= '01/04/2005'
              AND   spu.d_previous_rating_date <= add_months('01/04/2005',-12)
 
 
              GROUP BY spu.c_rating_agency_issuer)      
 
       )selAll
 
WHERE (selAll.c_pres_note='N' OR selAll.c_pres_note='A' OR selAll.c_pres_note='R')
AND   selAll.c_last_action_rating_code='UPG'
 
)SelUp,
 
 
 
(
 
SELECT count (*) as IssuerDowngrades, 
       nvl(sum(nvl(selAll.c_notche,0)),0) as IssuerDowngradesPonderes
FROM
 
      (SELECT * 
      FROM rdt_fs_histo_sp_w spx
      WHERE (spx.d_rating_date, spx.c_rating_agency_issuer) in              
 
            (SELECT max(spw.d_rating_date), spw.c_rating_agency_issuer 
 
            FROM   rdt_fs_histo_sp_w spw
 
            WHERE    spw.c_rating_agency_issuer in (100412,100317,100382,100794,100528,100326,100339)
            AND      spw.d_rating_date <= '01/04/2005'
 
 
            GROUP BY spw.c_rating_agency_issuer)
 
      AND (spx.d_previous_rating_date, spx.c_rating_agency_issuer) in
 
          (SELECT max(spu.d_previous_rating_date), spu.c_rating_agency_issuer
 
              FROM   rdt_fs_histo_sp_w spu
 
              WHERE spu.c_rating_agency_issuer in (100412,100317,100382,100794,100528,100326,100339)
              AND   spu.d_rating_date <= '01/04/2005'
              AND   spu.d_previous_rating_date <= add_months('01/04/2005',-12)
 
 
              GROUP BY spu.c_rating_agency_issuer)        
 
       )selAll
 
WHERE (selAll.c_pres_note='N' OR selAll.c_pres_note='A' OR selAll.c_pres_note='R')
AND   selAll.c_last_action_rating_code='DNG'
 
)SelDown

Et l'explication :

Je souhaite calculer des indicateurs... Mais cela prend 6 secondes, ce qui est trop, sachant que je dois exécuter cette requete une centaine de fois (boucle )

Le gros pb, est que cette requête exécute 3 fois la même sous-requête à une clause where près... comment optimiser cela ? De même, est il possible de revoir la sous requête qui permet de sélectionner la bonne ligne.

Merci pour votre aide.