Différences

Ci-dessous, les différences entre deux révisions de la page.

--- site:enseignement:licence:3i009:tmejointure [20/09/2017 16:46] – [Exercice 3. ORDRE des jointures entre 3 relations] hubert
+++ site:enseignement:licence:3i009:tmejointure [17/11/2020 00:37] (Version actuelle) – [Exercice préliminaire] hubert
@@ Ligne 3: / Ligne 3: @@
 ====== TME 6 Jointure ======
-L'objectif de ce TME est de comprendre l'optimisation des requêtes qui contiennent des jointures.
+/*   TME pour 3I009 */
+L'objectif de ce TME est de comprendre l'optimisation des requêtes qui contiennent des jointures. Les notions étudiées sont :
+  * Algorithmes de jointures,
   * Ordre des jointures,
   * Coût d'une requête de jointure,
-  * Forme des arbres de jointure  (linéaire à gauche et autre forme),
+  * Forme des arbres de jointure (linéaire à gauche et autre forme),
   * Avantage/inconvénient d'utiliser un index sur l'attribut de jointure et/ou sur d'autres attributs.
@@ Ligne 17: / Ligne 21: @@
 </showif>
 ===== Préparation du TME =====
-Lire l'énoncé de l'exercice dans le poly de TD:  Exercice 3: Club de joueurs
+Lire l'énoncé de l'exercice dans le poly : TD 4 et 5:  Exercice 4: INTRO pour le TME6 Jointures
+Télécharger l'archive du TME: [[https://nuage.lip6.fr/s/6dpYZdLAMHtdHpG|tmeJointure2020.zip]]
+Se connecter à [[site:enseignement:documentation:oracle:sqlworkbench|]], charger les macros, puis ajouter les synonymes vers les tables du TME en exécutant la ligne
+   @synonymJCF
+/* on enlève cette partie PPTI
 ^commande^description^
@@ Ligne 26: / Ligne 41: @@
 | **Alt-x** my/sql-oracle //ou// **Atl-x** sql-oracle | se connecter à  Oracle.  Voir [[site:enseignement:documentation:oracle:connexionoracle|ConnexionOracle]] |
 | aller sur le paragraphe contenant @baseJCF et faire  Ctrl-C Ctrl-C | créer vos tables J, C, F, les index et les statistiques nécessaires à l'optimisation basée sur le coût|
+*/
 Les tables d'un club de sport  sont :
@@ Ligne 39: / Ligne 57: @@
   * **F** (cnum, budget, depense, recette)
-Les index existants s'appellent: **I_J_CNUM** pour J(cnum), **I_J_SALAIRE** pour J(salaire), **I_C_CNUM** pour C(cnum),  **I_C_DVISION** pour C(division) et **I_F_CNUM** pour F(cnum)
+Les index existants s'appellent:
+  * **I_J_CNUM** surJ(cnum), **I_J_SALAIRE** sur J(salaire),
+  * **I_C_CNUM** sur C(cnum),  **I_C_DIVISION** sur C(division)
+  * **I_F_CNUM** sur F(cnum)
-Pour **afficher** les plans proposés par le SGBD et leur coût, commencer chaque requête par
+Pour **afficher** les plans proposés par le SGBD et leur coût, se placer dans une requête et exécuter la macro p4 (touche F2)
-<code ascii>
+/*
+<code sql>
    explain plan for SELECT ...
 </code>
 puis terminer chaque requête par
     @p4
+*/
@@ Ligne 56: / Ligne 79: @@
  </fs>
 </showif>
 ===== Exercice préliminaire  =====
 Combien de n-uplets ont chacune des relations ?
 Quel est le coût d'accès à chaque table ? Rappel : le coût d'un plan se lit dans la colonne Cost de l'opérateur racine (Id=0)
 <code sql>
-explain plan for
+--explain plan for
     select * from J;
-@p4
+--@p4
 </code>
@@ Ligne 134: / Ligne 158: @@
 <showif isloggedin>
- <fc #008000>le prédicat sur le salaire est maintenant très sélectif</fc>
+ <fc #008000>le prédicat sur le salaire est maintenant assez sélectif pour que le nombre de Joueurs soit inférieur au nombre de Clubs (ça change l'ordre par rapport à la 1ère requête), mais pas assez sélectif pour utiliser l'index</fc>
 </showif>
@@ Ligne 146: / Ligne 170: @@
 </code>
-a) Afficher et dessiner **P2**.
+a) Afficher et dessiner le plan **P2** de cette requête.
 <showif isloggedin>
@@ Ligne 184: / Ligne 208: @@
-b) Quel est le coût de **P2** ?
+c) Quel est le coût de **P2** ?
 <showif isloggedin>
 <fc #008000>
@@ Ligne 234: / Ligne 258: @@
-b) détailler les étapes de l'évaluation
+b) Détailler les étapes de l'évaluation
 <showif isloggedin>
@@ Ligne 246: / Ligne 270: @@
-c) Quel est le coût du plan exprimé en fonction du cout pour lire une table, un index et pour lire un nuplet seul.
+c) Quel est le coût du plan exprimé en fonction du coût pour lire une table, un index et pour lire un nuplet seul.
 <showif isloggedin>
@@ Ligne 264: / Ligne 288: @@
 === Question 4) ===
+/*
+Jointure par boucles imbriquées avec index sur l'attribut de jointure
+*/
 On considère la requête **R4**.
@@ Ligne 321: / Ligne 348: @@
   * La directive ''USE_HASH'' indique que la jointure doit être traitée par hachage.
-Reprendre les requêtes de l'exercice précédent en ajoutant une directive. Expliquer le plan obtenu.
+Reprendre les requêtes R1 à R4 de l'exercice précédent en ajoutant une directive. Expliquer le plan obtenu. Comparer les plans obtenus avec/sans directive pour une même requête.
@@ Ligne 593: / Ligne 620: @@
 --------------------------------------------------------------------------------
 </code>
 </showif>
@@ Ligne 615: / Ligne 640: @@
 Dessiner le plan obtenu. Expliquer son coût.
+<showif isloggedin>
+<fc #008000>**REPONSE**</fc>
+<code>
+-----------------------------------------------------------------------------------
+| Id  | Operation		       | Name	     | Rows  | Bytes | Cost (%CPU)|
+-----------------------------------------------------------------------------------
+|   0 | SELECT STATEMENT	       |	     |	   1 |	  40 |	1007   (0)|
+|   1 |  NESTED LOOPS		       |	     |	     |	     |		  |
+|   2 |   NESTED LOOPS		       |	     |	   1 |	  40 |	1007   (0)|
+|   3 |    NESTED LOOPS 	       |	     |	   5 |	 125 |	1002   (0)|
+|*  4 |     TABLE ACCESS BY INDEX ROWID| J	     |	   5 |	  90 |	 997   (0)|
+|*  5 |      INDEX RANGE SCAN	       | I_J_SALAIRE |	 997 |	     |	   4   (0)|
+|   6 |     TABLE ACCESS BY INDEX ROWID| F	     |	   1 |	   7 |	   1   (0)|
+|*  7 |      INDEX UNIQUE SCAN	       | I_F_CNUM    |	   1 |	     |	   0   (0)|
+|*  8 |    INDEX UNIQUE SCAN	       | I_C_CNUM    |	   1 |	     |	   0   (0)|
+|*  9 |   TABLE ACCESS BY INDEX ROWID  | C	     |	   1 |	  15 |	   1   (0)|
+-----------------------------------------------------------------------------------
+</code>
+</showif>
@@ Ligne 630: / Ligne 675: @@
 Dessiner le plan obtenu. Expliquer son coût.
+<showif isloggedin>
+<fc #008000>**REPONSE**</fc>
+<code>
+------------------------------------------------------------------------------------
+| Id  | Operation		       | Name	      | Rows  | Bytes | Cost (%CPU)|
+------------------------------------------------------------------------------------
+|   0 | SELECT STATEMENT	       |	      |     1 |    40 |    97	(2)|
+|   1 |  NESTED LOOPS		       |	      |       |       | 	   |
+|   2 |   NESTED LOOPS		       |	      |     1 |    40 |    97	(2)|
+|*  3 |    HASH JOIN		       |	      |     5 |   165 |    92	(2)|
+|*  4 |     TABLE ACCESS FULL	       | J	      |     5 |    90 |    68	(0)|
+|   5 |     TABLE ACCESS BY INDEX ROWID| C	      |  2500 | 37500 |    23	(0)|
+|*  6 |      INDEX RANGE SCAN	       | I_C_DIVISION |  2500 |       |     5	(0)|
+|*  7 |    INDEX UNIQUE SCAN	       | I_F_CNUM     |     1 |       |     0	(0)|
+|   8 |   TABLE ACCESS BY INDEX ROWID  | F	      |     1 |     7 |     1	(0)|
+------------------------------------------------------------------------------------
+</code>
+</showif>