Différences

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

--- site:enseignement:master:bdr:tmejointurerepartie [05/03/2018 11:29] – [Installation] hubert
+++ site:enseignement:master:bdr:tmejointurerepartie [20/03/2019 14:05] (Version actuelle) – [3) Requêtes réparties] hubert
@@ Ligne 9: / Ligne 9: @@
 L'objectif de ce TME est de comprendre l'évaluation d'une requête de jointure
 entre 2 relations qui sont situées sur 2 sites distincts.
-  * définir le schéma global qui offre un accès transparent à des données de plusieurs bases,
+  * Définir le schéma global qui offre un accès transparent à des données de plusieurs bases,
-  * formuler une requête répartie,
+  * Formuler une requête répartie,
-  * comprendre l'ordre et l'emplacement des opérations permettant d'évaluer une requête répartie (quel site traite quelles opérations?).
+  * Comprendre l'ordre et l'emplacement des opérations permettant d'évaluer une requête répartie (quel site traite quelles opérations?).
-=== Scénario ===
+==== Scénario ====
-On dispose de 2 SGBD : site 1 et site 2
+<code bash>
-Données:
+cd <votre repertoire de travail>
-  * Le site 1 contient les Clubs (table C),
+tar zxvf /Infos/bd/public/tmeJointureRep.tgz
-  * le site 2 contient les Joueurs (table J)
+cd tmeJointureRep
-La couche BDR est implémentée sur le site 1.
+emacs tmeJR.sql &
+</code>
-==== Installation ====
+On dispose de deux SGBD hébergés sur des sites différents
+  * Le site 1 s'appelle **ora11** :
+<code sql>
+-- se connecter en remplaçant 1234567 par votre numéro d'étudiant
+CONNECT E1234567/E1234567@ora11
+-- vérifier que la connexion est correcte. Le nom du SGBD qui doit s'afficher est : oracle
+select sys_context('USERENV', 'INSTANCE_NAME') from dual;
-Créer les tables J,C,F (déjà fait lors du TME précédent)
+</code>
+  * Le site 2 s'appelle **ora10** :
 <code sql>
-    @base3
+-- se connecter en remplaçant 1234567 par votre numéro d'étudiant
+CONNECT E1234567/E1234567@ora10
+-- vérifier que la connexion est correcte. Le nom du SGBD qui doit s'afficher est : ora10
+select sys_context('USERENV', 'INSTANCE_NAME') from dual;
 </code>
-Supprimer les joueurs J du site 1 (les joueurs seront stockés seulement sur le site 2)
+====Données ====
+  * Le site 1 **ora11** contient les Clubs dans la table **Club**(cnum, nom, division, ville)
+  * le site 2 **ora10** contient les Joueurs dans la table **Stagiaire**(licence, cnum, salaire, sport, profil)
+La couche BDR est implémentée sur le site 1 appelé **ora11**
+=====1) Installation =====
+Créer la table Club sur le site 1
 <code sql>
-    connect E1234567/E1234567@ora11
+    CONNECT ... @ora11
-    drop table J;
+    @tableClub
-    desc J (doit répondre: "table inconnue")
+    desc Club
 </code>
-Créer la table J des joueurs dans le site 2 (le serveur du site 2 s'appelle ora10)
+Créer la table Stagiaire sur le site 2
 <code sql>
-    connect E1234567/E1234567@ora10  --(avec votre propre numéro d'étudiant)
+    connect ... @ora10
-    @base3
+    @tableStagiaire
-    drop table C cascade constraints;
+    desc Stagiaire
-    drop table F;
 </code>
@@ Ligne 45: / Ligne 66: @@
 Relier les sites : La couche BDR (site1) doit pouvoir se connecter au site 2
 <code sql>
-    connect E1234567/E1234567@ora11
+    connect ... @ora11
     drop database link site2;
-    create database link site2 connect to E1234567 identified by "E1234567" using 'ora10'; --(avec votre propre numéro d'étudiant)
+    -- remplacer 1234567 par votre numéro d'étudiant
+    create database link site2 connect to E1234567 identified by "E1234567" using 'ora10';
 </code>
 Vérifier le bon fonctionnement du lien
 <code sql>
-    desc J@site2
+    connect ... @ora11
+    desc Stagiaire@site2
 </code>
+/* OLD
 Ajouter un club dans une nouvelle ville. Ce club n'a que 10 joueurs ce qui permettra, par la suite, de poser une requête de jointure très sélective.
 <code sql>
+    connect ... @ora11
    insert into C values( 6000, 'petit club', 2, 'Combourg');
 </code>
+*/
+===== 2) Construire le schéma global=====
-==== Construire le schéma global====
 <code sql>
-   create view J as
+    CONNECT ... @ora11
-      select *
+    create view Stagiaire as
-      from j@site2;
+    select *
+    from Stagiaire@site2;
 </code>
-==== Requêtes réparties====
+===== 3) Requêtes réparties=====
 Pour chaque requête, répondre aux questions
@@ Ligne 84: / Ligne 110: @@
 */
-== R1 : Jointure seule avec un transfert volumineux ==
+=== R1 : Jointure seule avec un transfert volumineux ===
-Afficher les joueurs avec leur club
+Afficher les stagiaires avec leur club
 <code sql>
-    select *
+    connect ... @ora11
-    from J, C
+    EXPLAIN plan FOR
-    where j.cnum = c.cnum;
+    SELECT s.prenom, s.profil, c.division
+    FROM Stagiaire s, Club c
+    WHERE s.cnum = c.cnum;
+    @p5
 </code>
-== R2 : jointure avec sélection ==
+=== R2 : Jointure avec sélection ===
 <code sql>
-    select *
+set linesize 120
-    from J, C
+    EXPLAIN plan FOR
-    where j.cnum = c.cnum
+    SELECT s.prenom, s.profil, c.division
-    and salaire > 59000
+    FROM Stagiaire s, Club c
+    WHERE s.cnum = c.cnum
+    AND s.salaire > 59000;
+    @p5
 </code>
@@ Ligne 104: / Ligne 136: @@
-== R3 Jointure très sélective ==
+=== R3 Jointure très sélective ===
-  * R3a : jointure très sélective et avec un transfert volumineux
+  * R3a : Jointure très sélective et avec un transfert volumineux
 <code sql>
-    select *
+    EXPLAIN plan FOR
-    from J, C
+    SELECT s.prenom, s.profil, c.division
-    where j.cnum = c.cnum
+    FROM Stagiaire s, Club c
-    and ville = 'Combourg';
+    WHERE s.cnum = c.cnum
+    AND c.ville = 'ville7';
+    @p5
 </code>
   * R3b : jointure très sélective et avec un transfert faible.
- La directive **driving_site** prend en argument le nom de la variable //j1// associée à la relation stockée sur le site dans lequel oracle doit traiter la jointure (i.e. le site 2).
+ La directive **driving_site** prend en argument le nom de la variable //s// associée à la table //Stagiaire// stockée sur le site dans lequel oracle doit traiter la jointure. Autrement dit, Oracle doit traiter la jointure sur le site 2 contenant Stagiaire.
 <code sql>
-    select /*+ driving_site(j1) */ *
+    EXPLAIN plan FOR
-    from J j1, C c1
+    SELECT /*+ driving_site(s) */ s.prenom, s.profil, c.division
-    where j1.cnum = c1.cnum
+    FROM Stagiaire s, Club c
-    and ville = 'Combourg';
+    WHERE s.cnum = c.cnum
+    AND c.ville = 'ville7';
+    @p5
 </code>
+===R4 : jointure et sélection avec index===
+Dans le site 2, créer un index sur Stagiaire(cnum).
+<code sql>
+    CONNECT ... @ora10
+    create index ....
+    @liste
+</code>
+Montrer que l'index Stagiaire(cnum) est utilisé pour **R3** (sélection sur la ville). Quelle partie de la requête est posée sur le site 2 ?  Combien de requêtes sont posées sur le site 2 pour obtenir le résultat complet de R3 ?
+Pourquoi l'index Stagiaire(cnum) n'est pas utilisé pour traiter la requête R2 ?
+/*pour illustrer les optimisations de requêtes réparties vues en cours.*/
+===R5: Proposer une requête avec 2 jointures entre 3 tables ===
+La première jointure traitée le site 2, la 2ème jointure sur le site 1.
+===R6: Proposer une requete avec 1 jointure entre deux table traitée par semi-jointure===
+Ajouter sur le site 1 une table Match(licence, annee, commentaire)
+  * transférer **le numéro de licence** des joueurs ayant fait un match en 2018 du site 1 vers le site 2. Ne **pas** transférer les commentaires des matchs.
+  * transférer les joueurs satisfaisant la requête du site 2 vers le site 1.
+  * finir le calcul de la requête sur le site 1
+===== 4) Durée des transferts =====
+Chronométrer les transferts en répétant n fois (n=10) l'exécution d'une requête afin d'obtenir des durées significatives mesurables.
+Pour mesurer principalement les transferts de données entre les sites et non la durée d'affichage du résultat, on modifie légèrement les requêtes : le résultat d'une requête est agrégé (par exemple avec un max). Ainsi, le résultat d'une requête sera "petit", il aura un seul nuplet. En conséquence, le temps d'affichage devient négligeable face à la durée des transferts de données entre les sites. Le temps total que l'on chronomètre (avec ''set timing on'') correspond principalement aux transferts de données.
+Comparer les deux exécution suivantes a) et b). Laquelle est la plus rapide ? Est elle beaucoup plus rapide ?
+  * a) Avec transfert de tous les Stagiaires
+<code plsql>
+set timing on
+declare
+  res number;
+begin
+  for i in 1 .. 10 loop
+     SELECT max(length(s.profil))
+     into res
+     FROM Stagiaire s, Club c
+     WHERE s.cnum = c.cnum
+     AND c.ville = 'ville7';
+  end loop;
+end;
+/
+set timing off
+</code>
+  * b) Avec transfert des clubs de la ville7
+<code plsql>
+set timing on
+declare
+res number;
+begin
+for i in 1 .. 10 loop
+    SELECT /*+ driving_site(s) */ max(length(s.profil))
+    into res
+    FROM Stagiaire s, Club c
+    WHERE s.cnum = c.cnum
+    AND c.ville = 'ville7';
+end loop;
+end;
+/
+set timing off
+</code>
+===== 5) Fragmentation=====
+/*
+TODO: solution à rédiger
+*/
+  * Fragmenter les Club par division : placer les clubs de première division sur le site 1 (table Club1) et ceux de deuxième division sur le site 2 (table Club2).
+  * Définir la **vue** Club réunissant tous les clubs des deux divisions.
+  * Est-ce que les 2 fragments sont accédés pour une requête affichant seulement les clubs de 1ère division ? Pourquoi ?
+<code sql>
+select *
+from Club c
+where c.division=1
+</code>
-==Proposer d'autres requête pour illustrer les optimisations de requêtes réparties vues en cours.==