Précédent Index Suivant

2   Optimisation(8 points)

Soit le schéma, la requête et le plan d'exécution ORACLE suivants: 

create table TGV (
        NumTGV integer,
        NomTGV varchar(32),
        GareTerm varchar(32));

create table Arret (
        NumTGV integer,
        NumArr integer,
        GareArr varchar(32),
        HeureArr varchar(32));


EXPLAIN PLAN 
    SET statement_id = 'eds0'
    FOR select NomTGV
  from TGV, Arret
 where TGV.NumTGV = Arret.NumTGV
   and GareTerm = 'Aix';

@exbdb;

/* sans index */
0 SELECT STATEMENT
  1 MERGE JOIN
    2 SORT JOIN
      3 TABLE ACCESS FULL ARRET
    4 SORT JOIN
      5 TABLE ACCESS FULL TGV
Que calcule la requête (0,5 point)?

Solution: Noms des TGV dont le terminus est Aix et qui sont répertoriés dans la table Arret.

Que pouvez-vous dire sur l'existence d'index pour les tables ``TGV'' et ``Arret'' (0,5 point)? Décrivez en détail le plan d'exécution: quel algorithme de jointure a été choisi (0,5 point), quelles opérations sont effectuées et dans quel ordre (1,5 points)?

Solution: Il n'y a pas d'index ni sur la gare terminus ni sur le numéro de TGV dans la table TGV. Il n'y a pas d'index sur le numéro de TGV dans la table Arret. L'algo de jointure est le tri-fusion. On parcourt séquentiellement la table TGV et on sélectionne les TGV dont le terminus est Aix, on projète sur le numéro de TGV et le nom. On trie sur le numéro de TGV. On lit la table Arret, on la projète sur le numéro de TGV et on trie sur le numéro de TGV. On fusionne les deux tables triées et on projète sur le nom de TGV

On fait la création d'index suivante: 
create index arret_numtgv on arret(numtgv);
L'index créé est-il dense? unique?(1 point) Quel est le plan d'exécution choisi par Oracle? Vous pouvez donner le plan avec la syntaxe ou sous forme arborescente (1 point). Expliquez en détail le plan choisi (1 point).

Solution: L'index est dense et non unique. 

0 SELECT STATEMENT
  1 NESTED LOOPS
    2 TABLE ACCESS FULL TGV
    3 INDEX RANGE SCAN ARRET_NUMTGV
On parcourt la table TGV et on sélectionne les nuplets (TGV) qui ont pour gare terminus 'Aix'. Pour chacun d'eux on utilise le numéro de TGV comme clé d'accès à l'index sur les numéros de TGV de la table 'Arret'. On vérifie en traversant l'index de la table 'Arret' que le TGV existe. C'est l'algorithme de boucles imbriquées en présence d'index, mais observez qu'il n'est pas nécessaire d'accéder à la table 'Arret' elle-même.

On rajoute encore un index: 
create index tgv_gareterm on tgv(gareterm);
Quel est le plan d'exécution choisi par Oracle? (1 point) Expliquez le en détail (1 point)

Solution: 

0 SELECT STATEMENT
  1 NESTED LOOPS
    2 TABLE ACCESS BY INDEX ROWID TGV
      3 INDEX RANGE SCAN TGV_GARETERM
    4 INDEX RANGE SCAN ARRET_NUMTGV
C'est presque le même plan, sauf que au lieu de balayer tous les TGV (toute la table TGV), on accède directement à ceux dont le terminus est 'Aix' en traversant l'index sur les gares terminus

Précédent Index Suivant