Table des matières

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.

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

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

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

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

    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

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)

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 ?

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

SELECT *
FROM Club c
WHERE c.division=1

Divers

Aller vers BDR