[10g] Optimisation requête UNION

**Julien59** · 11/05/2012, 14h23

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** · 11/05/2012, 14h51

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** · 11/05/2012, 14h51

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** · 11/05/2012, 15h02

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** · 11/05/2012, 15h20

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

**mnitu** · 11/05/2012, 15h34

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** · 11/05/2012, 16h37

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** · 11/05/2012, 16h40

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** · 11/05/2012, 16h59

Envoyé par Julien59

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é.

Envoyé par Julien59

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.

Envoyé par lola06

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** · 11/05/2012, 17h02

L'explain plan est en pièce jointe.

Le fait de normaliser les jointures ne change rien du tout, j'ai exactement le même explain plan.

Pour info :
Il existe un index sur
- CARTE.NUMERO_CARTE
- CARTE.NUMERO_PERSONNE
- TICKET.NUMERO_CARTE
- TICKET_PREMIER.NUMERO_PERSONNE
- SAS_CRM.PERSONNE_TICKET_SAS.NUMERO_PERSONNE

volumétrie :
CARTE : 11.000.000
TICKET : 82.000.000
TICKET_PREMIER : 11.000.000
PERSONNE_TICKET_SAS : 25.000

**Julien59** · 11/05/2012, 17h08

Envoyé par Waldar

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.

Envoyé par Waldar

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