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:master:bdr: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édentesRévision précédente
Prochaine révision		Révision précédente
site:enseignement:master:bdr:tmejointure [23/02/2018 16:48] – [Exercice préliminaire] hubertsite:enseignement:master:bdr:tmejointure [14/03/2019 09:51] (Version actuelle) – [Divers] hubert
Ligne 3: Ligne 3:
 ====== TME Jointure ====== ====== TME Jointure ======
  
-==version 2018==+/* 
 + 
 +                   TME pour BDR 
 + 
 +Voir l'énoncé du TME jointures en 3I009 
 + 
 + 
 +*/ 
 + 
 +==version 2019==
  
  
Ligne 13: Ligne 22:
  
  
-Le TME dure 2 séances. Faire l'ex1 pendant la première séance et l'ex2 pendant la deuxième. L'ex3 est facultatif.+Le TME dure 2 séances. 
  
  
 ** Ne pas oublier de consulter les <fc #ff0000>questions fréquentes</fc> en bas de cette page ** ** Ne pas oublier de consulter les <fc #ff0000>questions fréquentes</fc> en bas de cette page **
 +
 <showif isloggedin> <showif isloggedin>
-   +**<fc #008000>Vous êtes connecté !</fc>** en tant que membre de l'équipe BD
 </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 de TD:  Exercice 3: Club de joueurs   
Ligne 29: Ligne 40:
 | emacs tmeJointure.sql & | éditer le fichier à compléter pendant le TME| | 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]] | | **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 : Les tables d'un club de sport  sont :
  
Ligne 51: Ligne 62:
    explain plan for SELECT ...    explain plan for SELECT ...
 </code> </code>
 +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  ===== ===== Exercice préliminaire  =====
 Combien de n-uplets ont chacune des relations J, C, F et BigJoueur? 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> <code sql>
 explain plan for explain plan for
Ligne 60: Ligne 83:
 </code> </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 ? Combien de pages ont chacune des relations ?
Ligne 66: Ligne 88:
     select table_name, num_rows as cardinalite, blocks as nb_pages      select table_name, num_rows as cardinalite, blocks as nb_pages 
     from user_tables;     from user_tables;
 +    
 +    select table_name, num_rows as cardinalite, blocks as nb_pages 
 +    from all_tables
 +    where table_name='BIGJOUEUR';
 </code> </code>
  
Ligne 78: Ligne 104:
  
 ===== Exercice 1 : Jointure entre 2 relations ===== ===== Exercice 1 : Jointure entre 2 relations =====
 +
 +
 +
 +=== Question 1)  ===
 +
 +/* Jointure par hachage */
 +
 On considère la requête **R1**.   On considère la requête **R1**.  
 <code sql> <code sql>
-   select J.licence, C.nom +   explain plan for 
-   from J, C +       select J.licence, C.nom 
-   where J.cnum = C.cnum +       from J, C 
-   and J.salaire > 10;+       where J.cnum = C.cnum 
 +       and J.salaire > 1000; 
 +   @p4
 </code> </code>
  
-Ecrire en français ce que retourne la requête R1+a) Traduisez la requête en français.
  
-=== Question 1) Jointure par hachage === +<showif isloggedin> 
-Afficher le plan de la requête R1 et constater qu'il s'agit d'une jointure par hachage. +<fc #008000>Pour les joueurs dont le salaire est supérieur à 1000: leur n° de licence et le nom de leur club.</fc> 
-Dessiner l'arbre du plan d'exécution proposé par le SGBD. Numéroter les opérateurs de 0 à 3 (valeur du champ //Id//).+</showif>
  
-La jointure par hachage entre deux tables s'effectue en deux étapes : +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.
-  * Etape 1) lire entièrement la table de gauche (Table Access Full de C), la hacher sur la clé de jointure (ici c'est l'attribut //cnum//) pour créer en mémoire une HashMap<clé, {nuplet}>  +
-  * Etape 2) lire la relation de droite (Table Access Full de J) et pour chaque nuplet, effectuer la jointure (HASH_JOIN) en consultant la HashMap construite à l'étape 1.+
  
-a) Quel est le coût du plan ? Est-il égal à la somme des coûts du parcours séquentiel de J et C ? 
  
-Observer que le coût est le même lorsqu'on inverse l'ordre de lecture des relations : lire J avant C ou lire C avant J.  Remarque, l'ordre est fixé par la clause ''FROM'' quand on utilise la directive ''ordered''. +<showif isloggedin> 
-<code sql> + 
-   select /*+ ordered */ J.licence, C.nom +<code> 
-   FROM J, C  +REPONSE: 
-   where J.cnum = C.cnum +---------------------------------------------------------------- 
-   and J.salaire > 10;+| 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> </code>
 +
 +<fc #008000>**REPONSE: jointure HASH JOIN**</fc> 
 +
 +<code ascii>
 +REPONSE:
 +        0     0=Affichage
 +        | 
 +        1     1=Jointure et projection sur j.licence, c.nom
 +       / \
 +      2   3   3=Sélection des joueurs et proj sur cnum,licence
 +              2=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> <code sql>
-   select /*+ ordered */ J.licence, C.nom +   explain plan for 
-   FROM C, J  +       select J.licence, C.nom 
-   where J.cnum = C.cnum +       from J, C 
-   and J.salaire > 10;+       where J.cnum = C.cnum 
 +       and J.salaire < 11000; 
 +   @p4
 </code> </code>
  
-b) On remplace la table Joueur par une table plus grande 'BigJoueur'. On étudie la jointure par hachage entre les tables C et BigJoueur.+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  
 +---------------------- 
 +    1 - access(J.CNUM=C.CNUM) 
 +    2 - filter(SALAIRE<11000) 
 +</code> 
 + 
 + 
 +<fc #008000> 
 +Réponse: 
 + 
 +2=joueurs et selection et  proj 
 + 
 +3=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 */ 
 + 
 +d) On remplace la table Joueur par une table plus grande 'BigJoueur'. On étudie la jointure par hachage entre les tables C et BigJoueur.
 Observer que la jointure dans l'ordre ''C, BigJoueur'' a un coût plus petit que celle dans l'ordre ''BigJoueur, C'' Observer que la jointure dans l'ordre ''C, BigJoueur'' a un coût plus petit que celle dans l'ordre ''BigJoueur, C''
  
Ligne 127: Ligne 260:
 where j.cnum = c.cnum; where j.cnum = c.cnum;
 </code> </code>
-Le coût total de la jointure est la somme du coûts de lecture des tables plus le coût de constuire la HashMap (cf. colonne TmpSpc). En déduire le coût de construction de la HashMap.+ 
 +Sachant que le coût total de la jointure est la somme du coût de lecture des tables plus le coût de constuire la HashMap (cf. colonne TmpSpc). En déduire le coût de construction de la HashMap.
  
 Enlever maintenant la directive ''ordered'' et observer que le SGBD choisit de lire C avant BigJoueur car cela est moins coûteux. Enlever maintenant la directive ''ordered'' et observer que le SGBD choisit de lire C avant BigJoueur car cela est moins coûteux.
Ligne 133: Ligne 267:
  
  
-c) Observer que lorsque le prédicat de sélection sur le salaire n'est pas très sélectif, le plan est toujours le même. Répondre en remplaçant sal>10 par sal>100, ..., sal>1000.+=== Question 3) ===
  
-d) On étudie maintenant le cas où le prédicat de sélection est très sélectif. Expliquer le plan de la requête suivante. Détailler comment chaque valeur de la colonne **Cost** est calculée.+On considère la requête **R3**.  
 <code sql> <code sql>
-select J.licence, C.nom +explain plan for 
-from C, j +    select J.licence, C.division 
-where J.cnum = C.cnum +    from C, J 
-and salaire < 10050;+    where J.cnum = C.cnum 
 +    and C.nom in ('PSG', 'Barca'); 
 +@p4
 </code> </code>
 +
 +a) Affichez le plan **P3** de cette requête. Quel est le nom de l'opérateur de jointure ? 
 +
 +
 +<showif isloggedin>
  
 <code> <code>
-| Id  | Operation      | Name    | Rows  | Bytes | Cost (%CPU)| Time    | +| Id  | Operation      | Name | Rows | Bytes | Cost (%CPU)| 
--------------------------------------------------------------------------------------------- +------------------------------------------------------------------------------ 
-|   0 | SELECT STATEMENT      |    | 50 |  1300 | 60   (2)| 00:00:01 | +|   0 | SELECT STATEMENT      | |    15 |   360 |    29   (0)| 
-|*  1 |  HASH JOIN      |    | 50 |  1300 | 60   (2)| 00:00:01 | +|   1 |  NESTED LOOPS      | | | |      | 
-|   2 |   TABLE ACCESS BY INDEX ROWID| J    | 50 |   700 | 52   (0)| 00:00:01 | +|   2 |   NESTED LOOPS      | |    15 |   360 |    29   (0)| 
-|*  3 |    INDEX RANGE SCAN      | I_J_SALAIRE | 50 |    |  2   (0)| 00:00:01 | +|*  3 |    TABLE ACCESS FULL      | C |     2 |    30 |     7   (0)| 
-|   4 |   TABLE ACCESS FULL      | C    |  5000 | 60000 | 7   (0)| 00:00:01 | +|*  4 |    INDEX RANGE SCAN      | I_J_CNUM |    10 | |     1   (0)| 
--------------------------------------------------------------------------------------------- +|   5 |   TABLE ACCESS BY INDEX ROWID| J |    10 |    90 |    11   (0)| 
-Predicate Information: +------------------------------------------------------------------------------ 
-   1 - access("J"."CNUM"="C"."CNUM") + 
-   3 - access("SALAIRE"<10050)+Predicate Information : 
 +    3 - filter(C.NOM='Barca' OR C.NOM='PSG') 
 +    4 - access(J.CNUM=C.CNUM)
 </code> </code>
  
  
 +<fc #008000>
 +**Reponse: NESTED LOOP
 +**</fc>
 +</showif>
  
-===Question 2) Jointure par boucles imbriquées avec index sur l'attribut de jointure=== 
  
-a) Expliquer le plan de la requête **R2** suivante. Détailler comment chaque valeur de la colonne **Cost** est calculée.+ 
 +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> 
 + 
 + 
 + 
 + 
 +=== Question 4) === 
 +/* 
 +Jointure par boucles imbriquées avec index sur l'attribut de jointure 
 +*/ 
 + 
 +On considère la requête **R4**.  
 <code sql> <code sql>
-select J.licence, C.nom +explain plan for 
-from C, j +  select J.licence, C.division 
-where J.cnum = C.cnum +  from C, J 
-and salaire < 10006;+  where J.cnum = C.cnum 
 +  and J.salaire between 10000 and 10001; 
 +@p4
 </code> </code>
  
 +Affichez le plan **P4** de cette requête, expliquer ses étapes et son coût.
 +
 +
 +<showif isloggedin>
 <code> <code>
-| Id  | Operation       | Name     | Rows  | Bytes | Cost (%CPU)| Time     | +| Id  | Operation       | Name     | Rows  | Bytes | Cost (%CPU)| 
---------------------------------------------------------------------------------------------- +---------------------------------------------------------------------------------- 
-|   0 | SELECT STATEMENT       |     |   6 | 156 |  14   (0)| 00:00:01 | +|   0 | SELECT STATEMENT       |     |   3 |  63 |   8   (0)| 
-|   1 |  NESTED LOOPS       |     |     |     |  |     | +|   1 |  NESTED LOOPS       |     |     |     | | 
-|   2 |   NESTED LOOPS       |     |   6 | 156 |  14   (0)| 00:00:01 | +|   2 |   NESTED LOOPS       |     |   3 |  63 |   8   (0)| 
-|   3 |    TABLE ACCESS BY INDEX ROWID| J     |   6 |  84 |   8   (0)| 00:00:01 | +|   3 |    TABLE ACCESS BY INDEX ROWID| J     |   3 |  42 |   5   (0)| 
-|*  4 |     INDEX RANGE SCAN       | I_J_SALAIRE |   6 |     |   2   (0)| 00:00:01 | +|*  4 |     INDEX RANGE SCAN       | I_J_SALAIRE |   3 |     |   2   (0)| 
-|*  5 |    INDEX UNIQUE SCAN       | I_C_CNUM    |   1 |     |   0   (0)| 00:00:01 | +|*  5 |    INDEX UNIQUE SCAN       | I_C_CNUM    |   1 |     |   0   (0)| 
-|   6 |   TABLE ACCESS BY INDEX ROWID | C     |   1 |  12 |   1   (0)| 00:00:01 | +|   6 |   TABLE ACCESS BY INDEX ROWID | C     |   1 |   7 |   1   (0)| 
---------------------------------------------------------------------------------------------- +---------------------------------------------------------------------------------- 
-Predicate Information : + 
-   4 - access("SALAIRE"<10006) +Predicate Information 
-   5 - access("J"."CNUM"="C"."CNUM")+--------------------- 
 +    4 - access(J.SALAIRE>=10000 AND J.SALAIRE<=10001) 
 +    5 - access(J.CNUM=C.CNUM)
 </code> </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>
 +
 +
 +
 +
 +
 +===== 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.
 +
 +Etant donné une requête on voudrait construire un autre plan équivalent à celui que propose l'optimiseur, mais qui utilise un algorithme de jointure différent.
 +
 +Pour cela on ajoute une **directive** pour forcer l'usage d'un algorithme de jointure.
 +  * La directive ''USE_NL'' indique que la jointure doit être traitée par boucles imbriquées.
 +  * La directive ''USE_HASH'' indique que la jointure doit être traitée par hachage.
 +
 +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.
 +
  
 +=== Question 1 ===
 +On veut évaluer R1 avec une jointure par boucles imbriquées
  
-b) On veut utiliser l'index sur l'attribut de jointure //cnum// pour évaluer R1. Pour cela, on utilise une jointure par boucles imbriquées. La directive ''USE_NL'' indique que la jointure doit être traitée par boucles imbriquées. 
 <code sql> <code sql>
    select /*+ USE_NL(J,C) */ J.licence, C.nom    select /*+ USE_NL(J,C) */ J.licence, C.nom
Ligne 193: Ligne 415:
    and J.salaire > 10;    and J.salaire > 10;
 </code> </code>
-Dessiner l'arbre obtenu. Quelle table est parcourue séquentiellement une seule fois ? 
-L'index choisi sur //cnum// est-il celui de la table C ou J ? 
  
-Quel est le coût de ce plan ? Expliquer comment il est obtenu à partir +Expliquer le plan. Vérifier que son coût est supérieur à celui de P1. 
-des feuilles. + 
- Pourquoi est-il très supérieur au coût de la jointure par hachage pour laquelle on lit J et C une seule fois?+ 
 + 
 +=== Question 2 === 
 +On veut évaluer R2 avec une jointure par boucles imbriquées
  
-Inversement, on évalue R1 en utilisant l'index sur //cnum// de la table **J** en fixant l'ordre (on impose d'itérer sur C avant J). 
 <code sql> <code sql>
-   select /*+ USE_NL(J,C) ordered*/ J.licence, C.nom +   select /*+ USE_NL(J,C) */ J.licence, C.nom 
-   FROM C,J+   from J, C
    where J.cnum = C.cnum    where J.cnum = C.cnum
-   and J.salaire > 10;+   and J.salaire < 11000;
 </code> </code>
-Dessiner l'arbre obtenu. Expliquer pourquoi le coût est plus grand. Ce plan fait combien d'accès à l'index ? 
  
-c)  Jointure et sélection avec/sans index +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  
 +----------------------- 
 +   3 - filter("SALAIRE">10) 
 +   4 - access("J"."CNUM"="C"."CNUM") 
 +</code> 
 +</showif> 
 + 
 + 
 + 
 +=== Question 3 === 
 +On veut évaluer R3 avec une jointure par hachage
  
-Comparer les plans obtenus pour **R2** avec ou sans index 
 <code sql> <code sql>
-   select /*+ no_index(J I_J_salaire) */ J.licence, C.nom +explain plan for 
-   from J, C +  select /*+ USE_HASH(J,C) */ J.licence, C.division 
-   where J.cnum = C.cnum +  from C, J 
-   and J.salaire <10006;+  where J.cnum = C.cnum 
 +  and C.nom in ('PSG', 'Barca'); 
 +@p4
 </code> </code>
  
-===== Exercice 2. Jointure entre 3 relations ===== +Expliquer le plan. Vérifier que son coût est supérieur à celui de P3 (de l'exercice 1). 
-Soit la requête R3 :+ 
 + 
 +<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  
 +---------------------- 
 +   1 - access("J"."CNUM"="C"."CNUM") 
 +   2 - filter("C"."NOM"='Barca' OR "C"."NOM"='PSG') 
 +</code> 
 +</showif> 
 + 
 + 
 + 
 + 
 + 
 + 
 +=== Question 4 === 
 +On veut évaluer R4 avec une jointure par hachage 
 <code sql> <code sql>
-   select /*+ use_nl(J,C,F) */ c.nom, f.budget +explain plan for 
 +  select /*+ USE_HASH(J,C) */ J.licence, C.division 
 +  from C, J 
 +  where J.cnum = C.cnum 
 +  and J.salaire between 10000 and 10001; 
 +@p4 
 +</code> 
 + 
 +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 
 +--------------------- 
 +   1 - access("J"."CNUM"="C"."CNUM") 
 +   3 - access("J"."SALAIRE">=10000 AND "J"."SALAIRE"<=10001) 
 +</code> 
 +</showif> 
 + 
 + 
 + 
 + 
 + 
 + 
 + 
 + 
 + 
 +===== Exercice 3. ORDRE des jointures entre 3 relations ===== 
 +Soit la requête R5 : 
 +<code sql> 
 +explain plan for 
 +   select c.nom, f.budget 
    from J, C, F    from J, C, F
-   WHERE J.cnum = C.cnum AND C.cnum = F.cnum AND J.cnum = F.cnum+   where J.cnum = C.cnum and C.cnum = F.cnum and J.cnum = F.cnum
    and c.division=1 and J.salaire > 59000      and c.division=1 and J.salaire > 59000  
    and j.sport = 'sport1';    and j.sport = 'sport1';
 +@p4
 </code> </code>
- 
-La directive USE_NL permet d'imposer des jointures par boucles imbriquées afin d'utiliser les index sur l'attribut de jointure //cnum// de C et J et F. 
  
 On sait (cf. cours) qu'il y a 6 ordres de jointure possibles pour évaluer cette requête.  On sait (cf. cours) qu'il y a 6 ordres de jointure possibles pour évaluer cette requête. 
 +La directive ''ORDERED'' permet de fixer l'ordre des jointures. Attention le mot ''ORDERED'' est ajouté dans le <fc #9acd32>SELECT</fc> mais l'ordre est fixé dans le <fc #9acd32>FROM</fc>.
 +Par exemple, l'ordre //C,F,J// est fixé par '' FROM C, F, J'' de cette façon :
 +<code sql>
 +explain plan for
 +    select /*+ ORDERED */ C.nom, F.budget 
 +    from C, F, J
 +    where J.cnum = C.cnum and C.cnum = F.cnum and J.cnum = F.cnum
 +    and C.division=1 and J.salaire > 59000
 +    and J.sport = 'sport1';
 +@p4
 +</code>
  
-a) Compléter le tableau des ordres possibles :+a) Avec la directive ''ORDERED'' et en changeant le <fc #9acd32>FROM</fc>, évaluer les 6 ordres. 
  
-^  ordre  ^  coût  ^ +Expliquer chaque plan et compléter le tableau
-|  J, C, F  |  88  | +
-|  ...  |  ...  | +
-|  F, J, C  |  ...  |+
  
-D'après les résultats du tableau, dessiner l'arbre correspondant à l'ordre de moindre coût. Quels index sont utilisés ?+^  ordre  ^  1ère jointure  ^ 2eme jointure  ^ coût  ^ 
 +|  C, F, J  | HASH   | HASH    |   82  | 
 +|  C, J, F  |        |         |       | 
 +|  F, C, J  |        |         |       | 
 +|  F, J, C  |        |         |       | 
 +|  J, C, F  |        |         |       | 
 +|  J, F, C  |        |         |       |
  
-Rmq: veillez à bien expliciter les 3 prédicats de jointure dans la clause where.  En particulier le prédicat J.cnum = **F**.cnum est nécessaire pour les ordres JFC et FJC. 
  
-/** ANCIENNE REMARQUE ENLEVEE 
-Pourquoi l'ordre J, F, C n'utilise pas l'index sur F.cnum? Observer que le SGBD propose de faire un produit cartésien entre J et F au lieu d'une jointure. 
-Remplacer le prédicat de jointure  //J.cnum = C.cnum// par le prédicat équivalent //J.cnum = **F**.cnum//, déduit par transitivité, et observer que cela permet d'utiliser l'index sur F.cnum pour la jointure de J avec F. Que peut-on conclure concernant la capacité du SGBD à explorer tous les ordres de jointure équivalents ? 
-*/ 
  
-b) Modifier R3 en une requête R4 de jointure et de sélection avec index : modifier seulement la valeur du salaire (59000) pour que le plan obtenu choisisse l'index sur le salaire. Quel serait le coût de R4 sans utiliser l'index sur le salaire ?+<showif isloggedin> 
 +<fc #008000>**REPONSE**</fc>
  
  
-c) On force les index sur les attributs //"J.salaire"// et //"C.division"// pour évaluer R3+<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. 
 + 
 + 
 +c) Proposer un plan pour R5 qui utilise l'index sur le salaire avec la directive ''index(J I_J_salaire)''. Quel est l'ordre choisi par l'optimiseur ? 
 <code sql> <code sql>
-   select /*+ use_nl(J,C,F) index(C I_C_division) index(J I_J_salaire)*/ c.nom, f.budget +   select /*+ index(J I_J_salaire) */ c.nom, f.budget 
    from J, C, F    from J, C, F
-   where J.cnum = C.cnum and C.cnum = F.cnum+   where J.cnum = C.cnum and C.cnum = F.cnum and J.cnum = F.cnum
    and c.division=1 and J.salaire > 59000    and c.division=1 and J.salaire > 59000
    and j.sport = 'sport1';     and j.sport = 'sport1'; 
 </code> </code>
  
-Dessiner le plan obtenu.  Quel est l'inconvénient d'utiliser l'index sur l'attribut //division// ?+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> 
 + 
 + 
 + 
 + 
 + 
 + 
 +d) Proposer un plan pour R5 qui utilise l'index sur l'attribut division directive ''index(C I_C_division)''. Quel est l'ordre choisi par l'optimiseur ?
  
-===== Exercice 3. Forme des arbres de jointure (FACULTATIF) ===== 
-a) Soit R4: 
 <code sql> <code sql>
-   select /*+ use_nl(J,C,F) */ C.nom, F.budget, J.licence +explain plan for 
-   from J, C, F +    select /*+ index(C I_C_division) */  C.nom, F.budget  
-   where J.cnum = C.cnum and C.cnum = F.cnum   ;+    from J, C, F 
 +    where J.cnum = C.cnum and C.cnum = F.cnum and J.cnum = F.cnum 
 +    and C.division=1 and J.salaire > 59000 
 +    and J.sport = 'sport1'; 
 +@p4
 </code> </code>
  
-Dessiner l'arbre obtenu.  Justifier l'odre choisit. Pourquoi lire F en premier et J en dernier ?+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> 
 + 
  
-b) Compléter la clause WHERE de R4 pour obtenir  R5 de telle sorte que l'ordre choisi par le SGBD soit 
-C,F,J 
  
-c) Ajouter une 4ème table **Z** et proposer une requête de jointure entre les 4 tables de telle sorte que 
- le plan de moindre coût ne soit pas un arbre linéaire à gauche. C'est à dire que le coût de P1 soit inférieur à P2 avec : 
-  * P1  = (J join C) join (F join Z)  
-  * P2 =  ( (J join C) join F) join Z      ou tout autre arbre linéaire à gauche. 
  
  
Ligne 316: Ligne 794:
  
  
 +[[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]]+Aller vers  [[site:enseignement:master:bdr:start|BDR]]
  
  
site/enseignement/master/bdr/tmejointure.1519400915.txt.gz · Dernière modification : de hubert