optimisation de requête sql avec select imbriquées

Version imprimable

Bonjour,

je dispose de la requete sql ci-dessous. le problème c'est qu'elle met énormément de temps à s'exécuter :? . je pense que c'est à cause des select imbriquées. pouvez vous m'aider à optimiser cette requete svp.

Code:

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
select 
  co.dw_co_id,  co.MSISDN, cs.CS_FNAME ,cs.CS_LNAME ,cs.ADDRESS,cs.CARD_NUM,cs.CARD_TYPE,
  CITY,CUSTCODE,co.co_id,tt.DESCRIPTION TARIFF_PLAN, trunc(co.FIRST_CALL_DATE)FIRST_CALL_DATE,  
  s.STATUS_DESC status,co.LAST_BALANCE,day LAST_CALL,
  cs.CS_NAME NAME,BIRTHDATE,co.current_msisdn,cs.cust_activ_day_id
from
(SELECT  ff.*,nvl(ff.dw_cur_tarif_index_id,-99) dw_tarif_index_id_lookup,
   nvl(case when ff.dw_in_cur_tariff_plan_id =1061 and ff.kit_code  like'2020001%' then dw_in_cur_tariff_plan_id
         when t.status_in ='Y'  then dw_in_cur_tariff_plan_id  else ff.dw_cur_tariff_plan_id  end ,-99) dw_tariff_plan_id_lookup
   FROM DWHPROD.dw_contract_dim ff ,DWHPROD.dw_tariff_plans_dim t
   WHERE 
   ff.first_call_day_id >0   
   and ff.dw_status_id in(2,3,4,8)
   and used=1
   and dw_in_cur_tariff_plan_id=t.dw_tariff_plan_id (+)      
   ) co,
   DWHPROD.dw_tariff_plans_dim tt,
   DWHPROD.dw_tarif_index_dim m,
   DWHPROD.dw_customer_dim cs, 
   DWHPROD.dw_status_dim s,
   DWHPROD.dw_days d
where 
co.dw_tariff_plan_id_lookup=tt.dw_tariff_plan_id and m.dw_tarif_index_id =co.dw_tarif_index_id_lookup 
and tt.revenue_producing='Y' 
and (tt.POST_PAID='Y' or m.POST_PAID='Y')
and s.DW_STATUS_ID=co.DW_STATUS_ID
and d.DW_DAY_ID=nvl(co.LAST_CALL_DAY_ID,-99)
and cs.dw_customer_id = co.dw_cust_id
and cs.dummy_customer='N';

merci ;)

01/11/2010, 08h51
dariyoosh

Bonjour,

Il y a de nombreux tables utilisées dans les produits cartésiens de votre requête. Même s'il y a des clauses WHERE afin de réduire le nombre de lignes, votre requête va quand même former les tables temporaires (donc le temps d'exécution pour former la table de la requête et éliminer ensuite les lignes inutiles).

Essayez de créer plusieurs vues. Au lieu de mettre tous dans une seule requête, décomposez cette requête en plusieurs composantes. C'est au niveau des vues que vous réduisez le nombre des lignes avec les clause WHERE (qui seront présentes dans le produit cartésien final).

Donc dans la requête final vous faites le produit cartésien entre les vues et les clause WHERE servira à lier les éléments de ces vues.

Cordialement,
Dariyoosh
01/11/2010, 13h51
McM

Citation:

je pense que c'est à cause des select imbriquées

Peut être, peut être pas..

L'optimisation d'une requête nécessite de connaitre pas mal de choses :
L'explain plan
La volumétrie des tables
Les index
La cardinalité des champs

Première question : Est ce que les stats sont à jour ?
Ensuite, il faut nous donner l'explain plan de la requête, les index sur les tables et les foreign keys entre les tables.

Citation:

Envoyé par dariyoosh

Il y a de nombreux tables utilisées dans les produits cartésiens de votre requête. Même s'il y a des clauses WHERE afin de réduire le nombre de lignes, votre requête va quand même former les tables temporaires (donc le temps d'exécution pour former la table de la requête et éliminer ensuite les lignes inutiles).

Je n'arrive pas aux même conclusions, je n'ai trouvé aucun produit cartésien dans sa requête.

Une réécriture moderne s'impose et le démontre aisément :

Code:

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
WITH co AS
(
SELECT ff.*,
       coalesce(ff.dw_cur_tarif_index_id, -99) as dw_tarif_index_id_lookup,
       case
         when (ff.dw_in_cur_tariff_plan_id = 1061
          AND ff.kit_code LIKE '2020001%')
           OR t.status_in = 'Y'
         then coalesce(ff.dw_in_cur_tariff_plan_id, -99)
         else coalesce(ff.dw_cur_tariff_plan_id, -99)
       end) as dw_tariff_plan_id_lookup
  FROM DWHPROD.dw_contract_dim ff
       LEFT OUTER JOIN DWHPROD.dw_tariff_plans_dim t
         ON t.dw_tariff_plan_id = ff.dw_in_cur_tariff_plan_id
 WHERE ff.first_call_day_id > 0   
   AND ff.dw_status_id IN (2,3,4,8)
   AND used = 1
)
SELECT co.dw_co_id, co.MSISDN, cs.CS_FNAME, cs.CS_LNAME,
       cs.ADDRESS, cs.CARD_NUM, cs.CARD_TYPE, CITY,
       CUSTCODE, co.co_id, tt.DESCRIPTION TARIFF_PLAN,
       trunc(co.FIRST_CALL_DATE) as FIRST_CALL_DATE,  
       s.STATUS_DESC STATUS, co.LAST_BALANCE, day as LAST_CALL,
       cs.CS_NAME as NAME, BIRTHDATE, co.current_msisdn,
       cs.cust_activ_day_id
  FROM co
       INNER JOIN DWHPROD.dw_tariff_plans_dim tt
         ON tt.dw_tariff_plan_id = co.dw_tariff_plan_id_lookup
       INNER JOIN DWHPROD.dw_tarif_index_dim m
         ON m.dw_tarif_index_id = co.dw_tarif_index_id_lookup 
       INNER JOIN DWHPROD.dw_customer_dim cs
         ON cs.dw_customer_id = co.dw_cust_id
       INNER JOIN DWHPROD.dw_status_dim s
         ON s.DW_STATUS_ID = co.DW_STATUS_ID
       INNER JOIN DWHPROD.dw_days d
         ON d.DW_DAY_ID = coalesce(co.LAST_CALL_DAY_ID, -99) 
WHERE tt.revenue_producing = 'Y' 
  AND 'Y' IN (tt.POST_PAID, m.POST_PAID)
  AND cs.dummy_customer = 'N';

En plus des remarques de McM, il reste des colonnes sans alias de tables, on ne sait donc pas à qui elles appartiennent.

Si je ne m'abuse pas le fait de réécrire la requête avec with à la place de la sous-interrogation change parfois le plan d'exécution. Donc pour l’instant il faut garder le même type de requête.

Code:

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
 
SELECT 
       co.dw_co_id, co.MSISDN, cs.CS_FNAME ,cs.CS_LNAME ,cs.ADDRESS,cs.CARD_NUM,cs.CARD_TYPE,
       CITY,CUSTCODE,co.co_id,tt.DESCRIPTION TARIFF_PLAN, trunc(co.FIRST_CALL_DATE) FIRST_CALL_DATE,
       s.STATUS_DESC STATUS, co.LAST_BALANCE, day LAST_CALL,
       cs.CS_NAME NAME, BIRTHDATE, co.current_msisdn, cs.cust_activ_day_id
  FROM
     (SELECT  ff.*,nvl(ff.dw_cur_tarif_index_id,-99) dw_tarif_index_id_lookup,
              nvl(case 
                    when ff.dw_in_cur_tariff_plan_id =1061 AND ff.kit_code  LIKE'2020001%' then dw_in_cur_tariff_plan_id
                    when t.status_in ='Y'  then dw_in_cur_tariff_plan_id
                    else ff.dw_cur_tariff_plan_id  
                  end ,-99) dw_tariff_plan_id_lookup
        FROM DWHPROD.dw_contract_dim ff ,DWHPROD.dw_tariff_plans_dim t
       WHERE ff.first_call_day_id > 0   
         AND ff.dw_status_id IN(2,3,4,8)
         AND used = 1
         AND dw_in_cur_tariff_plan_id = t.dw_tariff_plan_id (+)
     ) co,
     DWHPROD.dw_tariff_plans_dim tt,
     DWHPROD.dw_tarif_index_dim m,
     DWHPROD.dw_customer_dim cs, 
     DWHPROD.dw_status_dim s,
     DWHPROD.dw_days d
 WHERE co.dw_tariff_plan_id_lookup = tt.dw_tariff_plan_id 
   AND m.dw_tarif_index_id         = co.dw_tarif_index_id_lookup 
   AND s.DW_STATUS_ID              = co.DW_STATUS_ID
   AND d.DW_DAY_ID                 = nvl(co.LAST_CALL_DAY_ID,-99)
   AND cs.dw_customer_id           = co.dw_cust_id
   AND tt.revenue_producing        = 'Y' 
   AND cs.dummy_customer           = 'N'
   AND (    tt.POST_PAID           = 'Y' 
         OR m.POST_PAID            = 'Y'
       )

02/11/2010, 16h51
mnitu

On peut voir que les critères de filtrage des données sont très faibles (Y, N, 0, 1, etc), donc peu des chances d’éliminer efficacement des enregistrements. Mais, le plus intéressant est la double présence de la table dw_tariff_plans_dim dans la requête. En continuant l’analyse on s’aperçoit que la deuxième fois la jointure avec cette table se fait sur la zone dw_tariff_plan_id_lookup qui est calculée dans la première jointure. Mmmm, il se peut bien que l’optimiseur n’aime pas ça trop.
Dans ce cas je pense qu’il sera sage de balayer la table dw_contract_dim et de recherche à gauche et à droit les autres infos via les index (qu'on suppose à être bien en place).
02/11/2010, 22h12
dariyoosh

Citation:

Envoyé par Waldar

Je n'arrive pas aux même conclusions, je n'ai trouvé aucun produit cartésien dans sa requête.

C'est peut-être parce qu'on considère deux terminologies différentes. Une jointure est une sorte de produit cartésien, sauf qu'au lieu de renvoyer toutes les lignes la condition de jointure détermine le nombre de lignes à renvoyer, mais pour vérifier cette condition, oracle procède de la même manière qu'un produit cartésien, c'est à dire associer chaque ligne avec les autres.