2  Optimisation (6 points)

SELECT NomF, DateL
  FROM Livraison, Container, Consommation
 WHERE Livraison.IdC = Container.IdC
   AND Container.IdC = Consommation.IdC
   AND TypeA = 'farine animale'
   AND DateC > '03/06/99';

Plan d'exécution
--------------------------------------------------------------
0 SELECT STATEMENT
  1 MERGE JOIN
    2 SORT JOIN
      3 NESTED LOOPS
        4 TABLE ACCESS FULL CONSOMMATION
        5 TABLE ACCESS BY INDEX ROWID CONTAINER
          6 INDEX RANGE SCAN idx
    7 SORT JOIN
      8 TABLE ACCESS FULL LIVRAISON

1. Sur quelle relation et quel attribut l'index idx a-t-il été défini? (0.5 points) Expliquer ce plan d'exécution en détail. En particulier expliquer à quelle opération correspond chaque ligne et dans quel ordre sont effectuées les opérations. Mettre bien en évidence les algorithmes de jointure utilisés (1.5 points).
   
   Solution : CREATE INDEX idx on Container(IdC);
   
   
2. On crée un index supplémentaire avec la commande suivante

   CREATE INDEX consommation_idc_idx on Consommation(IdC);
   

   Donner le nouveau plan d'exécution (sous forme EXPLAIN ou sous forme d'un arbre)(1 point).
   
   Solution :

   Plan d'exécution
   -------------------------------------------------------------------
   0 SELECT STATEMENT
     1 NESTED LOOPS
       2 NESTED LOOPS
         3 TABLE ACCESS FULL LIVRAISON
         4 TABLE ACCESS BY INDEX ROWID CONTAINER
           5 INDEX RANGE SCAN idx
       6 TABLE ACCESS BY INDEX ROWID CONSOMMATION
         7 INDEX RANGE SCAN CONSOMMATION_IDC_IDX
   

3. Jointure par hachage. Soit une relation R1 de taille 10 pages et contenant 1000 nuplets (100 par page). Soit une relation R2 de taille 100 pages contenant 10 000 nuplets (100 par page).
   1. La première phase consiste à se réserver une extension de 12 pages dans le segment temp, à lire la table R1 séquentiellement, page par page et pour chaque article a de chaque page, appliquer la fonction de hachage qui donne un numéro n de page entre 1 et 12, et à écrire a dans la page de numéro n. On suppose qu'aucune page déborde. On a un buffer d'une seule page pour lire les pages de la relation R1 et un buffer d'une seule page pour lire une page de temp, y insérer le nuplet de R1 et réécrire la page. Quel est le nombre de pages lues/écrites dans cette phase? (Attention, le coût donné dans le poly, page 325 correspond à une variante optimisée de cet algorithme) (1 point)
      
      Solution: 10 + 1000 + 1000 = 2010
      
      
   2. Comment modifier l'algorithme de la première phase et à combien se réduit le coût de la première phase en E/S si au lieu de 2 pages, on dispose de 1 + 12 pages en mémoire centrale? (1 point)
      
      Solution: 10 + 12 = 22
      
      
   3. La deuxième phase consiste (i) à lire la relation R2 page par page dans un buffer B1 et pour chaque article b, (a) appliquer la fonction de hachage qui donne un numéro de page à lire dans le segment temp (étape 1), (b) à lire la page dans un buffer B2, (c) à comparer b à chacun des articles a du buffer B2 et à mettre le résultat de la jointure dans une page d'un buffer B3 et (ii) quand la page B3 est pleine à l'écrire sur le disque dans une autre extension du segment temp de taille 50 pages (on suppose que le résutat de la jointure fait 50 pages). On suppose pour cette phase qu'on dispose en mémoire centrale de trois pages seulement, une pour chaque buffer. Quel est le nombre de pages lues/écrites sur disque au cours de cette phase? (1 point)
      
      Solution: 100 + 10000 + 50 = 10150