Ci-dessous, les différences entre deux révisions de la page.
Prochaine révision | Révision précédente | ||
site:enseignement:master:bdr:tmejointurerepartie [04/01/2016 18:20] hubert créée |
site:enseignement:master:bdr:tmejointurerepartie [20/03/2019 14:05] (Version actuelle) hubert [3) Requêtes réparties] |
||
---|---|---|---|
Ligne 1: | Ligne 1: | ||
- | ====== TME jointure répartie ====== | + | {{indexmenu_n>3}} |
+ | ====== TME Jointure répartie ====== | ||
+ | /* | ||
lire le [[ http://www-bd.lip6.fr/ens/bdr2015/index.php/JointureR%C3%A9partie| sujet]] du TME sur la jointure répartie entre 2 tables gérées par 2 SGBD différents. | lire le [[ http://www-bd.lip6.fr/ens/bdr2015/index.php/JointureR%C3%A9partie| sujet]] du TME sur la jointure répartie entre 2 tables gérées par 2 SGBD différents. | ||
+ | */ | ||
+ | |||
+ | |||
+ | 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, | ||
+ | * 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?). | ||
+ | |||
+ | ==== Scénario ==== | ||
+ | <code bash> | ||
+ | cd <votre repertoire de travail> | ||
+ | tar zxvf /Infos/bd/public/tmeJointureRep.tgz | ||
+ | cd tmeJointureRep | ||
+ | emacs tmeJR.sql & | ||
+ | </code> | ||
+ | |||
+ | 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; | ||
+ | |||
+ | </code> | ||
+ | |||
+ | * Le site 2 s'appelle **ora10** : | ||
+ | <code sql> | ||
+ | -- 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> | ||
+ | |||
+ | ====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 ... @ora11 | ||
+ | @tableClub | ||
+ | desc Club | ||
+ | </code> | ||
+ | |||
+ | Créer la table Stagiaire sur le site 2 | ||
+ | <code sql> | ||
+ | connect ... @ora10 | ||
+ | @tableStagiaire | ||
+ | desc Stagiaire | ||
+ | </code> | ||
+ | |||
+ | |||
+ | Relier les sites : La couche BDR (site1) doit pouvoir se connecter au site 2 | ||
+ | <code sql> | ||
+ | connect ... @ora11 | ||
+ | drop database link site2; | ||
+ | -- 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> | ||
+ | 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===== | ||
+ | <code sql> | ||
+ | CONNECT ... @ora11 | ||
+ | create view Stagiaire as | ||
+ | select * | ||
+ | from Stagiaire@site2; | ||
+ | </code> | ||
+ | |||
+ | |||
+ | ===== 3) Requêtes réparties===== | ||
+ | |||
+ | Pour chaque requête, répondre aux questions | ||
+ | * Où est traitée chaque opération (sélection, projection, jointure, ...) ? | ||
+ | * Quelles sont les données transférées entre les sites pendant l'évaluation de la requête ? | ||
+ | |||
+ | /* | ||
+ | * Activer le mode de visualisation des plans et le chronométrage | ||
+ | <verbatim> | ||
+ | set timing on | ||
+ | set autotrace trace explain stat | ||
+ | </verbatim> | ||
+ | */ | ||
+ | |||
+ | === R1 : Jointure seule avec un transfert volumineux === | ||
+ | |||
+ | Afficher les stagiaires avec leur club | ||
+ | <code sql> | ||
+ | connect ... @ora11 | ||
+ | EXPLAIN plan FOR | ||
+ | SELECT s.prenom, s.profil, c.division | ||
+ | FROM Stagiaire s, Club c | ||
+ | WHERE s.cnum = c.cnum; | ||
+ | @p5 | ||
+ | </code> | ||
+ | |||
+ | === R2 : Jointure avec sélection === | ||
+ | <code sql> | ||
+ | set linesize 120 | ||
+ | EXPLAIN plan FOR | ||
+ | SELECT s.prenom, s.profil, c.division | ||
+ | FROM Stagiaire s, Club c | ||
+ | WHERE s.cnum = c.cnum | ||
+ | AND s.salaire > 59000; | ||
+ | @p5 | ||
+ | </code> | ||
+ | |||
+ | La sélection est-elle poussée sur le site 2 ? | ||
+ | |||
+ | |||
+ | === R3 Jointure très sélective === | ||
+ | * R3a : Jointure très sélective et avec un transfert volumineux | ||
+ | <code sql> | ||
+ | EXPLAIN plan FOR | ||
+ | SELECT s.prenom, s.profil, c.division | ||
+ | FROM Stagiaire s, Club c | ||
+ | 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 //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> | ||
+ | EXPLAIN plan FOR | ||
+ | SELECT /*+ driving_site(s) */ s.prenom, s.profil, c.division | ||
+ | FROM Stagiaire s, Club c | ||
+ | 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> | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== Divers ===== | ||
+ | |||
+ | |||
+ | Aller vers [[site:enseignement:master:bdr:start | BDR]] | ||
+ |