Précédent Index

2   Optimisation (5 points)

Soit les tables relationnelles suivantes :
TGV(NumTGV, NomTGV, GareTerm)
:

Arrêt(NumTGV, NumArr, GareArr, HeureArr)
:

Exemple : Le TGV 311 s'appelle ``Le Mistral'' et sa gare terminus est Marseille. Il part de Paris à 13h23, fait un arrêt à Lyon à 15h15 et à Aix en Provence à 16h20, et arrive à Marseille a 16h30 :

TGV
NumTGV NomTGV GareTerm
311 Le Mistral Marseille
Arrêt
NumTGV NumArr GareArr HeureArr
311 0 Paris 13h23
311 1 Lyon 15h15
311 2 Aix 16h20
311 3 Marseille 16h30

On donne ci-dessous une requête SQL (nom des TGV dont le terminus est Marseille et qui s'arrête à Avignon) et le plan d'exécution fourni par Oracle 
EXPLAIN PLAN 
    SET statement_id = 'eds0'
    FOR select NomTGV
  from TGV, Arret
 where TGV.NumTGV = Arret.NumTGV
   and GareTerm = 'Marseille'
   and GareArr = 'Avignon';

@exbdb;


0 SELECT STATEMENT
  1 MERGE JOIN
    2 SORT JOIN
      3 TABLE ACCESS FULL ARRET
    4 SORT JOIN
      5 TABLE ACCESS FULL TGV
  1. Existence d'index (1 point): Existe-t-il un index? sur quel(s) attribut(s)?
  2. Algorithme de jointure (2 points): Expliquer en détail le plan d'exécution (accès aux tables, sélections, jointure, projections)
  3. Ajout d'index (2 points): On crée un index sur la table TGV sur l'attribut NumTGV. Expliquer en détail le nouveau plan d'exécution.
Solution:
  1. Il n'y a pas d'index sur les attributs de jointure. Il n'y a pas d'index non plus ni sur GareTerm, ni sur GareArr. Le plan d'exécution (tri-fusion) consiste à accéder séquentiellement à la table TGV, sélectionner les nuplets de TGV tels que GareTerm='Marseille', et projeter sur NumTGV et NomTGV, on obtient une relation R1; parcourir en séquentiel Arret, sélectionner les nuplets tels que GareArr='Avignon' et projeter sur NumTGV; faire la jointure naturelle de R1 et R2 par tri-fusion (on trie les deux tables résultantes sur NumTGV et on fait la fusion); enfin projeter sur NomTGV.
  2. après création d'index le plan est: 
    
0 SELECT STATEMENT
  1 NESTED LOOPS
    2 TABLE ACCESS FULL ARRET
    3 TABLE ACCESS BY ROWID TGV
      4 INDEX UNIQUE SCAN TGV_NUMTGV
    On parcourt séquentiellement la table Arret, on sélectionne les nuplets tels que GareArr='Avignon'; pour chacun d'entre eux, la valeur de l'attribut NumTGV sert de clé d'accès à l'index sur NumTGV de la table TGV. la traversée de l'index donne un rowid de nuplet de la table TGV. On accède à ce nuplet. Si l'attribut GareTerm='Marseille, on projète sur le nom qu'on ajoute au résultat.
Précédent Index