Comment optimiser requête SQL avec création Index

**schumi101** · 10/12/2007, 15h03

Bonjour,

Afin de satisfaire une demande client, j'essaye d'optimiser une requête SQL. Cette requête ramène 12 lignes seulement mais prend environ 3 minutes pour l'exécution (ce qui est très long car la volumétrie des tables va augmenter régulièrement)

Les plus grosse tables de ma requête sont : (ceci est une indication)

P_Tarif_com avec plus de 600 000 lignes
P_Tarif_palier avec plus de 600 000 lignes
P_assoc_bareme_prestation avec plus de 80000 lignes
P_bareme avec 15000 lignes

Ma requête est la suivante :

je met en gras dès qu'il y a un index:

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
SELECT   
  P_BAREME.FK_GBA_OPERATEUR,
  P_BAREME.BAREME,
  P_ASSOC_BAREME_PRESTATION.FK_PRESTATION,
  P_TYPE_TAXE.TYPE_TAXE,
  P_OPERATEUR_EMETTEUR.ID_OP_EMET,
  P_TYPE_ZONE.ID_TYPE_ZONE,
  to_date(to_char(P_TARIF_COM.DATE_DEBUT,'dd/mm/yyyy'),'dd/mm/yyyy'),
  to_date(to_char(P_TARIF_COM.DATE_FIN,'dd/mm/yyyy'),'dd/mm/yyyy'),
  P_MONNAIE.MONNAIE,
  P_TARIF_COM.MONTANT_MINIMUM,
  P_TARIF_COM.SURTAXE,
  P_TARIF_COM.CREDIT_TEMPS,
  P_TARIF_COM.CREDIT_TEMPS_COUT,
  P_TARIF_COM.VALO_DERNIER_PALIER,
  P_TARIF_PALIER.DEBUT,
  P_TARIF_PALIER.TARIF,
  P_TARIF_PALIER.COUT_FIXE,
  P_UNITE.UNITE,
  P_TARIF_PALIER.UNITE_INDIVISIBLE
FROM
  P_BAREME,
  P_ASSOC_BAREME_PRESTATION,
  P_TYPE_TAXE,
  P_OPERATEUR_EMETTEUR,
  P_TYPE_ZONE,
  P_TARIF_COM,
  P_MONNAIE,
  P_TARIF_PALIER,
  P_UNITE
WHERE
  ( P_ASSOC_BAREME_PRESTATION.FK_BAR_NUMERO_BAREME=P_BAREME.NUMERO_BAREME  )
  AND  ( P_TARIF_COM.FK_TYPE_ZONE=P_TYPE_ZONE.ID_TYPE_ZONE  )
  AND  ( P_TARIF_COM.FK_MONNAIE=P_MONNAIE.MONNAIE  )
  AND  ( P_OPERATEUR_EMETTEUR.ID_OP_EMET=P_TARIF_COM.FK_OP_EMET  )
  AND  ( P_TARIF_COM.FK_TYPE_TAXE=P_TYPE_TAXE.TYPE_TAXE  )
  AND  ( P_TARIF_PALIER.FK_NUMERO_TARIF=P_TARIF_COM.NUMERO_TARIF_COM  )
  AND  ( P_TARIF_PALIER.FK_UNITEID=P_UNITE.UNITE  )
  AND  ( P_ASSOC_BAREME_PRESTATION.DATE_DEBUT=P_TARIF_COM.FK_ABP_DATE_DEBUT and P_ASSOC_BAREME_PRESTATION.FK_BAR_NUMERO_BAREME=P_TARIF_COM.FK_ABP_NUMERO_BAREME and P_ASSOC_BAREME_PRESTATION.FK_PRESTATION=P_TARIF_COM.FK_ABP_PRESTATION  )
  AND  (
  to_date(to_char(P_TARIF_COM.DATE_DEBUT,'dd/mm/yyyy'),'dd/mm/yyyy')  <=  '12/10/2007')
  AND  P_TARIF_PALIER.DEBUT  <=  9999999999999999999
  AND  to_date(to_char(P_TARIF_COM.DATE_FIN,'dd/mm/yyyy'),'dd/mm/yyyy')  >=  '12/10/2007'
  AND  P_BAREME.FK_GBA_OPERATEUR  IN  ('9T','B3G','BYT','COLT','CPTEL','CWF','FFMV2','FREE','FTF','FTLD','FTLDH','FTLDM','FTLDO','FTMRD','MFS','MONAC','OCR','OGPRS','OMMS','OMSP','ORLCF','OVGT','OVT','OWHA','OWIFI','OZB','SFR','SIRIS','SMSC','SMSR','TD','TELE2')
  AND   P_BAREME.BAREME  IN  ('MMSBE')
  AND  P_ASSOC_BAREME_PRESTATION.FK_PRESTATION  IN  ('11C1','11C2','11F','12C1','12C2','12F','13C1','13C2','13F','20C1','20C2','20F','21C1','21C2','21F','TRCE','TRCO','TRF','TVC','TVF','WFC','WIC','WIF','WMMC','WSC','WSMC','WTC','WVC')
  AND  P_TARIF_COM.AUT_IND  IN  ('A')
  AND  P_OPERATEUR_EMETTEUR.ID_OP_EMET  IN ('MODEL','TE')
  AND  P_TYPE_ZONE.ID_TYPE_ZONE  IN  ('EZ','INDET','IZ','T1','T2','T3','TZ')

Désole pour la longueur des clauses where mais c'est pour vous donner une idée du nombre de sélection de chaque clause.
NB: Cett requête est exécuté par businness object, d'où un nombre important de sélection

Voici mon explain plan:
(voir la pièce jointe)

Liste des index:
P_ASSOC_BAREME_prest -> 1index sur 3 champs (fk_bar_numero_bareme,fk_prestation,date_debut)
P_TARIF_COM -> 1 index sur 3 champs
(FK_ABP_NUMERO_BAREME, FK_ABP_PRESTATION, FK_ABP_DATE_DEBUT)
P_tarif_palier -> 1 index sur (debut, fk_numero_tarif)
il y a des index sur toutes les jointures (voir en gras dans le code)

Il n'y a aucun index sur les dates (mais la volumétrie est faible, moins de 50 dates différentes)

Merci de m'éclaircir sur la démarche à suivre pour pouvoir diminuer mon temps de réponse, si cela est possible

Cordialement

**schumi101** · 10/12/2007, 15h23

Voici l'explain plan correct

Désolé pour le désagrément

**orafrance** · 10/12/2007, 15h36

Pas facile de répondre comme ça

Moi ce qui m'étonne c'est le coût de 81 pour un accès par ROWID. Peut-être bien que tu gagnerais avec un index sur ces 2 colonnes :

Code :

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

1
2
  AND  P_BAREME.FK_GBA_OPERATEUR  IN  ('9T','B3G','BYT','COLT','CPTEL','CWF','FFMV2','FREE','FTF','FTLD','FTLDH','FTLDM','FTLDO','FTMRD','MFS','MONAC','OCR','OGPRS','OMMS','OMSP','ORLCF','OVGT','OVT','OWHA','OWIFI','OZB','SFR','SIRIS','SMSC','SMSR','TD','TELE2')
  AND   P_BAREME.BAREME  IN  ('MMSBE')

Je suis aussi étonné de voir la colonne FK_GBA_OPERATEUR qui semble être une FK alors qu'elle n'est jointe à aucune table, est-ce normal ?

**schumi101** · 10/12/2007, 16h00

C'est normal en ce qui concerne fk_gba_operateur car c'est une base de données d'un infocentre afin de faire des rapports Business Object dessus, donc il n'a pas de contrainte au niveau des foreign key, le contrôle est effectué au moment du peuplement de la base par des scripts (voila pour la petite explication

En ce qui concerne la Table P_bareme, il y a 3 indexs

1 sur le champ numero_bareme (pour la jointure)
1 sur fk_gba_operateur
1 sur fk_gba_groupe_bareme

Peut être que l'optimisation est maximale pour cette requête cependant je n'ai pas encore la compétence pour analyser correctement et interpréter correctement l'explain plan

Merci pour vos suggestions et vos explications

NB: le bon explain plan est le second

**schumi101** · 10/12/2007, 16h07

Cela peut être une grande aide, j'ai essayé de mettre en surbrillance les jointures du schéma MPD (il manque certainement 1 table ou 2 table cause manque de place)

**mnitu** · 10/12/2007, 16h24

Il faut examiner les accès de type index rang scan. A priori l’index BOINT.PK_P_TARIF_PALIER manipule 575 000 lignes (575K ?). De même BOINT.PK_P_ASSOC_BAREME_PRESTATION 52 000 lignes mais au bout du compte le plan estime qu’il y aura une seule ligne. Il se peut que ces indexes ne sont pas bons pour cette requête.
Exécute une trace SQL et examine le plan réel, est-ce que le nombre des lignes manipulées est du même ordre de grandeur ?
Quelle est la méthode de collecte des statistiques et à quel moment les statistiques sont calculées ? En fin quelle est la version d’Oracle ?