Différences

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

--- site:enseignement:master:bdr:tmejointure [08/03/2018 10:51]
hubert [Exercice 1 : Jointure entre 2 relations]
+++ site:enseignement:master:bdr:tmejointure [14/03/2019 09:51] (Version actuelle)
hubert [Divers]
@@ Ligne 3: / Ligne 3: @@
 ====== TME Jointure ======
-==version 2018==
+/*
+                   TME pour BDR
+Voir l'énoncé du TME jointures en 3I009
+*/
+==version 2019==
@@ Ligne 17: / Ligne 26: @@
 ** Ne pas oublier de consulter les <fc #ff0000>questions fréquentes</fc> en bas de cette page **
 <showif isloggedin>
+**<fc #008000>Vous êtes connecté !</fc>** en tant que membre de l'équipe BD
 </showif>
 ===== Préparation du TME =====
 Lire l'énoncé de l'exercice dans le poly de TD:  Exercice 3: Club de joueurs
@@ Ligne 29: / Ligne 40: @@
 | emacs tmeJointure.sql & | éditer le fichier à compléter pendant le TME|
 | **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 @base3 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|
+| 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 53: / Ligne 64: @@
 puis terminer chaque requête par
     @p4
+<showif isloggedin>
+ <fs xx-large><fc #008000>Les réponses sont insérées en VERT
+ </fc>
+ </fs>
+</showif>
 ===== Exercice préliminaire  =====
 Combien de n-uplets ont chacune des relations J, C, F et BigJoueur?
+Quel est le coût d'accès à chaque table ? Rappel: le coût d'accès à un plan se lit dans la **colonne Cost** de l'opérateur racine (Id=0) sur la première ligne.
 <code sql>
 explain plan for
@@ Ligne 62: / Ligne 83: @@
 </code>
-Quel est le coût d'accès à chaque table ? Rappel: le coût d'accès à un plan se lit dans la **colonne Cost**, pour la racine de l'arbre représentant le plan (première ligne).
 Combien de pages ont chacune des relations ?
@@ Ligne 102: / Ligne 122: @@
 a) Traduisez la requête en français.
+<showif isloggedin>
+<fc #008000>Pour les joueurs dont le salaire est supérieur à 1000: leur n° de licence et le nom de leur club.</fc>
+</showif>
 b) Affichez le plan **P1** de cette requête. Quel est le nom de l'opérateur de jointure ? Dessinez l'arbre de P1 en suivant la méthode présentée en TD : numérotez chaque opération avec son //Id// de 0 à 3.
+<showif isloggedin>
+<code>
+REPONSE:
+----------------------------------------------------------------
+| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)|
+----------------------------------------------------------------
+|   0 | SELECT STATEMENT   |	  | 50000 |  1269K|    76   (2)|
+|*  1 |  HASH JOIN	   |	  | 50000 |  1269K|    76   (2)|
+|   2 |   TABLE ACCESS FULL| C	  |  5000 | 60000 |	7   (0)|
+|*  3 |   TABLE ACCESS FULL| J	  | 50000 |   683K|    68   (0)|
+----------------------------------------------------------------
+</code>
+<fc #008000>**REPONSE: jointure HASH JOIN**</fc>
+<code ascii>
+REPONSE:
+     0=Affichage
+        |
+     1=Jointure et projection sur j.licence, c.nom
+       / \
+   3   3=Sélection des joueurs et proj sur cnum,licence
+=Club et proj sur cnum,nom.
+</code>
+</showif>
 c) Quelle table est lue en premier, J ou C, pourquoi ?
+<showif isloggedin>
+<fc #008000>table Club car plus petite en octets (60Ko) que l'autre (683Ko). Cf la colonne bytes.</fc>
+</showif>
 d) Quel est le coût de P1 ? Expliquer comment le coût est calculé en fonction des coûts d'accès à J et C.
+<showif isloggedin>
+<fc #008000>égal à la somme des coûts du parcours séquentiel de C et J : Cout(P1) = cout(C) + cout(J)
+</fc>
+</showif>
 === Question 2)  ===
 On considère la requête **R2**.
+<showif isloggedin>
+ <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>
 <code sql>
@@ Ligne 123: / Ligne 191: @@
 a) Afficher et dessiner le plan **P2** de cette requête.
+<showif isloggedin>
+<code>
+| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)|
+----------------------------------------------------------------
+|   0 | SELECT STATEMENT   |	  |  1000 | 26000 |    76   (2)|
+|*  1 |  HASH JOIN	   |	  |  1000 | 26000 |    76   (2)|
+|*  2 |   TABLE ACCESS FULL| J	  |  1000 | 14000 |    68   (0)|
+|   3 |   TABLE ACCESS FULL| C	  |  5000 | 60000 |	7   (0)|
+----------------------------------------------------------------
+Predicate Information
+----------------------
+- access(J.CNUM=C.CNUM)
+- filter(SALAIRE<11000)
+</code>
+<fc #008000>
+Réponse:
+=joueurs et selection et  proj
+=club et proj
+</fc>
+</showif>
 b) Quelle table est lue en premier J ou C, pourquoi ?
+<showif isloggedin>
+<fc #008000>
+la table Joueur car on ne sélectionne que 1000 Joueurs (sur 50 000 Joueurs) pour la jointure. La taille des 1000 Joueurs (14Ko) est plus petite celle des Clubs (60Ko).
+</fc>
+</showif>
 c) Quel est le coût de **P2** ?
+<showif isloggedin>
+<fc #008000>
+le coût de P2 est le même que P1 bien qu'on inverse l'ordre de lecture des relations.
+cout(C) + cout(J) = cout(J) + cout(C)
+</fc>
+</showif>
 /* question d) spécifique a l'UE BDR */
@@ Ligne 164: / Ligne 280: @@
 a) Affichez le plan **P3** de cette requête. Quel est le nom de l'opérateur de jointure ?
+<showif isloggedin>
+<code>
+| Id  | Operation		     | Name	| Rows	| Bytes | Cost (%CPU)|
+------------------------------------------------------------------------------
+|   0 | SELECT STATEMENT	     |		|    15 |   360 |    29   (0)|
+|   1 |  NESTED LOOPS		     |		|	|	|	     |
+|   2 |   NESTED LOOPS		     |		|    15 |   360 |    29   (0)|
+|*  3 |    TABLE ACCESS FULL	     | C	|     2 |    30 |     7   (0)|
+|*  4 |    INDEX RANGE SCAN	     | I_J_CNUM |    10 |	|     1   (0)|
+|   5 |   TABLE ACCESS BY INDEX ROWID| J	|    10 |    90 |    11   (0)|
+------------------------------------------------------------------------------
+Predicate Information :
+- filter(C.NOM='Barca' OR C.NOM='PSG')
+- access(J.CNUM=C.CNUM)
+</code>
+<fc #008000>
+**Reponse: NESTED LOOP
+**</fc>
+</showif>
 b) Détailler les étapes de l'évaluation
+<showif isloggedin>
+**<fc #008000>REPONSE :</fc>**
+  * Lire la table Club et sélectionner les 2 clubs. Proj sur division et cnum
+  * Itération pour chaque club c (parmi les 2 clubs):
+    * accéder à l'index pour obtenir la liste des rowid des joueurs du club c
+    * Pour chaque rowid
+      * lire le nuplet de joueur correspondant et proj sur licence
+</showif>
 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>
+**<fc #008000>REPONSE:</fc>**
+  * lire la table C: cout(lire C)=7 et sélectionner 2 clubs
+  * pour chaque club sélectionné:
+    * lire l'index : cout(lire index)=1 et obtenir 10 rowid
+    * pour chaque rowid
+      *  lire un joueur: cout(lire nuplet) =1
+cout(P3) = cout(C) + 2 * ( cout(index) + 10 * cout(lire nuplet) )
+=   7     + 2 * (1 + 10*1)
+= 29
+</showif>
@@ Ligne 189: / Ligne 359: @@
 Affichez le plan **P4** de cette requête, expliquer ses étapes et son coût.
+<showif isloggedin>
+<code>
+| Id  | Operation		      | Name	    | Rows  | Bytes | Cost (%CPU)|
+----------------------------------------------------------------------------------
+|   0 | SELECT STATEMENT	      | 	    |	  3 |	 63 |	  8   (0)|
+|   1 |  NESTED LOOPS		      | 	    |	    |	    |		 |
+|   2 |   NESTED LOOPS		      | 	    |	  3 |	 63 |	  8   (0)|
+|   3 |    TABLE ACCESS BY INDEX ROWID| J	    |	  3 |	 42 |	  5   (0)|
+|*  4 |     INDEX RANGE SCAN	      | I_J_SALAIRE |	  3 |	    |	  2   (0)|
+|*  5 |    INDEX UNIQUE SCAN	      | I_C_CNUM    |	  1 |	    |	  0   (0)|
+|   6 |   TABLE ACCESS BY INDEX ROWID | C	    |	  1 |	  7 |	  1   (0)|
+----------------------------------------------------------------------------------
+Predicate Information
+---------------------
+- access(J.SALAIRE>=10000 AND J.SALAIRE<=10001)
+- access(J.CNUM=C.CNUM)
+</code>
+<fc #008000> REPONSE:</fc>
+  * Accès à l'index sur le //salaire// (coût=2) pour obtenir les rowid des **3** joueurs qui ont le salaire demandé
+  * Pour chaque rowid,
+    * Lire le nuplet correspondant de la table Joueur et projeter sur //cnum// et //licence//
+    * Le cnum permet d'accéder a l'index sur le //cnum// de **Club** pour obtenir le rowid du club
+    * Le rowid permet de lire le nuplet correpondant de la table Club et projeter sur l'attribut //division//
+</showif>
@@ Ligne 195: / Ligne 394: @@
 ===== Exercice 2: Directives USE_NL et USE_HASH pour une jointure =====
 Objectif : comprendre la notion de choix entre 2 plans equivalents, basé sur le coût.
@@ Ligne 231: / Ligne 431: @@
 Expliquer le plan. Vérifier que son coût est supérieur à celui de P2.
+<showif isloggedin>
+<fc #008000>**REPONSE**</fc>
+<code>
+| Id  | Operation		     | Name	| Rows	| Bytes | Cost (%CPU)|
+------------------------------------------------------------------------------
+|   0 | SELECT STATEMENT	     |		| 50000 |  1269K| 50083   (1)|
+|   1 |  NESTED LOOPS		     |		|	|	|	     |
+|   2 |   NESTED LOOPS		     |		| 50000 |  1269K| 50083   (1)|
+|*  3 |    TABLE ACCESS FULL	     | J	| 50000 |   683K|    68   (0)|
+|*  4 |    INDEX UNIQUE SCAN	     | I_C_CNUM |     1 |	|     0   (0)|
+|   5 |   TABLE ACCESS BY INDEX ROWID| C	|     1 |    12 |     1   (0)|
+------------------------------------------------------------------------------
+Predicate Information
+-----------------------
+- filter("SALAIRE">10)
+- access("J"."CNUM"="C"."CNUM")
+</code>
+</showif>
@@ Ligne 247: / Ligne 466: @@
 Expliquer le plan. Vérifier que son coût est supérieur à celui de P3 (de l'exercice 1).
+<showif isloggedin>
+<fc #008000>**REPONSE**</fc>
+<code>
+| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)|
+----------------------------------------------------------------
+|   0 | SELECT STATEMENT   |	  |    15 |   360 |    76   (2)|
+|*  1 |  HASH JOIN	   |	  |    15 |   360 |    76   (2)|
+|*  2 |   TABLE ACCESS FULL| C	  |	2 |    30 |	7   (0)|
+|   3 |   TABLE ACCESS FULL| J	  | 50000 |   439K|    68   (0)|
+----------------------------------------------------------------
+Predicate Information
+----------------------
+- access("J"."CNUM"="C"."CNUM")
+- filter("C"."NOM"='Barca' OR "C"."NOM"='PSG')
+</code>
+</showif>
@@ Ligne 263: / Ligne 503: @@
 Expliquer le plan. Vérifier que son coût est supérieur à celui de P4.
+<showif isloggedin>
+<fc #008000>**REPONSE**</fc>
+<code>
+| Id  | Operation		     | Name	   | Rows  | Bytes | Cost (%CPU)|
+---------------------------------------------------------------------------------
+|   0 | SELECT STATEMENT	     |		   |	 3 |	63 |	13   (8)|
+|*  1 |  HASH JOIN		     |		   |	 3 |	63 |	13   (8)|
+|   2 |   TABLE ACCESS BY INDEX ROWID| J	   |	 3 |	42 |	 5   (0)|
+|*  3 |    INDEX RANGE SCAN	     | I_J_SALAIRE |	 3 |	   |	 2   (0)|
+|   4 |   TABLE ACCESS FULL	     | C	   |  5000 | 35000 |	 7   (0)|
+---------------------------------------------------------------------------------
+Predicate Information
+---------------------
+- access("J"."CNUM"="C"."CNUM")
+- access("J"."SALAIRE">=10000 AND "J"."SALAIRE"<=10001)
+</code>
+</showif>
@@ Ligne 307: / Ligne 567: @@
 |  J, C, F  |        |         |       |
 |  J, F, C  |        |         |       |
+<showif isloggedin>
+<fc #008000>**REPONSE**</fc>
+<fc #008000>**CFJ:**</fc>
+<code>
+-----------------------------------------------------------------
+| Id  | Operation	    | Name | Rows  | Bytes | Cost (%CPU)|
+-----------------------------------------------------------------
+|   0 | SELECT STATEMENT    |	   |	 1 |	40 |	82   (3)|
+|*  1 |  HASH JOIN	    |	   |	 1 |	40 |	82   (3)|
+|*  2 |   TABLE ACCESS FULL | J    |	 5 |	90 |	68   (0)|
+|*  3 |   HASH JOIN	    |	   |  2500 | 55000 |	13   (8)|
+|*  4 |    TABLE ACCESS FULL| C    |  2500 | 37500 |	 7   (0)|
+|   5 |    TABLE ACCESS FULL| F    |  5000 | 35000 |	 5   (0)|
+-----------------------------------------------------------------
+</code>
+<fc #008000>**CJF :**</fc>
+<code>
+------------------------------------------------------------------------------
+| Id  | Operation		     | Name	| Rows	| Bytes | Cost (%CPU)|
+------------------------------------------------------------------------------
+|   0 | SELECT STATEMENT	     |		|     1 |    40 |    81   (2)|
+|   1 |  NESTED LOOPS		     |		|	|	|	     |
+|   2 |   NESTED LOOPS		     |		|     1 |    40 |    81   (2)|
+|*  3 |    HASH JOIN		     |		|     5 |   165 |    76   (2)|
+|*  4 |     TABLE ACCESS FULL	     | C	|  2500 | 37500 |     7   (0)|
+|*  5 |     TABLE ACCESS FULL	     | J	|     5 |    90 |    68   (0)|
+|*  6 |    INDEX UNIQUE SCAN	     | I_F_CNUM |     1 |	|     0   (0)|
+|   7 |   TABLE ACCESS BY INDEX ROWID| F	|     1 |     7 |     1   (0)|
+------------------------------------------------------------------------------
+</code>
+<fc #008000>**FCJ :**</fc>
+<code>
+-----------------------------------------------------------------
+| Id  | Operation	    | Name | Rows  | Bytes | Cost (%CPU)|
+-----------------------------------------------------------------
+|   0 | SELECT STATEMENT    |	   |	 1 |	40 |	82   (3)|
+|*  1 |  HASH JOIN	    |	   |	 1 |	40 |	82   (3)|
+|*  2 |   TABLE ACCESS FULL | J    |	 5 |	90 |	68   (0)|
+|*  3 |   HASH JOIN	    |	   |  2500 | 55000 |	13   (8)|
+|   4 |    TABLE ACCESS FULL| F    |  5000 | 35000 |	 5   (0)|
+|*  5 |    TABLE ACCESS FULL| C    |  2500 | 37500 |	 7   (0)|
+-----------------------------------------------------------------
+</code>
+<fc #008000>**FJC :**</fc>
+<code>
+------------------------------------------------------------------------------
+| Id  | Operation		     | Name	| Rows	| Bytes | Cost (%CPU)|
+------------------------------------------------------------------------------
+|   0 | SELECT STATEMENT	     |		|     1 |    40 |    79   (2)|
+|   1 |  NESTED LOOPS		     |		|	|	|	     |
+|   2 |   NESTED LOOPS		     |		|     1 |    40 |    79   (2)|
+|*  3 |    HASH JOIN		     |		|     5 |   125 |    74   (2)|
+|   4 |     TABLE ACCESS FULL	     | F	|  5000 | 35000 |     5   (0)|
+|*  5 |     TABLE ACCESS FULL	     | J	|     5 |    90 |    68   (0)|
+|*  6 |    INDEX UNIQUE SCAN	     | I_C_CNUM |     1 |	|     0   (0)|
+|*  7 |   TABLE ACCESS BY INDEX ROWID| C	|     1 |    15 |     1   (0)|
+------------------------------------------------------------------------------
+</code>
+<fc #008000>**JCF :**</fc>
+<code>
+--------------------------------------------------------------------------------
+| Id  | Operation		       | Name	  | Rows  | Bytes | Cost (%CPU)|
+--------------------------------------------------------------------------------
+|   0 | SELECT STATEMENT	       |	  |	1 |    40 |    78   (0)|
+|   1 |  NESTED LOOPS		       |	  |	  |	  |	       |
+|   2 |   NESTED LOOPS		       |	  |	1 |    40 |    78   (0)|
+|   3 |    NESTED LOOPS 	       |	  |	5 |   165 |    73   (0)|
+|*  4 |     TABLE ACCESS FULL	       | J	  |	5 |    90 |    68   (0)|
+|*  5 |     TABLE ACCESS BY INDEX ROWID| C	  |	1 |    15 |	1   (0)|
+|*  6 |      INDEX UNIQUE SCAN	       | I_C_CNUM |	1 |	  |	0   (0)|
+|*  7 |    INDEX UNIQUE SCAN	       | I_F_CNUM |	1 |	  |	0   (0)|
+|   8 |   TABLE ACCESS BY INDEX ROWID  | F	  |	1 |	7 |	1   (0)|
+--------------------------------------------------------------------------------
+</code>
+<fc #008000>**JFC :**</fc>
+<code>
+--------------------------------------------------------------------------------
+| Id  | Operation		       | Name	  | Rows  | Bytes | Cost (%CPU)|
+--------------------------------------------------------------------------------
+|   0 | SELECT STATEMENT	       |	  |	1 |    40 |    78   (0)|
+|   1 |  NESTED LOOPS		       |	  |	  |	  |	       |
+|   2 |   NESTED LOOPS		       |	  |	1 |    40 |    78   (0)|
+|   3 |    NESTED LOOPS 	       |	  |	5 |   125 |    73   (0)|
+|*  4 |     TABLE ACCESS FULL	       | J	  |	5 |    90 |    68   (0)|
+|   5 |     TABLE ACCESS BY INDEX ROWID| F	  |	1 |	7 |	1   (0)|
+|*  6 |      INDEX UNIQUE SCAN	       | I_F_CNUM |	1 |	  |	0   (0)|
+|*  7 |    INDEX UNIQUE SCAN	       | I_C_CNUM |	1 |	  |	0   (0)|
+|*  8 |   TABLE ACCESS BY INDEX ROWID  | C	  |	1 |    15 |	1   (0)|
+--------------------------------------------------------------------------------
+</code>
+</showif>
@@ Ligne 323: / Ligne 695: @@
 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 339: / Ligne 736: @@
 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>
@@ Ligne 378: / Ligne 793: @@
 Documentation sur [[http://www-ari.ufr-info-p6.jussieu.fr/OUTILS/documentation/doc/oracle/doc10G-2/server.102/b14211/optimops.htm#i21299 | l'optimiseur]] d'Oracle
+[[http://www.oaktable.net/content/right-deep-left-deep-and-bushy-joins| comparaison des plans linéaires à gauche et à droite]]
 Aller vers  [[site:enseignement:master:bdr:start|BDR]]

Bases de Données / Databases

Outils pour utilisateurs

Outils du site

Différences

Outils de la page