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?).

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; 

• 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

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

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

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 ?

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

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 ?

• 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

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 ?

La première jointure traitée le site 2, la 2ème jointure sur le site 1.

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

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

• 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

Aller vers SAM