Bases de Données / Databases

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

Outils pour utilisateurs

Outils du site


site:enseignement:master:sam:tmejointurerepartie

TME Jointure répartie

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

cd <votre repertoire de travail>
tar zxvf /Infos/bd/public/tmeJointureRep.tgz 
cd tmeJointureRep
emacs tmeJR.sql &

On dispose de deux SGBD hébergés sur des sites différents

  • Le site 1 s'appelle ora11 :
-- 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;
  • Le site 2 s'appelle ora10 :
-- 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; 

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

    CONNECT ... @ora11
    @tableClub
    DESC Club

Créer la table Stagiaire sur le site 2

    CONNECT ... @ora10  
    @tableStagiaire
    DESC Stagiaire

Relier les sites : La couche BDR (site1) doit pouvoir se connecter au site 2

    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'; 
 

Vérifier le bon fonctionnement du lien

    CONNECT ... @ora11
    DESC Stagiaire@site2

2) Construire le schéma global

    CONNECT ... @ora11
    CREATE VIEW Stagiaire AS
    SELECT *
    FROM Stagiaire@site2;

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 ?

R1 : Jointure seule avec un transfert volumineux

Afficher les stagiaires avec leur club

    CONNECT ... @ora11
    EXPLAIN plan FOR
    SELECT s.prenom, s.profil, c.division
    FROM Stagiaire s, Club c
    WHERE s.cnum = c.cnum;
    @p5

R2 : Jointure avec sélection

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

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
    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
  • 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.

    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

R4 : jointure et sélection avec index

Dans le site 2, créer un index sur Stagiaire(cnum).

    CONNECT ... @ora10
    CREATE INDEX ....
    @liste

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 ?

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
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
  • b) Avec transfert des clubs de la ville7
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

5) Fragmentation

  • 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 ?
SELECT *
FROM Club c
WHERE c.division=1

Divers

Aller vers SAM

site/enseignement/master/sam/tmejointurerepartie.txt · Dernière modification: 02/03/2020 11:55 par hubert