Discussion :

[Julien59]

Bonjour à tous,

Voici la requête qui me pose problème...

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
 
    select
      t.ANNEE_MOIS_JOUR,
      t.CODE_MAGASIN,
      t.NUMERO_TICKET,
      t.NUMERO_LIGNE,
      t.CODE_ARTICLE,
      t.NUMERO_CARTE,
      p.NUMERO_PERSONNE,
      t.HEURE_VENTE,
      t.QTE_CAISSE,
      t.CA_TTC_REAL,
      t.CA_HT_REAL,
      t.MG_REAL,
      t.CA_TTC_NET,
      t.CA_HT_NET,
      t.MG_NET,
      t.CONCAT_NUM_TICKET,
      t.CDE_EXPRESS,
      t.CDE_MOBILE
    from
      SAS_CRM.PERSONNE_TICKET_SAS p,
      CRM.CARTE c,
      CRM.TICKET t
    where
      c.NUMERO_PERSONNE = p.NUMERo_PERSONNE and
      t.NUMERO_CARTE = c.NUMERO_CARTE
 
      UNION
 
      select
      t.ANNEE_MOIS_JOUR,
      t.CODE_MAGASIN,
      t.NUMERO_TICKET,
      t.NUMERO_LIGNE,
      t.CODE_ARTICLE,
      t.NUMERO_CARTE,
      t.NUMERO_PERSONNE,
      t.HEURE_VENTE,
      t.QTE_CAISSE,
      t.CA_TTC_REAL,
      t.CA_HT_REAL,
      t.MG_REAL,
      t.CA_TTC_NET,
      t.CA_HT_NET,
      t.MG_NET,
      t.CONCAT_NUM_TICKET,
      t.CDE_EXPRESS,
      t.CDE_MOBILE
    from
      SAS_CRM.PERSONNE_TICKET_SAS p,
      CRM.TICKET_PREMIER t
    where
      t.NUMERO_PERSONNE = p.NUMERO_PERSONNE

L'explain plan est assez dégueu puisqu'il fait des full sur toutes mes tables. Toutes les tables sont indexées sur les champs qui vont bien et les stats sont recalculées quotidiennement.
Les 2 éléments de l'union indépendemment l'un de l'autre tournent relativement vite. Ce qui plombe ma requête semble du coup être l'union.

Je cherche donc à réécrire cette requête par des jointures plutôt que par un union, mais je n'y parviens pas... C'est là que j'ai besoin de votre aide

Merci d'avance pour vos conseils

[Rei Ichido]

Quelle est la volumétrie retournée par chacune de ces deux requêtes ?
L'utilisation de UNION force un tri pour faire du DISTINCT, qui va porter ici sur pas mal de colonnes, on peut comprendre que ça ralentisse !
S'il n'y a pas de doublons possibles, utilisez un UNION ALL.

[lola06]

Il faudrait déjà écrire les jointures selon la norme.

Sinon voici un exemple vu dans les cours d'optimisation sur dvp :

Code :

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

1
2
3
4
5
6
7
8
SELECT OBJ_NOM AS NOM, OBJ_PRIX AS PRIX  
FROM   T_OBJET  
 
UNION  
 
SELECT MAC_NOM AS NOM, MAC_PRIX AS PRIX  
FROM   T_MACHINE  
ORDER  BY NOM, PRIX

Code :

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

1
2
3
4
5
6
7
SELECT COALESCE(OBJ_NOM, MAC_NOM) AS NOM,
COALESCE(OBJ_PRIX, MAC_PRIX) AS PRIX  
FROM   T_OBJET O
         FULL OUTER JOIN T_MACHINE M
              ON O.OBJ_NOM = M.MAC_NOM
                 AND O.OBJ_PRIX = M.MAC_PRIX
ORDER  BY NOM, PRIX

[Julien59]

Bonjour,

Merci pour les réponses.

@Rei Ichido : Les doublons sont possibles donc le UNION ALL n'est pas possible dans mon cas.

@lola06 : J'ai justement voulu m'inspirer de cet exemple mais ca ne me retourne pas le résultat attendu. Je ne parviens pas à placer correctement mes jointures externes. Je dois absolument faire un full outer join entre TICKET et TICKET_PREMIER, et le fait de mettre ca me rejette les autres jointures externes que je souhaite positionner.

[McM]

Peut être voir pourquoi il fait un full sur les tables.
L'union me parait une meilleure approche que des jointures externes.

[mnitu]

En fait vos donnes proviennent soit de la table ticket_pemier pourvu que la personne existe dans la table personne_ticket_sas soit de la table ticket. Les enregistrements de la table ticket sont d’abord probablement multipliés par la table carte avant d’être testés pour existence dans la même table personne_ticket_sas. Le tout est par la suite trié et les enregistrements en doublon sont éliminés par le UNION. Vous devez comprendre que c’est une manière peu efficace d’obtenir vos données et donc il n’est pas étonnant si ça rame.

[Julien59]

Bon, j'ai réussi à le faire avec des jointures, et en effet, apparemment, c'est pire !

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
select 
      PREM_TICK.ANNEE_MOIS_JOUR,
      PREM_TICK.CODE_MAGASIN,
      PREM_TICK.NUMERO_TICKET,
      PREM_TICK.NUMERO_LIGNE,
      PREM_TICK.CODE_ARTICLE,
      PREM_TICK.NUMERO_CARTE,
      PREM_TICK.NUMERO_PERSONNE,
      PREM_TICK.HEURE_VENTE,
      PREM_TICK.QTE_CAISSE,
      PREM_TICK.CA_TTC_REAL,
      PREM_TICK.CA_HT_REAL,
      PREM_TICK.MG_REAL,
      PREM_TICK.CA_TTC_NET,
      PREM_TICK.CA_HT_NET,
      PREM_TICK.MG_NET,
      PREM_TICK.CONCAT_NUM_TICKET,
      PREM_TICK.CDE_EXPRESS,
      PREM_TICK.CDE_MOBILE,
      sysdate,
      sysdate
from (
select
 
  dense_rank() over (partition by numero_personne order by annee_mois_jour asc,heure_vente asc,numero_ticket asc) anciennete,
  PREMIER_TICKETS.* 
 
from
    (
    select 
      nvl(t.ANNEE_MOIS_JOUR,tp.ANNEE_MOIS_JOUR) ANNEE_MOIS_JOUR,
      nvl(t.CODE_MAGASIN,tp.CODE_MAGASIN) CODE_MAGASIN,
      nvl(t.NUMERO_TICKET,tp.NUMERO_TICKET) NUMERO_TICKET,
      nvl(t.NUMERO_LIGNE,tp.NUMERO_LIGNE) NUMERO_LIGNE,
      nvl(t.CODE_ARTICLE,tp.CODE_ARTICLE) CODE_ARTICLE,
      nvl(t.NUMERO_CARTE,tp.NUMERO_CARTE) NUMERO_CARTE,
      p.NUMERO_PERSONNE,
      nvl(t.HEURE_VENTE,tp.HEURE_VENTE) HEURE_VENTE,
      nvl(t.QTE_CAISSE,tp.QTE_CAISSE) QTE_CAISSE,
      nvl(t.CA_TTC_REAL,tp.CA_TTC_REAL) CA_TTC_REAL,
      nvl(t.CA_HT_REAL,tp.CA_HT_REAL) CA_HT_REAL,
      nvl(t.MG_REAL,tp.MG_REAL) MG_REAL,
      nvl(t.CA_TTC_NET,tp.CA_TTC_NET) CA_TTC_NET,
      nvl(t.CA_HT_NET,tp.CA_HT_NET) CA_HT_NET,
      nvl(t.MG_NET,tp.MG_NET) MG_NET,
      nvl(t.CONCAT_NUM_TICKET,tp.CONCAT_NUM_TICKET) CONCAT_NUM_TICKET,
      nvl(t.CDE_EXPRESS,tp.CDE_EXPRESS) CDE_EXPRESS,
      nvl(t.CDE_MOBILE,tp.CDE_MOBILE) CDE_MOBILE
from
TICKET_PREMIER tp full outer join TICKET t on
(t.annee_mois_jour = tp.annee_mois_jour and
 t.code_magasin = tp.code_magasin and
 t.numero_carte = tp.numero_carte and
 t.numero_ligne = tp.numero_ligne)
 inner join CARTE c on 
  (nvl(tp.numero_carte,t.numero_carte) = c.numero_carte) 
 inner join sas_crm.PERSONNE_TICKET_SAS p on (c.numero_personne = p.numero_personne)
      ) PREMIER_TICKETS
    ) PREM_TICK
where
  anciennete = 1

[lola06]

Les parenthèses sont superflues ici.

As-tu essayé de faire des jointures normalisées dans tes deux sous-requête et de garder l'union ? Qu'est ce que ça donne ?

Sinon pourrais-tu nous mettre le plan d'exécution et les index pour avoir plus d'infos ?

[Waldar]

L'explain plan est assez dégueu puisqu'il fait des full sur toutes mes tables

Vous n'avez aucun filtre, le full scan est parfaitement justifié.

Les 2 éléments de l'union indépendemment l'un de l'autre tournent relativement vite. Ce qui plombe ma requête semble du coup être l'union.

Vous parlez de la réponse complète et pas de la prévisualisation des cinq cents premières lignes ?

La seule optimisation que je vois c'est celle-ci (signalée par mnitu) :

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
SELECT t.ANNEE_MOIS_JOUR
     , t.CODE_MAGASIN
     , t.NUMERO_TICKET
     , t.NUMERO_LIGNE
     , t.CODE_ARTICLE
     , t.NUMERO_CARTE
     , c.NUMERO_PERSONNE
     , t.HEURE_VENTE
     , t.QTE_CAISSE
     , t.CA_TTC_REAL
     , t.CA_HT_REAL
     , t.MG_REAL
     , t.CA_TTC_NET
     , t.CA_HT_NET
     , t.MG_NET
     , t.CONCAT_NUM_TICKET
     , t.CDE_EXPRESS
     , t.CDE_MOBILE
  FROM CRM.CARTE c
       INNER JOIN CRM.TICKET t
         ON t.NUMERO_CARTE = c.NUMERO_CARTE
 WHERE EXISTS (SELECT NULL
                 FROM SAS_CRM.PERSONNE_TICKET_SAS p
                WHERE p.NUMERO_PERSONNE = c.NUMERO_PERSONNE)
 
 UNION
 
SELECT t.ANNEE_MOIS_JOUR
     , t.CODE_MAGASIN
     , t.NUMERO_TICKET
     , t.NUMERO_LIGNE
     , t.CODE_ARTICLE
     , t.NUMERO_CARTE
     , t.NUMERO_PERSONNE
     , t.HEURE_VENTE
     , t.QTE_CAISSE
     , t.CA_TTC_REAL
     , t.CA_HT_REAL
     , t.MG_REAL
     , t.CA_TTC_NET
     , t.CA_HT_NET
     , t.MG_NET
     , t.CONCAT_NUM_TICKET
     , t.CDE_EXPRESS
     , t.CDE_MOBILE
  FROM CRM.TICKET_PREMIER t
 WHERE EXISTS (SELECT NULL
                 FROM SAS_CRM.PERSONNE_TICKET_SAS p
                WHERE p.NUMERO_PERSONNE = t.NUMERO_PERSONNE);

Un index sur CRM.CARTE (NUMERO_PERSONNE, NUMERO_CARTE) vous permettrai de ne pas lire la table CARTE.

Sinon voici un exemple vu dans les cours d'optimisation sur dvp

Il a du craquer notre ami SQLPro parce que si ça c'est une optimisation...

[Julien59]

Vous n'avez aucun filtre, le full scan est parfaitement justifié.

Je pensais que le fait de passer par une jointure par une table limitant le nombre d'enregistrements faisait passer par l'index.

Vous parlez de la réponse complète et pas de la prévisualisation des cinq cents premières lignes ?

Les 50 premières lignes, en effet.

Concernant votre optimisation, elle fait passer mon coût de requête de 267577 à 259329, on a gagné un peu de terrain