Bases de Données / Databases

Site Web de l'équipe BD du LIP6 / LIP6 DB Web Site

Outils pour utilisateurs

Outils du site

Piste:
site:enseignement:licence:3i009:tmejointure

Différences

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

Lien vers cette vue comparative

Les deux révisions précédentes Révision précédente
Prochaine révision 		Révision précédente
site:enseignement:licence:3i009:tmejointure [20/09/2017 16:41]
hubert [Exercice 2: Directives USE_NL et USE_HASH pour une jointure] 		site:enseignement:licence:3i009:tmejointure [17/11/2020 00:37] (Version actuelle)
hubert [Exercice préliminaire]
Ligne 3: Ligne 3:
 ====== TME 6 Jointure ====== ====== 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, ​   * Ordre des jointures, ​
   * Coût d'une requête de jointure, ​   * 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.   * Avantage/​inconvénient d'​utiliser un index sur l'​attribut de jointure et/ou sur d'​autres attributs.
  
Ligne 17: Ligne 21:
 </​showif>​ </​showif>​
 ===== Préparation du TME ===== ===== 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^ ^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]] | | **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| | 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 : Les tables d'un club de sport  sont :
  
Ligne 39: Ligne 57:
   * **F** (cnum, budget, depense, recette)   * **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 ...    ​explain plan for SELECT ...
 </​code>​ </​code>​
 puis terminer chaque requête par puis terminer chaque requête par
     @p4     @p4
 +*/
  
  
Ligne 56: Ligne 79:
  </​fs>​  </​fs>​
 </​showif>​ </​showif>​
 +
 ===== Exercice préliminaire ​ ===== ===== Exercice préliminaire ​ =====
 Combien de n-uplets ont chacune des relations ? 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) 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> <code sql>
-explain plan for+--explain plan for
     select * from J;     select * from J;
-@p4+--@p4
 </​code>​ </​code>​
  
Ligne 134: Ligne 158:
  
 <showif isloggedin>​ <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>​ </​showif>​
  
Ligne 146: Ligne 170:
 </​code>​ </​code>​
  
-a) Afficher et dessiner **P2**.+a) Afficher et dessiner ​le plan **P2** ​de cette requête.
  
 <showif isloggedin>  ​ <showif isloggedin>  ​
Ligne 184: Ligne 208:
  
  
-b) Quel est le coût de **P2** ? +c) Quel est le coût de **P2** ? 
 <showif isloggedin>​ <showif isloggedin>​
 <fc #008000> <fc #008000>
Ligne 234: Ligne 258:
  
  
-b) détailler ​les étapes de l'​évaluation+b) Détailler ​les étapes de l'​évaluation
  
 <showif isloggedin>​ <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>​ <showif isloggedin>​
Ligne 264: Ligne 288:
  
 === Question 4) === === Question 4) ===
 +/*
 +Jointure par boucles imbriquées avec index sur l'​attribut de jointure
 +*/
  
 On considère la requête **R4**.  ​ 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.   * 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 494: Ligne 521:
 |  J, C, F  |        |         ​| ​      | |  J, C, F  |        |         ​| ​      |
 |  J, F, C  |        |         ​| ​      | |  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>​
 +
 +
 +
 +
  
 b) D'​après les résultats du tableau, quel(s) ordre(s) a un coût minimal. Quels index sont utilisés ? Vérifier que c'est bien l'​ordre choisi par l'​optimiseur **sans** la directive ORDERED. b) D'​après les résultats du tableau, quel(s) ordre(s) a un coût minimal. Quels index sont utilisés ? Vérifier que c'est bien l'​ordre choisi par l'​optimiseur **sans** la directive ORDERED.
Ligne 509: Ligne 640:
  
 Dessiner le plan obtenu. Expliquer son coût. 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 524: Ligne 675:
  
 Dessiner le plan obtenu. Expliquer son coût. 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>​
  
  
site/enseignement/licence/3i009/tmejointure.1505918465.txt.gz · Dernière modification: 20/09/2017 16:41 par hubert

Outils de la page

Sauf mention contraire, le contenu de ce wiki est placé sous les termes de la licence suivante : CC Attribution-Noncommercial-Share Alike 3.0 Unported
CC Attribution-Noncommercial-Share Alike 3.0 Unported Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki