UPMC

Master d'informatique - M1
Saison fvrier - juin 2010

BDR Bases de Donnes Rparties

TD : poly n 1

Stphane GANCARSKI
Hubert NAACKE

URL : http://www-master.ufr-info-p6.jussieu.fr/2009/Ext/naacke/bdr2010/

TABLE DES MATIERES

1 Indexation 2
Extraits de partiels 2002 4
2003 5
2004 6

2 Optimisation de requtes 9
Extraits de partiels 2002 11
2003 12
2004 13
Partiel 2005 14

3 TME
Index 18
Jointure, 21
Annexe1 et 2 25
Jointure rpartie 27
JDBC 29
2PC 35
 Universit
e Pierre et Marie Curie - Paris 6. Master dinformatique.

UE BDR : Bases de Donn

ees R
eparties.

TD1 Techniques dindexation

Rappel : Arbres B+
Un arbre B+ est compose de noeuds tels que :
Le noeud racine contient au moins une cle. Un noeud intermediaire contient c cles avec d c 2 d (d
est xe en fonction lespace alloue pour un noeud). Seules les feuilles font reference aux donnees indexees.
Le nombre de noeuds `a traverser pour aller de la racine vers une feuille est constant (egal a` la profondeur
de larbre). .
1. Soit un noeud contenant c cles, combien contient-il de references vers ses noeuds ls ?
2. Soit un noeud contenant n cles {c1 , ..., cn } et n+1 references {r1 , ..., rn+1 }. On represente un noeud
par la liste (r1 , c1 , ..., ri , ci , ..., rn , cn , rn+1 ). Soit Ki lensemble des cles contenues dans le sous arbre
reference par ri . Les cles de Ki sont comprises dans lintervalle [a, b[. Quelles sont les valeurs a et
b pour K1 , Ki et Kn+1 ?

Exercice 1 : Arbres B+
On consid`ere un arbre B+ dont les nuds et les feuilles peuvent contenir au plus 4 cles et au minimum
(sauf la racine) 2 cles. A letat initial, la racine de larbre contient la cle 50. Un seul niveau intermediaire
contient (tous nuds confondus) les cles 8, 18, 32, 40 73, 85. Les feuilles contiennent (toutes feuilles
confondues) les valeurs 1, 2, 5, 6, 8, 10, 18, 27, 32, 39, 41, 45, 52, 58, 73, 80, 91, 99.
1. Dessinez letat inital de larbre.
2. Montrer letat de larbre apr`es linsertion de la cle 9.
3. Montrer letat de larbre resultant de linsertion dans larbre original de la cle 3. Combien de
lecture/ecriture de page cette insertion va-t-elle necessiter ?
4. On consid`ere la redistribution eventuelle des cles avec les feuilles voisines de meme p` ere. En
considerant uniquement la redistribution a` gauche, montrer letat de larbre resultant de la sup-
pression dans larbre original de la cle 8. Reprendre cette question en considerant uniquement la
redistribution a` droite.
5. Montrer letat de larbre resultant, a` partir de larbre original, de linsertion de la cle 46 suivie de
la suppression de la cle 52 (fusion eventuelle avec le voisin de droite).
6. Montrer letat de larbre resultant, a` partir de larbre original, de la suppression successive des cles
32, 39, 41, 45 et 73.

Exercice 2 : Arbres B+
On consid`ere un arbre B+ dont les nuds et le feuilles peuvent contenir au plus 4 cles et au minimum
(sauf la racine) 2 cles. A letat initial, la racine de larbre contient les cles 13, 17, 24, 30. Les feuilles
contiennent (toutes feuilles confondues) les valeurs2, 3, 5, 7, 14, 16, 19, 20, 22, 24, 27, 29, 33, 34, 38, 39.
On consid`ere uniquement la redistribution a` gauche.
1. Dessiner letat initial de larbre.
2. Donner 4 valeurs de cle telles que leur insertion successive puis leur suppression dans lordre inverse
resulte dans un etat identique a` letat initial.
3. Donner une valeur de cle dont linsertion suivie de la suppression resulte dans un etat dierent de
letat initial.
4. Combien au minimum faut-il inserer de cle pour que larbre gagne deux niveaux en hauteur ?

Exercice 3 (facultatif) : Arbres B+

On suppose quon dispose dun chier trie et quon veut construire un arbre B+ pour ce chier. La
methode la plus simple pour cela est dutiliser lalgorithme dinsertion et dinserer ls cles les unes apr`es
les autres. Quels sont les inconvenients de cette approche ? Proposer une autre approche qui remedie `a
ces probl`eme.

Exercice 4 : hachage statique

Rappeler les principes du hachage statique. Quels en sont les inconvenients lorsquun chier dimi-
nue ou augmente beaucoup. Quelle est la seule solution pour y remedier ? Peut-elle etre utilisee `a tout
moment ?
2
Exercice 5 : hachage dynamique (extensible)
Une structure de hachage extensible contient des entrees x dont les valeurs de hachage h(x) sont les
suivantes : 1, 4, 5, 7, 10, 12, 15, 16, 19, 21, 32.
1. Representer ces valeurs sous forme binaire.
2. Avec au plus 4 entrees par paquets, combien faut-il de paquets pour avoir la structure la plus
compacte possible ?
3. Dessiner la structure de hachage pour ces valeurs (repertoire et paquets, en indiquant profondeur
globale et profondeurs locales)
4. Expliquer pas a` pas comment retrouver le paquet contenant lentree 7 (tel que h(x) = 7).
5. Inserer 13
6. Inserer 20
7. Inserer 29
8. Donner un exemple de plus petit ensemble de cles `a supprimer pour obtenir une division par 2 du
repertoire.

Exercice 6 : hachage extensible

On consid`ere une structure de hachage extensible de profndeur globale 3. Dans letat initial, les entrees
du repertoire sont les suivantes : (000, *paquetA), (001, *paquetB), (010, *paquetC), (011, *paquetD),
(100, *paquetA2), (101, *paquetB), (110, *paquetC), (111, *paquetD). (000, *paquetA). Les entrees de
donnees sont les suivantes : 1, 4, 5, 7, 10, 12, 15, 16, 20, 21, 36, 51, 64.
1. Representer la structure compl`ete dans son etat initial.
2. Que peut-on dire de la derni`ere valeur inseree dans cette structure ?
3. Meme question si on suppose quil ny a pas eu de suppression depuis la derni`ere insertion. Cette
insertion a-t-elle cause un eclatement ? En cas de reponse negative, que peut-on dire de la derni`ere
insertion ayant cause un eclatement (on suppose toujours quil ny a pas eu de suppression depuis).
4. Inserer 68 dans la structure initiale.
5. Inserer 17 et 69 dans la structure initiale.
6. Supprimer 21 de la structure initiale.
7. Supprimer 10, puis 64 et enn 16 de la structure initiale.

Exercice 7 (facultatif) : hachage extensible

Montrer un exemple de structure de hachage extensible o` u linsertion de trois entrees provoque trois
eclatements et leur suppression (dans lordre inverse) remet la structure dans son etat initial.

Exercice 8 : Comparaison des m

ethodes dacc`
es
On consid`ere une relation R(a, b, c, d) contenant 1000000 de nuplets. Chaque page de donnee peut
contenir 10 nuplets. a est une cle candidate de R ( a [0, 1000000[ ) et les tuples de R sont stockes sur
le disque sans etre tries. Il y a M cles par feuille. On a le choix entre 3 methodes dacc`es :
Parcourir sequentiellement le chier (non trie) contenant R.
Utiliser un arbre-B+ sur R.a (on suppose la hauteur de cet arbre egale a` 3)
Utiliser une table de hachage sur R.a (on suppose que les paquets ont un taux doccupation de c )
On suppose que dans les 2 cas (arbre B+ et hachage), lorsquon acc`ede une entree de donnee dans la
structure, il sut dune lecture de page pour acceder ensuite au nuplet desire.
Pour chacune des requetes suivantes, evaluer le nombre dE/S necessaires pour chaque methode et
deduire quelle est la meilleure methode.
1. trouver tous les nuplets de R.
2. trouver tous les nuplets de R tels que a < 50
3. trouver tous les nuplets de R tels que a = 50
4. trouver tous les nuplets de R tels que a > 50 et a < 100

3
 Universit Pierre et Marie Curie - Paris 6

Extrait du partiel BDA (avril 2002)

Exercice 2 : Profondeur des arbres B+ pts
7
Soit la relation R(a1, a2, a3) contenant 10 tuples,
Lattribut a1 est la cl primaire, il est index par un arbre B+.
Lindex est dense : toute valeur distincte de a1 correspond une cl dune feuille de larbre.
Question 1
Le nombre de cl par nud est nc tel que :
nc [4,9] pour tout nud sauf la racine, nc [1,9] pour la racine
La profondeur p dun arbre correspond au nombre de niveaux, racine incluse, soit:
p = 1 pour un arbre rduit sa seule racine,
p = 2 pour un arbre ayant seulement une racine et des feuilles,
etc
Quelle est la profondeur minimale de lindex sur a1? Expliquez brivement votre rponse.
Question 2
Soit la requte R1 : Quelle est la valeur de a2 pour un a1 donn de valeur x ?
R1(x) : select a2 from R where a1 = x
Pour amliorer les performances de la requte R1, la structure de lindex sur a1 est modifie :
Le nombre maximal de cls par noeud est modifi,
La valeur de a2 est stocke directement dans les feuilles de larbre : chaque feuille contient un ensemble
de couples (cl, a2). Il ny a pas de liste chane entre les feuilles.
Lattribut a2 est une chane de 50 octets. La taille dune cl est 4 octets. La taille dune rfrence un
noeud est 8 octets. La taille dun nud est de 4000 octets.
Quel est le nombre maximum de couples (cl, a2) dans une feuille ?
Quel est le nombre maximum de cls dans un nud intermdiaire ?
Quelle est la profondeur minimale de lindex sur a1 ?
Exercice 3 : Arbres B+ : insertion et suppression de cls pts
Soit un arbre B+ dont le nombre de cl par nud est nc tel que :
nc [2,4] pour tout nud sauf la racine, et nc [1,4] pour la racine.
a) Dessiner larbre A1 ayant 2 niveaux tels que :
- la racine a les cls 10, 20, 23, 40.
- les feuilles ont les cls 1, 2, 4, 6, 10, 12, 14, 20, 22, 24, 30, 32, c, 42 avec (32< c <42).
b) Donner toutes les valeurs possibles pour la cl c.
c) Reprsenter larbre A2 aprs insertion de la cl 8 dans A1, sans redistribuer les cls avec les voisins.
En cas dajout dun nud, le nud cr doit contenir le nombre minimal de cls.

d) Reprsenter larbre A3 aprs suppression de la cl 8 dans A2. On considre la redistribution ventuelle

avec le voisin de gauche dabord.
e) Combien de cls au maximum peut on supprimer dans larbre A3 sans quil perde un niveau ? Donner
un exemple de cls que lon peut supprimer.
Exercice 4 : Fonction de hachage multi-attributs pt
Soit la relation Livre(num, mois, anne, titre). Les domaines sont : anne [1900-1999], mois [1-12].
La distribution des attributs est uniforme. Les livres sont indexs avec une seule table de hachage sur les
deux attributs mois et anne. Proposer une fonction de hachage h1(mois, anne) pour rpartir
uniformment les tuples dans 1200 paquets.
Proposer une fonction h2(mois, anne) pour rpartir uniformment les tuples dans 512 paquets.

4
 Universit Pierre et Marie Curie - Paris 6

Extrait du partiel BDR (avril 2003)

Exercice 1 : Arbre B+ 4 pts
Soit un arbre B+ avec 3 niveaux. Le nombre de cls par nud est tel que :
la racine et les nuds intermdiaires contiennent de 1 2 cls,
les feuilles contiennent de 2 3 cls,
Question 1 :
1.1) Dessiner l'arbre A1 ayant 3 niveaux tels que :
Les feuilles ont les cls 1, 4, 9,16, 25, 36, 49, 54, 61, 70, 81, 84, 87, 88, 95, 99.
Le niveau intermdiaire a les cls 9, 54, 70, 88
La racine contient 2 cls (pour les cls de la racine: choisir les plus petites valeurs possibles parmi les
cls des feuilles).

1.2) Reprsenter l'arbre A2 aprs insertion de la cl 32 dans A1, sans jamais redistribuer de cl avec les voisins.
Crer un nouveau nud en cas de dbordement d'une feuille ou d'un nud intermdiaire.

1.3) Soit l'arbre A3 aprs insertion de la cl 32 dans A1. L'arbre A3 est obtenu en redistribuant si possible les cls
des niveaux intermdiaires. Quel est le nombre de nuds de l'arbre A3 (racine, nuds intermdiaires et feuilles
inclus) ?
1.4) Reprsenter l'arbre A4 aprs suppression de la cl 16 dans A1. L'arbre A4 est obtenu en redistribuant si
possible les cls des feuilles avec les voisins.

1.5) Quel est le nombre minimum de cl supprimer dans A1 pour qu'il perde un niveau ? Justifier votre rponse.
Donner un exemple de cls supprimer (en choisissant des valeurs de cl les plus petites possibles).

Question 2 :
Soit la relation Produit(numro, prix). Les produits sont stocks dans l'ordre croissant du numro.
L'attribut numro est une cl, il est index par un arbre B+. Les attributs sont indpendants et leur
distribution est uniforme.
La relation Produit a 100 000 n-uplets, une page de donnes contient 100 n-uplets.
L'index est non dense (i.e., une seule cl par page de donnes).

2.1) Quel est le nombre minimum de cls au niveau des feuilles ? Justifier votre rponse.
2.2) Quel est le nombre minimal de niveaux de l'index ? Justifier votre rponse.

5
 Universit Pierre et Marie Curie - Paris 6

Extrait du partiel BDR (avril 2004)

Exercice 1 : Indexation par hachage (2004) 5 pts

On considre un index utilisant une technique de hachage extensible. La profondeur globale est indique
au dessus du rpertoire. La profondeur locale est indique au dessus de chaque paquet. Un paquet peut
contenir de 1 4 entres. Les entres sont des nombres entiers strictement positifs.
On utilise un algorithme de suppression complte, cest--dire que la suppression d'un paquet entrane la
diminution de la taille du rpertoire (division par 2), si cela est possible. On suppose quil y a fusion
seulement si un paquet est vide.
Question 1.
Soit T la taille du rpertoire de l'index. L'index contient au maximum T paquets, et 4*T entres
a) Quel est le nombre minimum de paquets de l'index ? Justifier et donner la profondeur locale des
paquets.
b) Soit Nmin est le nombre minimum d'entres de l'index. Que vaut Nmin ?
c) Soit un index avec Nmin entres dont les valeurs sont les plus petites possibles. Quelle est la valeur de
la plus grande entre de l'index ?

Question 2. On considre ltat suivant dun index utilisant une technique de hachage extensible.
a) Compltez l'tat initial de cet index de telle faon que :
L'tat initial est obtenu par plusieurs insertions, sans aucune suppression. Vous devez dterminer
des valeurs d'entres insrer, les plus petites possibles.
L'index dans l'tat initial doit contenir une entre telle que sa suppression ultrieure provoquera une
diminution de la profondeur globale.
b) Donnez ltat de lindex aprs suppression de cette entre.
Index complter : Cl supprimer : ...

profondeur globale Index aprs suppression :

profondeur locale
3 2

000 64 44 A

001
2
profondeur locale
010
9 25 5 B
011

100

101

110

111

6
 Universit Pierre et Marie Curie - Paris 6
Question 3. Soit l'index suivant dans l'tat initial E1.
Donnez une liste de trois valeurs e1, e2, e3, dentres dindex, telle que :
- leur insertion (dans l'ordre e1, e2, e3) dans lindex ci-dessous provoque 3 clatements,
- puis leur suppression dans l'ordre inverse (e3, e2, e1) redonne l'index initial E1.
Reprsenter l'tat E2 de lindex aprs insertion de ces trois valeurs.

Index initial (E1):

2 2

00 64 32 8 16 A

01
2

10 B
9 25 41 73

11
2

10 C

11 19 35 3 D

Entres insrer : e1=.. e2=.. e3=..

Index aprs insertion (E2):

Question 4. On veut modifier les valeurs initiales de lindex E1 de telle sorte que :
- l'insertion dans lindex (dans l'ordre e1, e2, e3) des entres dtermines la question prcdente,
provoque 3 clatements,
- la suppression de ces trois entres dans lordre inverse de leur insertion (supprimer dabord e3,
puis e2, puis e1) ne provoque pas de suppression de paquet.
Quel est le nombre minimum de valeurs d'entres modifier ?
Reprsenter l'index E1' dans l'tat initial modifi. Souligner les valeurs modifies. Si vous avez le choix
parmi plusieurs entres modifier, modifier l'entre la plus petite.

Exercice 2 : Arbre B+ (partiel 2004) 4 pts

On considre des arbres B+ contenant des donnes de type entier, tel que les nuds et les feuilles
contiennent au plus quatre valeurs. Les noeuds (sauf la racine) et les feuilles doivent tre au moins
moiti pleins (2 valeurs au moins). La hauteur d'un arbre est gale son nombre de niveaux. Un arbre de
hauteur 1 est rduit sa seule racine.
Question 1. Donnez un exemple darbre B+ dont la hauteur passe de 2 3 lorsquon y insre la valeur 25.
Donnez les deux arbres, avant et aprs linsertion. Utiliser la trame quadrille pour dessiner les nuds et
laisser un espace entre deux nuds. Rpondre page suivante.

7
 Universit Pierre et Marie Curie - Paris 6
Question 2. Donnez un exemple darbre B+ dans lequel la suppression de la valeur 25 conduit une
redistribution. Donnez les deux arbres, avant et aprs la suppression.
Question 3. Donnez un exemple darbre B+ dans lequel la suppression de la valeur 25 conduit une
fusion de deux nuds, mais ne modifie pas la hauteur de larbre. Donnez les deux arbres, avant et aprs la
suppression.

Exercice 3 : Arbres B+ (partiel 2005) 4 pts

On considre un arbre B+ tel qu'un nud quelconque peut contenir de 1 3 cls.
Question 1
a) Dessiner l'arbre rsultant de l'insertion successive des 10 cls 1, 2, ..., 10, dans l'ordre croissant, dans
un arbre initialement vide. En cas de dbordement d'une feuille, l'clater en 2 feuilles ayant chacune le
mme nombre de cls. Utiliser la trame quadrille pour dessiner les nuds et laisser un seul espace entre
deux nuds.
b) On considre l'insertion successive de N cls 1, 2, ..., N, dans l'ordre croissant, dans un arbre
initialement vide. Donner la valeur maximale de N lorsque l'arbre obtenu est de profondeur 3 (un seul
niveau intermdiaire).
c) On considre l'arbre de profondeur 3 qui a le nombre maximum de cls (sans se proccuper de la faon
d'obtenir cet arbre), et M le nombre total de cls dans ses feuilles. Nmax est il gal M ? Justifier.
Question 2
Donner une suite de 10 cls insrer successivement, dans un arbre vide afin d'obtenir un arbre de
profondeur 2 (pas de niveau intermdiaire), en choisissant la valeur des cls parmi les entiers dans [1,
10]. Puis reprsenter l'arbre obtenu.
Indication : Rpondre de manire insrer autant que possible les plus petites valeurs en premier. Parmi
toutes les rponses possibles, donner celle qui est la plus "proche" d'une suite croissante. C'est--dire,
donner la suite qui est compose d'un nombre minimum de sous-suites croissantes.
Exercice 4 : Arbres B+ (partiel 2005) 2 pts
On considre l'arbre B+ d'ordre 2 suivant :

20 n1

10 n2

6 8 n3 13 20 n4

1 2 3 8 9 6 7 10 11 13 14 20 21
n5 n6 n7 n8 n9 n10 30 40 n11

n12 22 27 n13 33 36 47 52 n14

22 23 24 25 27 28 30 31 33 34 36 37 40 43 47 49 52 55
n15 n16 n17 n18 n19 n20 n21 n22 n23

Trouver toutes les erreurs dans cet arbre. Indiquer le numro ni du noeud erron et expliquer brivement
l'erreur. S'il est possible de corriger l'erreur sans restructurer l'arbre, mais en modifiant seulement des
valeurs de cls, alors suggrer une correction.

8
Universit Pierre et Marie Curie Paris 6 page 13
Master d'informatique - BDR - v. :08/02/2010

TD 2a - Optimisation de requtes

Gnralits
1.Dcrire l'avantage d'un plan d'excution en pipeline pour un arbre de jointures

2.Pendant l'optimisation de requte, quel est le rle des statistiques collectes sur les donnes de la base ?

Exercice 1 : Attributs lire pour traiter une requte

1. Soit le schma relationnel:
Etudiant(num, integer, dnum: integer, nom:char(20), ge: integer)
Dept(dnum: integer, matire: char(20), salle: integer, budget: real)
Pour chaque requte SQL suivante, donner la liste des attributs qui doivent tre lu pour obtenir le rsultat de la
requte.
1.SELECT * FROM Etudiant
2.SELECT * FROM Etudiant, Dept
3.SELECT * FROM Etudiant E, Dept D WHERE E.dnum = D.dnum
4.SELECT E.num, D.matire FROM Etudiant E, Dept D WHERE E.dnum = D.dnum
5.SELECT COUNT(*) FROM Etudiant E, Dept D WHERE E.dnum = D.dnum
6.SELECT MAX(E.age) FROM Etudiant E, Dept D WHERE E.dnum = D.dnum
7.SELECT MAX(E.age) FROM Etudiant E, Dept D WHERE E.dnum = D.dnum AND D.salle = 5
8.SELECT E.dnum, COUNT(*) FROM Etudiant E, Dept D WHERE E.dnum = D.dnum GROUP BY D.dnum
9.SELECT D.salle, AVG(D.budget) FROM Dept D GROUP BY D.salle HAVING COUNT(*) > 2
10.SELECT D.salle, AVG(D.budget) FROM Dept D GROUP BY D.salle ORDER BY D.salle

Exercice 2 : Modle de cot

Lobjectif de cet exercice est de comparer deux mthodes pour le choix du plan dexcution dune requte. La
premire mthode est la transformation de plan base sur des heuristiques. La 2me mthode est la gnration
exhaustive de lespace de recherche associe au choix du plan candidat de moindre cot.
Soit le schma R1(A,B, ), R2(A,B, ) et R3(A, B, )
Soit la requte : select R1.A, R3.B from R1, R2, R3
where R1.A=R2.A and R2.A=R3.A and R1.B=1 and R2.B=2
1) Donner larbre algbrique correspondant en respectant lordre des prdicats donns dans la clause where.
2) Donner un arbre quivalent en appliquant les oprations les plus rductrices (restriction, projection)
dabord.
3) Soit le modle de cot simplifi suivant, o lunit de cot est laccs un tuple.
o Pour toute relation R, si card(R) est le nombre de tuples de R, le cot dune restriction sur galit est :
cot (att.=valeur (R)) = card(att.=valeur (R)) sil y a un index sur att. (arbre-B+ ou hachage),
= card(R) sinon.
o Le cot dune lecture squentielle est le cot dune restriction sans index.
o Pour toutes relations R ayant card(R) tuples et S ayant card(S) tuples, le cot dune equi-jointure est :
cot (R |><|att. S) = card(R) sil y a un index sur lattribut att. de S
= card(R)*card(S) sinon.
o Le cot dun oprateur trait en pipeline est nul.

9
Universit Pierre et Marie Curie Paris 6 page 14
Master d'informatique - BDR - v. :08/02/2010

o On suppose que R1, R2, R3 ont les caractristiques suivantes :

o il y a un index sur lattribut B de R1, A est cl primaire de R1, R2 et R3 (il existe un index plaant
pour chaque cl primaire)
o card(R1) = card(R2) = card(R3) = 1000, A(R1) = A(R2) = A(R3)
o il y a 10 valeurs possibles pour B, uniformment rparties dans R1 et aussi dans R2 et dans R3.
o pour chaque relation, la rpartition des valeurs de A en fonction de B est uniforme et est
indpendante de la rpartition dans les deux autres relations
Questions :
a) Quelle est la cardinalit du rsultat de la requte ?
b) Donner larbre algbrique de cot minimal et son cot (le nombre total daccs des tuples), en ignorant
les projections. Prciser votre rponse en dtaillant le cot et la cardinalit des rsultats intermdiaires.

Exercice 3 : Espace de recherche

Soit le schma relationnel :
Joueur(licence: integer, cnum : integer, salaire: integer, sport: char(20))
Club(cnum: integer, nom: char(20), division: integer, ville : char(10))
Finance(cnum: integer, budget: real, dpense: real, recette: real)
On considre la requte:
SELECT C.nom, F.budget
FROM Joueur J, Club C, Finance F
WHERE J.cnum = C.cnum AND C.cnum = F.cnum
AND C.division = 1 AND J.salaire > 59000 AND J.sport = 'aviron'

1.Dterminer un arbre d'oprateurs de l'algbre relationnel qui reflte l'ordre des oprations qu'un optimiseur de
requte peut choisir. Donner la taille lespace de recherche.

2. Pour rduire l'espace de recherche explor pendant l'optimisation, on considre seulement les arbres de
jointure qui n'ont pas de produit cartsien et qui sont linaires gauche. Donner la liste de tous les arbres de
jointure construits. Expliquer comment vous obtenez cette liste.

3. Les informations suivantes sont extraites du catalogue du SGBD :Les attributs Joueur.cnum, Joueur.salaire,
Club.division, Club.cnum et Finance.cnum sont index par un arbre B+ secondaire. Le salaire d'un joueur est
compris entre 10.000 et 60.000 EUR. Les joueurs peuvent pratiquer 200 sports diffrents. Un club est en
division 1 ou 2. La BD contient au total 50000 joueurs et 5000 club. Il y a un tuple d'information financire par
club. Le seul algorithme de jointure implment dans le SGBD est la jointure par boucles imbriques.
a) Pour chaque relation de la base de donnes (Joueur, Club et Finance) estimer le nombre de tuples
qui sont slectionns aprs avoir trait les prdicats de slection et avant de traiter les jointures.

b) D'aprs la rponse la question prcdente, quel est l'arbre de jointure de cot minimum que
l'optimiseur construit ?

Exercice 4 : Cot dune jointure

Soit deux relation R(X,Y) et S(Y,Z). Le nombre de tuples de R et S est T(R) et T(S).On T(S)<T(R)
Le nombre de bloc (ou page) pour stocker une relation est B(R) et B(S).
La taille de la mmoire disponible est de M blocs. M < B(S) < B(R)
Dterminer le nombre N minimal de bloc lire pour traiter lequi-jointure de R avec S selon lalgorithme de
jointure utilis :
1) jointure par boucle imbrique en lisant les relations tuple par tuple.
2) jointure par boucle imbrique en lisant les relations bloc par bloc
3) jointure par tri fusion.

10
 Universit Pierre et Marie Curie - Paris 6 - UFR 922 - Matrise d'informatique

EXTRAIT DU PARTIEL BDA (AVRIL 2002)

Exercice 5 : Dictionnaire pour les statistiques pts

Un SGBD maintient des statistiques sur les donnes de la BD. Priodiquement et si les donnes ont t
mises jour, le SGBD actualise les statistiques au moyen de requtes SQL.
Soit la relation R(a1, a2, a3). La distribution des valeurs des attributs de R est uniforme.
a) Donner les requtes SQL pour calculer les statistiques suivantes :
Les bornes du domaine de a1
Le nombre de valeurs distinctes de a2
b) Donner une requte SQL pour vrifier que a2 et a3 sont indpendants. Expliquer comment interprter
le rsultat de la requte.
c) Le dictionnaire du SGBD est modifi pour grer la distribution non uniforme des attributs
Relation(nomRel, cardinalit), Attribut(nomRel, nomAtt, val, freq)
Un attribut est identifi par son nom nomAtt et le nom de sa relation nomRel. ; val reprsente une valeur
de lattribut ; freq reprsente le nombre de tuples dont lattribut nomAtt vaut val.
Ecrire, en pseudo-code, un algorithme pour collecter ces statistiques en utilisant des ordres SQL pour
manipuler les donnes du dictionnaire.
Donner en fonction de R, att et v, la requte SQL (sur les relations du dictionnaire) pour dterminer le
facteur de slectivit (SF) de loprateur de slection sur la relation R avec le prdicat att<v lorsque la
distribution de lattribut att est non uniforme. (ie. SF( att<v (R)))

Exercice 6 : Optimisation de requtes pts

Soit les relations R(a,b), S(c), la distribution des attributs est uniforme,
Le domaine des attributs est : a[1,1000], b[1,10], c[1,100].
La cardinalits des relations sont : card(R) = 1000, card(S)= 100
La taille en nb de pages des relations est : P(R) = 500, P(S) = 10
Le modle de cot simplifi est :
Le cot dune jointure entre A et B, avec le prdicat A.att = B.att est :
cot(A A.att=B.att B) = card(A) * cot ( att=x (B)) o x est une valeur quelconque.
Le cot dune slection sur A avec le prdicat att=val est :
si att est index : cot( att=val (A)) = card(( att=val (A))
sinon : cot( att=val (A)) = P(A).
Le cot dune lecture squentielle est le cot dune slection sans index.
Le cot dun oprateur trait en pipeline est nul.
Soit la requte : select * from R,S where a=c and b =1
Pour chaque cas a), b) et c), reprsenter le plan dexcution de cot minimal de la requte, sous la forme
dun arbre doprateurs. Donner le cot et la cardinalit de chaque oprateur de larbre, donner le cot
total de larbre.
a) il y a seulement un index sur R.a et un index sur R.b,
b) il y a seulement un index sur S.c,
c ) il y a seulement un index sur R.b et un index sur S.c.

11
 Universit Pierre et Marie Curie - Paris 6

Extrait du partiel BDR (avril 2003)

Exercice 1 : Optimisation 6 pts
On considre la jointure naturelle des relations R(a,b), S(c,b), T(c,d) et U(a,d).
Pour traiter un arbre de jointures, le SGBD excute les jointures les unes aprs les autres, en matrialisant
le rsultat d'une jointure avant de traiter la jointure suivante.
Le cot d'un arbre de jointures est le nombre de n-uplets matrialiss dans les relations intermdiaires.
On souhaite trouver larbre de jointures de cot minimum.

Le modle de cot d'une jointure est donn par la rgle :

cot ( A B) = card ( A B ) pour un nud interne (car la relation intermdiaire est

matrialise)
= 0 pour la racine (car le rsultat est directement envoy au processus client)

Les relations R et U ont 1000 n-uplets alors que S et T ont 100 n-uplets.Il y a 100 valeurs diffrentes pour
tous les attributs de toutes les relations except pour S.c et T.c qui ont seulement 10 valeurs diffrentes.
On note ces informations de la manire suivante :

card(R) = card(U) = 1000

card(S) = card(T) = 100
nb_val(R.a) = nb_val(R.b) = nb_val(S.b) = nb_val(T.d) = nb_val(U.a) = nb_val(U.d) = 100
nb_val(S.c) = nb_val(T.c) = 10

La cardinalit du rsultat dune jointure est donne par la formule :

card(A B) = card(A) * card(B) / max(nb_val(A.x), nb_val(B.x)) (x est lattribut de

jointure)

On rappelle que la jointure naturelle de deux relations est une qui-jointure sur les attributs de mmes
noms.
Question 1 :
On considre uniquement les arbres linaires gauches de la forme :

p4

p3

p1 p2
1.1) Parmi R, S, T, U, quelles relations peuvent tre la place p1 ?
1.2) Si R est la place p1, quelles relations peuvent tre la place p2 ?
1.3) Combien existent-ils darbres linaires gauches quivalents pour la jointure naturelle de R, S, T, U ?
1.4) Donner un des arbres linaires gauches de plus faible cot ? Donner son cot.
Question 2 :
Si on considre maintenant tous les arbres de jointures : les arbres linaires gauches, les arbres linaires droits et
les arbres quilibrs (dits touffus).
Quel est un des arbres de plus faible cot ? Donner son cot.

12
 Universit Pierre et Marie Curie - Paris 6

Extrait du partiel BDR (avril 2004)

Exercice 3 : Optimisation de requtes 4 pts
Soit le schma relationnel dcrivant l'organisation d'un laboratoire en dpartements contenant des employs et des
projets.
Emp (Enum, salaire, age, Dnum)
Dept (Dnum, Pnum, budget, statut)
Proj (Pnum, code, description)
Tous les attributs sont des nombres entiers sauf statut et description qui sont des chanes de caractres. Les attributs
souligns forment une cl. La cl de Dept est compose de deux attributs, (l'attribut Dnum seul n'est pas une cl
primaire de Dept).
La taille d'un n-uplet est respectivement de 20 octets pour Emp, 40 octets pour un Dept et 2000 octets pour Proj. Le
nombre de n-uplets par relation et respectivement de 20 000 pour, Emp, 5000 pour Dept et 1000 pour Proj. La taille
d'une page, sur le disque ou en mmoire, vaut 4000 octets. La fonction page(R) retourne le nombre de pages
contenant les n-uplets de R.
La distribution des valeurs des attributs est uniforme. Les attributs sont indpendants. Le domaine de l'ge des
employs est l'ensemble des nombres entiers de 20 69 inclus : {20, 21, , 69}. Le domaine des budgets est
l'ensemble des multiples de 1000 inclus dans ]100 000, 600 000].
On suppose que tous les index sont des arbres B+. Un index sur l'attribut A est dit plaant si les donnes sont tries
sur le disque dans l'ordre des valeurs de A. Un index sur l'attribut A est dit non plaant si les donnes ne sont pas
tries sur le disque dans l'ordre de A.
L'estimation du cot des oprations repose sur le modle suivant:
Le cot d'une lecture squentielle de la relation R est gal au nombre de pages de R.
Le cot d'une slection avec un prdicat pred de la forme a op v
o a est un attribut de type entier et op est l'oprateur = (gal) , < (infrieur ) ou < (suprieur )

cot( pred (R) ) = card( pred (R)) si l'attribut a est index par un index non plaant,

cot( pred (R) ) = page(R) * card( pred (R)) / card(R) si l'attribut a est index par un index plaant,

cot( pred (R) ) = page(R) si l'attribut a n'est pas index.

Prcisez clairement toute hypothse supplmentaire que vous jugez ncessaire pour rpondre aux questions poses.
Question 1. Soit la requte R1:
select * from Emp where age=30
a) Quel est le cot pour traiter R1 s'il existe un index non plaant sur l'attribut age ?
b) Quel est le cot pour traiter R1 s'il existe un index plaant sur l'attribut age ?
Question 2. Soit la requte R2
select * from Proj where code=20
On suppose que la cardinalit de R2 est identique celle de R1.
a) Quel est le cot pour traiter R2 s'il existe un index non plaant sur l'attribut code ?
b) Quel est le cot pour traiter R2 s'il existe un index plaant sur l'attribut code ?
Question 3. Soient n un nombre entier et la requte R3 dpendant de n :
select * from Dept where budget > n
On suppose qu'il existe un index non plaant sur l'attribut budget.
Pour quelles valeurs de n, la lecture squentielle de Dept est moins coteuse que l'accs par index, pour traiter R3 ?
Rpondre en donnant toutes les valeurs de n.

13
 1

BDR
EXTRAIT Partiel du 5 avril 2005
Exercice 2 : Optimisation de requtes 10 pts
Soit le schma suivant :
Emp (ne, salaire, age, ns) -- un employ est identifi par son numro ne
Service (ns, np, budget, statut) -- ns fait rfrence un numro de service.
Projet (np, code, libell) -- un projet est identifi par son numro np
La taille d'un n-uplet est de 20 octets pour Emp, 40 octets pour Service, 2000 octets pour Projet. Les
attributs ne, ns et np ont chacun 4 octets. La cardinalit des relations est de 20 000 pour Employs, 5000
pour Service et 1000 pour Projet. La relation Service reprsente l'association N-M d'un service avec un
projet. La cl de Service est compose des attributs ns et np (ainsi, l'attribut ns n'est pas unique dans
Service).
Chaque Service, identifi par ns a en moyenne 10 Projets. Les donnes sont stockes sur disque dans des
pages de 4000 octets. Les attributs sont indpendants et leur distribution est uniforme.
Soient les fonctions auxiliaires :
ntp(R) le nombre de tuples par pages pour la relation R,
D(R, c) le nombre de valeurs distinctes du domaine de l'attribut R.c,
largeur(R) la taille d'un tuple de R,
et arr(x) l'arrondi de x par excs une valeur entire,
Un index sur l'attribut c est dit plaant si les donnes sont tries sur le disque dans l'ordre des valeurs de
c. Un index sur l'attribut c est dit non plaant si les donnes ne sont pas tries sur le disque dans l'ordre
de c.
L'estimation du cot des oprations repose sur le modle suivant. Le modle de cot estime le nombre de
pages lire et crire, sans prendre en compte l'criture du rsultat final.

Le cot d'une lecture squentielle de la relation R est gal au nombre de pages de R (not page(R)).
Le cot d'une slection avec un prdicat pred de la forme a op v o a est un attribut numrique et op est
l'oprateur = (gal), < (infrieur ) ou < (suprieur ), est :
cot( pred (R) ) = card( pred (R)) si l'attribut a est index par un index non plaant,
cot( pred (R) ) = arr(page(R) * card( pred (R)) / card(R)) si l'attribut a est index par un index plaant,
cot( pred (R) ) = page(R) si l'attribut a n'est pas index.
Le cot d'une jointure naturelle avec un prdicat de jointure pred de la forme R.c = S.c dpend de
l'algorithme utilis et de la prsence d'index sur les attributs de jointure. Les algorithmes de jointure sont
numrots J1 J9 :
J1: jointure par boucles imbriques, l'itration sur R imbrique l'itration sur S, sans utiliser aucun
index.
J2: jointure avec itration sur R et accs par index plaant sur S.c
J3: jointure avec itration sur R et accs par index non plaant sur S.c
J4 : jointure par tri de R et S selon c, puis fusion sans utiliser aucun index
J5 : jointure par fusion, avec des index plaants sur R.c et sur S.c
J6 : jointure par fusion, avec des index non plaants sur R.c et sur S.c

14
 2

J7 : jointure par tri de R selon c, puis fusion en utilisant un index plaant sur S.c
J8 : jointure par tri de R selon c, puis fusion en utilisant un index non plaant sur S.c
J9 : cration d'une table de hachage (non plaante) de R sur c, puis jointure avec itration sur S et
accs R par la table de hachage.
On propose 9 formules de cot :

C1: cot( R pred (S) ) = page(R) + card(R) x card(S) / D(S, c)

C2: cot( R pred (S) ) = 3 x page(R) + card(S)

C3 : cot( R pred (S) ) = 3 x (page(R) + page(S))

C4 : cot( R pred (S) ) = page(R) + page(S)

C5: cot( R pred (S) ) = page(R) + card(R) x page(S)

C6: cot( R pred (S) ) = page(R) + card(R) x arr( page(S) / D(S, c))

C7: cot( R pred (S) ) = page(R) + page(S) + card(S) x card(R) / D(R,c)

C8: cot( R pred (S) ) = 3 x page(R) + page(S)

C9: cot( R pred (S) ) = card(R) + card(S)

Prcisez clairement toute hypothse supplmentaire que vous jugez ncessaire.
Question 1
1) Donner le nombre de services distincts et le nombre moyen de services par projet
2) Donner le nombre de tuples par page des 3 relations.
3) Donner la taille des relations en nombre de pages.
4) Combien de pages sont ncessaires pour stocker conjointement tous les tuples de R qui ont une mme
valeur pour l'attribut c ? Donner une formule en fonction de R, c et des notations dfinies ci-dessus.

Question 2
1) Parmi C1 C9, quelles sont les formules symtriques (i.e., pour lesquelles R et S ont le mme rle)
2) Parmi J1 J9, quels sont les algorithmes symtriques (i.e., pour lesquels R et S ont le mme rle)
3) Associer chaque algorithme de jointure avec la formule la plus approprie. Expliquer vos choix en une
phrase. Rpondre en compltant le tableau. Indication :
Cot Explication

J1: C5 page(R) : lire R une seule fois

card(R) * page(S): lire S autant de fois qu'il y a de tuples dans R
Question 3.
Soit la requte R1:
Select *
From Emp e, Service s
Where e.ns = s.ns
1) On suppose qu'il existe seulement un index plaant sur Emp.ns. Dire, pour chaque algorithme J1 J9,
s'il est utilisable pour traiter R1? Si oui, donner le cot du plan d'excution de R1. Sinon expliquer
brivement pourquoi.

15
 3

2) On suppose maintenant qu'il existe un index plaant sur Emp.ns et un index plaant sur Service.ns.
Quel est l'algorithme de cot minimal ? Donner son cot.
3) On suppose maintenant qu'il existe seulement un index plaant sur Service.ns. Le SGBD possdant
seulement 7 pages disponibles en mmoire, on choisit de traiter R1 avec l'algorithme suivant :
Etape 1: Lire Emp par blocs de 6 pages (1 page mmoire tant rserve pour crire le rsultat du
tri), et crer des sous-listes tries.
Etape 2: Tant que le nombre de sous-listes est suprieur ou gal 6 :
fusionner 6 sous-listes en 1 seule liste, crire le rsultat sur disque.
Etape 3: Fusionner les listes restantes avec la relation Service

a) Donner le cot de R1, en utilisant cet algorithme, en nombre de pages (lues ou crites).
b) Combien faut-il de pages mmoire au minimum pour pouvoir utiliser J7 ?
Question 4.
Rappel des formules valuant la cardinalit de la slection et de la jointure :
card(F (R)) = SF( (F) ) card(R)
o SF( A = valeur) = 1 / D(R, A)
SF( A > valeur) = (max(A) valeur) / (max(A) min(A))
SF( A < valeur) = (valeur min(A)) / (max(A) min(A))

card ( R A=B S ) = card(S) si A est cl de R, et B est cl trangre de S.

sinon card ( R A=B S ) = SFj *card(S)*card(R) o SFj = 1 / max( D(R, A), D(S, B) )

On donne SFj = 1/500 pour la jointure entre Emp et Serv.

Soit la requte R2 :
Select E.ne, S.ns, P.np
From Emp E, Service S, Projet P
Where E.Salaire = 50000
and S.budget > 20000
and E.ns=S.ns and S.np=P.np

On suppose une rpartition uniforme des salaires (par tranche de 5000 dans lintervalle [10000, 105000])
et des budgets (dans lintervalle [10000, 30000]). Il y a un index plaant sur lattribut salaire de Emp, sur
lattribut ns de Service et sur lattribut np de Projet.
On considre le plan dexcution P1 suivant :

16
 4

E.ne,S.sn,P.np

2me jointure
np

1re jointure
Projet
ns

sal = 50 000 budget > 20 000

Emp Service
1) Estimez le nombre de n-uplets rsultant de chacune des oprations.

card ( sal = 50 000 (Emp) ) =

card ( budget > 20 000 (Service) ) =

card ( ( sal = 50 000 (Emp)) ns ( budget > 20 000 (Service)) ) =

card (rsultat final) =

2) Calculez le cot du plan dexcution P1, en prcisant les algorithmes de jointure utiliss.

cot ( sal = 50 000 (Emp) ) = ...

cot ( budget > 20 000 (Service) ) = ...

cot (1re jointure) = ...

cot (2me jointure) = ...

cot total = ...

3) Donnez un plan dexcution de cot optimal pour la requte R2 et calculez son cot.

4) On suppose maintenant que lindex sur lattribut np de Projet est non plaant. Calculez le cot du plan
optimal sous cette hypothse.
5) Mme question en supposant que les index sur salaire et sur l'attribut ns de Service sont non-plaants.
Justifiez votre rponse.

17
bdr2010 - Les Index http://www-master.ufr-info-p6.jussieu.fr/2009/Ext/naacke/bdr2010/ind...

DerniresModifs | ChercherUnePage | | PagesSemblables | RtroLiens |

AdministrationDePhpWiki

Les Index
TME : Les Index

L'objectif de ce TP est de mettre en vidence l'utilisation des index pour amliorer le temps de
rponse des requtes. Lire l'annexe PlanRequete

Installer les fichiers du TME

Ouvrir une fentre de terminal (xterm), pour excuter les commandes suivantes :

commande description
cd aller dans votre rpertoire $HOME
tar zxvf $BD_TOOL/tp-index.tgz installer l'archive dans votre rpertoire principal
cd tp-index aller dans votre rpertoire de travail
emacs annuaire.sql & diter le fichier
SQL> @annuaire excute le script annuaire.sql depuis l'invite SQL

Exercice

1) (prparation) Crer une relation Annuaire(nom, prenom, age, cp, tel) en utilisant le fichier
annuaire.sql.
SQL> @annuaire

Elle contient 10000 tuples sur 90 prnoms et 100 noms diffrents. Les codes postaux (cp) sont des
multiples de 100 et sont compris entre 1000 et 100000. Le numro de tlphone est une chane de
10 chiffres commencant par 0. Le numro de tlphone est une cl de l'annuaire.

Rpondre aux questions suivantes dans le fichier tme2.sql

2) Proposer une ou plusieurs requtes pour vrifier si la distribution de l'attribut age est uniforme,
quasi uniforme ou fortement biaise. Expliquer brivement comment analyser le rsultat de cette
requte.

3) Proposer une ou plusieurs requtes pour vrifier que les attributs age et prnom sont
indpendants :

Quel que soit l'age, y a-t-il autant de prnom diffrents pour un age donn ?
Quel que soit le prnom, y-a-t-il autant d'ge diffrents pour un prnom donn ?
Y-a-t-il tous les prnoms possibles pour chaque age, et tous les ages possibles pour chaque
prnom ?

4) En utilisant les formules du cours, estimer la cardinalit des requtes suivantes. Comparer
ensuite la cardinalit estime avec la cardinalit relle des requtes. Quel est le pourcentage
d'erreur (rel/thorique) ?

R1: select * from Annuaire

18
1 sur 3 08/02/2010 15:57
bdr2010 - Les Index http://www-master.ufr-info-p6.jussieu.fr/2009/Ext/naacke/bdr2010/ind...

R2: select * from Annuaire where age < 65;

R3: select * from Annuaire where cp between 75000 and 78000;
R4: select * from Annuaire where age=18 and cp < 1200;
R5: select * from Annuaire where age=18 and cp = 1200;
R6: select count(*) from Annuaire where age < 65;

5) Crer les index mono-attributs permettant d'amliorer les performances des requtes
prcedentes. La syntaxe est :

create index nom_index on nom_relation(nom_attribut) ;

pour effacer un index : drop index nom_index;

5.1) Afin de visualiser les informations dtailles sur le traitement des requtes :

activer le mode de visualisation des plans :

set autotrace trace explain stat (pour dsactiver ce mode : set autotrace off)
Dans cette question, on demande l'optimiseur de requtes d'utiliser des rgles heuristiques
et non pas une estimation du cot. La session est modifies en consquence :
alter session set optimizer_mode = RULE;
puis re-excuter chaque requte pour voir le plan d'excution. Voir l'annexe PlanRequete.

Pour chaque requte, afficher les index utiliss par le moteur de requte.

Prsenter vos rponses dans un tableau rcapitulatif :

requte index utilis(s)

R1 ...
... ...

Analyser le tableau et suggrer des rgles heuristiques que l'optimiseur utilise pour choisir un index.
Si ncessaire, complter le tableau avec d'autres requtes.

5.2) Quelles sont les requtes pour lesquelles le plan d'excution construit par l'optimiseur ne vous
semble pas optimal ? Donner un exemple.

6) Estimation du cot des accs. L'ordre ANALYZE ajoute des statistique dans le dictionnaire du
SGBD pour permettre l'optimiseur d'estimer le cot des requtes :

analyze table Annuaire compute statistics; (pour supprimer les statistiques: analyze table
Annuaire delete statistics;)

6.1) En interrogant les vues user_tables, user_tab_columns et user_indexes, expliquer quelles

sont les statistiques maintenues dans le dictionnaire. Dsactiver le mode autotrace avant
d'interroger les vues (set autrotrace off).

parmi les attributs de ces vues, quels sont ceux qui reprsentent les statistiques prsentes
en cours ?
cardinalit d'une relation
description du domaine d'un attribut avec ses valeurs min et max et son nombre de
valeurs distinctes,
etc...

6.2) Afin que le SGBD maintienne des statistiques sur vos index, excutez la commande suivante
pour chaque index :

19
2 sur 3 08/02/2010 15:57
bdr2010 - Les Index http://www-master.ufr-info-p6.jussieu.fr/2009/Ext/naacke/bdr2010/ind...

analyze index nom_index compute statistics;

On demande maintenant l'optimiseur de requtes d'utiliser un modle de cot (et non pas
des rgles heuristiques) pour choisir un plan d'excution efficace. La session est modifies en
consquence :
alter session set optimizer_mode = CHOOSE;

Pour chaque requte, afficher les index utiliss par le moteur de requtes. Expliquer pourquoi les
index utiliss ne sont plus les mmes qu'en 5.1)

6.2.1) En dduire la mthode que l'optimiseur utilise pour choisir un index.

6.2.2) Est ce l'optimiseur estime avec prcision la cardinalit des requtes ? Si non, expliquer
pourquoi.

6.3) On veut dterminer partir de quel facteur de slectivit, l'optimiseur choisit la lecture
squentielle plutt que l'accs par index. On appelle S le seuil du facteur de slectivit au del
duquel la lecture squentielle est utilise de prfrence l'index. Combien vaut S ?

Mthode suggre pour dterminer S :

Faire varier la slectivit du prdicat cp < v en faisant varier v, puis dterminer par
dichotomie la valeur v partir de laquelle l'index n'est plus utilis.
Mesurer S pour une requte contenant 2 prdicats de slection. S est-il toujours le mme?
pourquoi ?

6.4) Quelles sont les requtes pour lesquelles le plan d'excution construit par l'optimiseur n'est pas
optimal, donner un exemple.

7) (facultatif) Proposer une mthode pour mesurer la dgradation de performance due l'index lors
de l'insertion de donnes dans l'annuaire.

retour vers: LesTravauxDirigs, LesCours, l'Accueil.

Dernire modification le mars 6, 2009 12:02 .

diter | Dverrouiller la Page | Supprimer la Page | Vous tes connect en tant que
Historique | Diff | InfosDeLaPage | DebugInfo bdr | Dconnexion

20
3 sur 3 08/02/2010 15:57
bdr2010 - Les Jointures http://www-master.ufr-info-p6.jussieu.fr/2009/Ext/naacke/bdr2010/ind...

DerniresModifs | ChercherUnePage | | PagesSemblables | RtroLiens |

AdministrationDePhpWiki

Les Jointures
TME Optimisation des requtes avec jointures

L'objectif de ce TME est de trouver le plan d'excution optimal pour traiter une requte contenant
des jointures. Ce TME met en pratique les notions du cours sur l'optimisation de requtes: plan
d'excution, espace de recherche, ordonnancement des jointures, modle de cot, algorithme de
jointure.

Sujet

voir l'exercice 3 du poly de TD (base Joueur, Club, Finance)

Documentation

Signification des valeurs mesures pendant l'excution d'une requte : Annexe1

Directives d'optimisation: Annexe2
Documentation annexe: L'optimiseur de requtes d'Oracle.

Installation des fichiers du TME

tar zxvf $BD_TOOL/tp-jointure.tgz

cd tp-jointure
emacs tme3.sql &

Prparation

construire la BD de l'exercice 3
@base3
ajouter les index
@index3
activer la visualisation des plans d'excution
set autotrace trace explain stat

Introduction

Dans la suite du TME, nous mesurons le cot d'une requte en nombre de lectures de blocs
mmoire. C'est le nombre de consistent gets qui apparait dans les statistiques d'excution d'une
requte.

Un plan d'excution est affich de manire arborescente. Chaque noeud de l'arbre est un
oprateur. Un oprateur a un numro de noeud et un nom. Les noms des oprateurs sont indents
pour montrer l'arbre d'excution : le noeud parent est celui dont le nom est dcal d'un caractre
gauche.

l'exemple suivant reprsente une jointure par boucles imbriques (nested loops). L'oprateur
de jointure (noeud numro 1) est le fils du noeud numro O. Les fils du noeud numro 1 sont

21
1 sur 4 08/02/2010 15:58
bdr2010 - Les Jointures http://www-master.ufr-info-p6.jussieu.fr/2009/Ext/naacke/bdr2010/ind...

des lectures squentielles.

0 SELECT
1 NESTED LOOPS
2 TABLE ACCESS
3 TABLE ACCESS

Autre exemple : l' accs la relation C (les clubs) par un index sur l'attribut division :
3 TABLE ACCESS (BY INDEX ROWID) OF 'C'
4 INDEX (RANGE SCAN) OF 'I_C_DIVISION' (NON-UNIQUE)

Les oprateurs implments par le moteur de requtes sont :

nom algorithme
nested loops jointure par boucles imbriques
merge join jointure par fusion
hash join jointure avec hachage temporaire des tuples
sort (join) tri prliminaire avant jointure par fusion
table access full lecture squentielle d'une relation
table access by rowid lecture non squentielle d'une relation (un accs par tuple)
index (range scan) traverse d'un index (arbre B+)

IMPORTANT

Dans la suite de ce TME on demande au SGBD ne raliser des jointures par boucles imbriques
(Nested Loop Join : voir diapo 13 du cours).
Ajouter la directive USE_NL(x,y,z,...) dans toutes les requtes (x,y,z sont les tables qu'on veut
joindre par boucles imbriques, ici x=C, y=J, z=F)

Question 1 : Plan d'excution choisi par l'optimiseur

1.1) Reprsenter l'arbre du plan d'excution P choisi par dfaut par l'optimiseur pour traiter la
requte R?

1.2) Mesurer le cot du plan P (cf. Annexe1).

Question 2: Ordonancement des jointures

L'objectif est de trouver l'ordre optimal pour traiter les jointures.

2.1) Quelles relations de la BD peuvent tre jointes avec J par equi-jointure ? Mme question pour C
et F. Combien y a-t-il d'ordres de jointure diffrents pour traiter la requte R ?

2.2) En utilisant la directive /*+ use_nl(c,j,f) ordered */, construire un plan d'excution pour chaque
ordre de jointure. L'ordre est celui de la clause FROM (pas celui de la clause where !). Pour chaque
plan, prciser clairement les index et les algorithmes de jointure utiliss, et mesurer le cot du plan.
Lire l'Annexe2 expliquant la directive ordered.

2.3) Quels sont les 3 plans de plus faible cot ? Comparer leur cot avec celui du plan P choisi par
l'optimiseur ?

Question 3 : Cot des slections

22
2 sur 4 08/02/2010 15:58
bdr2010 - Les Jointures http://www-master.ufr-info-p6.jussieu.fr/2009/Ext/naacke/bdr2010/ind...

On veut connaitre le cot des slections utilises dans la requte R. Pour cela, on mesure le cot
des slections avec et sans index.

3.1) Quels sont les noms des index existants pour les relations J, C et F ?

3.2) Pour chaque requte de slection pouvant servir traiter la requte R, donner son expression
SQL et son cot avec et sans index. Pour empcher un accs par index, utiliser la directive /*+
no_index(nom_relation nom_index) */ (voir l'Annexe2). Donner aussi la cardinalit (nb de n-uplets)
du rsultat de la slection.

cot avec cot sans

num slection SQL cardinal.
index index
sel(J, salaire > 59000 et sport =
S1
'sport1')
S2 sel(C, division=1)

3.3) Pour les slections S1, S2 l'accs par index est-il avantageux ? Expliquer pourquoi.

3.4) On veut traiter R en commenant par la slection la moins couteuse (entre S1 et S2). Donner le
plan de la requte R, qui utilise la slection la moins couteuse? Est-ce le plan optimal ?

Question 4

(question supprime)

Question 5 : Optimisation base sur un modle de cot

5.1) Ajouter dans le dictionnaire du SGBD les statistiques sur J, C et F et les index associs.

lire et comprendre le fichier stat3.sql, puis l'excuter:

@stat3

5.2) Lorsque le dictionnaire du SGBD contient des statistiques sur les donnes lues par la requte,
l'optimiseur d'Oracle peut estimer plus prcisment le cot des oprations d'un plan d'excution.
Cela lui permet de dterminer si un accs par index est intressant ou non. Modifier le prdicat de
slection du salaire dans la requte R (incrmenter de 100 en 100, la valeur 59000) jusqu' ce que
le SGBD utilise l'index. On note R' la requte obtenue.

5.3) Supprimer les statistiques, la commande est :

analyze table J delete statistics;

(idem pour les 2 autres tables)

Le plan choisi par l'optimiseur pour traiter R est-il le mme avec et sans statistiques sur J,C,F ?
Expliquer pourquoi.
Dcrmenter de 1000 en 1000, la valeur 59000, jusqu' ce que le SGBD n'utilise plus l'index. On
note R'' la requte obtenue.

5.4) Lorsque l'optimiseur ne dispose pas de statistiques, il tente tente d'estimer la taille des rsultats
intermdiaires partir d'un chantillon de donnes (technique appelle dynamic sampling).
Dsactiver le dynamic sampling en ajoutant la directive suivante.
select /*+ use_nl(c,j,f) dynamic_sampling(0) */ c.nom, f.budget

23
3 sur 4 08/02/2010 15:58
bdr2010 - Les Jointures http://www-master.ufr-info-p6.jussieu.fr/2009/Ext/naacke/bdr2010/ind...

a) Expliquer pourquoi le plan gnr par l'optimiseur est diffrent.

b) Dessiner l'arbre du plan d'excution. Comprendre les oprations effectues. L'optimiseur choisit
de calculer une intersection entre deux ensembles. Quels sont ces 2 ensembles ?

Question 6 (facultatif)

6.1) Laisser le SGBD choisir les algorithmes de jointure, en supprimant la directive use_nl. Pourquoi
la jointure par hachage est-elle choisie ?

6.2) Insrer des nuplets dans la table C jusqu' ce que l'algorithme de jointure choisi change (pour
un autre algorithme que le hash join)

Question 7 (facultatif)

7.1) Ajouter une 4eme table et proposer une requte de jointure entre les 4 tables qui ne puisse pas
tre transforme en un arbre linaire gauche.

7.2) Ajouter suffisamment de donnes dans les tables pour qu'au moins une jointure par tri-fusion
(sur les 3 jointures effectuer) soit choisie par l'optimiseur.

Question 8 (facultatif)

Donner un exemple de requte R' de jointure entre C,J,F o le plan choisi par le SGBD consiste
commencer par une lecture squentielle de F en entier. Cependant, ce plan serait efficace car la
slectivit de premire jointure entre F et une autre relation serait trs forte. R' est diffrent de R,
vous pouvez changer les prdicats de la clause where.

retour LesTravauxDirigs, Accueil,

Dernire modification le mars 17, 2009 6:09 .

diter | Dverrouiller la Page | Supprimer la Page | Vous tes connect en tant que
Historique | Diff | InfosDeLaPage | DebugInfo bdr | Dconnexion

24
4 sur 4 08/02/2010 15:58
bdr2010 - Annexe 1 http://www-master.ufr-info-p6.jussieu.fr/2009/Ext/naacke/bdr2010/ind...

DerniresModifs | ChercherUnePage | | PagesSemblables | RtroLiens |

AdministrationDePhpWiki

Annexe 1
Annexe1: Valeurs mesures pendant l'excution d'une requte

Parmi les valeurs mesures, nous considrons uniquement le nombre de consistent gets, sans
prendre en compte les autres valeurs, car consistent gets est reprsentatif du nombre de pages
disque lues (dans un tat froid de la base, lorsque toute les donnes sont sur disque et que le
cache est vide).

Les principales valeurs mesures sont:

Statistics
.-----------------------------------------------------------------------------

db block gets : Number of times a CURRENT block was requested

consistent gets : Number of times a consistent read was requested for a block.

physical reads : Total number of data blocks read from disk. This number equals the value of
"physical reads direct" plus all reads into buffer cache.

redo size : Total amount of redo generated in bytes.

sorts (disk) : Number of sort operations that required at least one disk write. Sorts that require
I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter
SORT_AREA_SIZE. For more information, see "SORT_AREA_SIZE".

sorts (memory) : Number of sort operations that were performed completely in memory and did
not require any disk writes. You cannot do much better than memory sorts, except maybe no
sorts at all. Sorting is usually caused by selection criteria specifications within table join SQL
operations.

rows processed: result cardinality

retour au TME LesJointures

Dernire modification le fvrier 26, 2004 9:34 .

diter | Dverrouiller la Page | Supprimer la Page | Vous tes connect en tant que
Historique | Diff | InfosDeLaPage | DebugInfo bdr | Dconnexion

25
1 sur 1 08/02/2010 15:59
bdr2010 - Annexe 2 http://www-master.ufr-info-p6.jussieu.fr/2009/Ext/naacke/bdr2010/ind...

DerniresModifs | ChercherUnePage | | PagesSemblables | RtroLiens |

AdministrationDePhpWiki

Annexe 2

Annexe2: Directives d'optimisation

Les directives d'optimisation guident l'optimiseur de requtes pour le choix d'un plan d'excution.

Syntaxe: les directives sont insres dans la requtes, aprs le mot SELECT, entre commentaires
spciaux :

/*+ nom_directive */

Utiliser les directives d'optimisation (hint) suivantes :

/*+ ordered */ : fixe l'ordre de traitement des jointures : l'ordre impos est celui indiqu dans la
clause FROM de la requte.
/*+ index(nom_relation nom_index) */ : pour forcer un accs par index
/*+ no_index(nom_relation nom_index)*/ : pour interdire l'utilisation d'un index

Ne pas confondre le nom de l'index et le nom de l'attribut index. Par exemple, I_J_cnum est le
nom de l'index sur l'attribut cnum de J.

Exemple de directive pour ordonner les jointures

Le plan P1 avec l'ordre (J |><| C) |><| F

select /*+ ordered */ c.nom, f.budget

from J, C, F
where J.cnum = C.cnum and C.cnum = F.cnum
and c.division=1 and J.salaire > 59000
and j.sport = 'sport1';

retour vers LesJointures

Dernire modification le fvrier 26, 2004 9:14 .

diter | Dverrouiller la Page | Supprimer la Page | Vous tes connect en tant que
Historique | Diff | InfosDeLaPage | DebugInfo bdr | Dconnexion

26
1 sur 1 08/02/2010 15:59
bdr2010 - Jointure Rpartie http://www-master.ufr-info-p6.jussieu.fr/2009/Ext/naacke/bdr2010/ind...

DerniresModifs | ChercherUnePage | | PagesSemblables | RtroLiens |

AdministrationDePhpWiki

Jointure Rpartie

TME Jointure Rpartie

L'objectif de ce TME est de comprendre l'valuation d'un requte de jointure entre 2 relations qui
sont situes sur 2 sites distincts.

dfinir le schma global qui offre un accs transparent des donnes de plusieurs bases,
formuler une requte rpartie,
comprendre l'ordre et l'emplacement des oprations permettant d'valuer une requte rpartie
(quel site traite quelles oprations?).

Scnario

On dispose de 2 SGBD : site1 et site 2

Donnes: Le site 1 contient les clubs (table C), le site 2 contient les Joueurs (table J)
La couche BDR est implmente sur le site 1.

Installation

crer les tables J,C,F (dj fait lors du TME prcdent)

@base3

supprimer les Joueurs du site 1 (les joueurs sont maintenant sur le site 2)
drop table J;

Relier les sites :

La couche BDR (site1) doit pouvoir se connecter au site 2
create database link ora2 connect to bdwa2 identified by "bdwa2" using 'ora2';

Vrifier le bon fonctionnement du lien

desc J@ora2

ajouter un club dans une nouvelle ville. Ce club n'a que 10 joueurs ce qui permettra, par la suite, de
poser une requte de jointure trs slective.
insert into C values( 6000, 2, 'petit club', 'Combourg');

Construire le schma global

create view J as
select * from j@ora2;

Requtes rparties

Pour chaque requte, rpondre aux questions

27
1 sur 2 08/02/2010 15:59
bdr2010 - Jointure Rpartie http://www-master.ufr-info-p6.jussieu.fr/2009/Ext/naacke/bdr2010/ind...

O est traite chaque opration (slection, projection, jointure, ...) ?

Quelles sont les donnes transfres entre les sites pendant l'valuation de la requte ?

Activer le mode de visualisation des plans et le chronomtrage

set timing on
set autotrace trace explain stat

R1 : Jointure seule avec un transfert volumineux

Afficher les joueurs avec leur club

select *
from J, C
where j.cnum = c.cnum;

R2 : jointure avec slection

select *
from J, C
where j.cnum = c.cnum
and salaire > 59000

La slection est-elle pousse sur le site 2 ?

R3 Jointure trs slective

R3a : jointure trs slective et avec un transfert volumineux

select *
from J, C
where j.cnum = c.cnum
and ville = 'Combourg';

R3b : jointure trs slective et avec un transfert faible

select /*+ driving_site(j1) */ *
from J j1, c c1
where j1.cnum = c1.cnum
and ville = 'Combourg';

Proposer d'autres requte pour illustrer les optimisations de requtes rparties vues en cours.

LesTravauxDirigs, Accueil

Dernire modification le mars 13, 2009 12:23 .

diter | Dverrouiller la Page | Supprimer la Page | Vous tes connect en tant que
Historique | Diff | InfosDeLaPage | DebugInfo bdr | Dconnexion

28
2 sur 2 08/02/2010 15:59
Universit Pierre et Marie Curie - Paris 6
Master d'informatique - BDR

TME - Architecture Web

JDBC
Introduction:
L'objectif de ce TD/TME est de raliser une application de type web qui accde une base de donnes. Cette
application repose sur l'architecture client-serveur 3 couches (cf. cours sur l'architecture C/S 3-tiers) : le serveur
de donnes, le serveur web et le navigateur client. Nous construisons la passerelle d'accs au serveur de
donnes avec JDBC.
Le serveur de donnes (Oracle sur la machine oracle), est un SGDB relationnel supportant l'interface JDBC. Il
contient la base de donnes tennis dont le schma est:
JOUEUR2 (NUJOUEUR, NOM, PRENOM, ANNAISS, NATIONALITE)
GAIN2 (NUJOUEUR, LIEUTOURNOI, ANNEE, PRIME, SPONSOR)
RENCONTRE2 (NUGAGNANT, NUPERDANT, LIEUTOURNOI, ANNEE, SCORE)
Les attributs NuGagnant, NuPerdant et NuJoueur sont dfinis sur le mme domaine. Les cls des relations sont
soulignes.

Etape 1 : Accs une base de donnes avec JDBC

Le package JDBC permet d'accder au SGBD depuis une application crite en langage java. Afficher la
documentation en ligne du TME (lien TME JDBC depuis la page daccueil), puis afficher la documentation java (lien
bibliothque JDBC).
Utilisation des bibliothques Java
Les bibliothques java sont regroupes en packages. Un package contient plusieurs interfaces et plusieurs classes.
Une classe (et une interface) contient des mthodes et des variables. La documentation en ligne permet de
naviguer dans les bibliothques pour dterminer les classes, interfaces et mthodes utiliser pour construire une
application java. Les classes et interfaces JDBC du package java.sql permettent daccder une base de
donnes. Naviguer dans la documentation du package java.sql pour rpondre aux questions suivantes :
a) Donner le nom et le type des paramtres des mthodes getConnection de la classe DriverManager.
b) A quoi sert la mthode next de l'interface ResultSet ?
c) A quoi sert la mthode setMaxRows de linterface Statement ?
d) Quelles sont les sous-interfaces de linterface Statement ?

1 Premire connexion une base de donnes

Installer lenvironnement logiciel (la procdure dinstallation est dtaille sur la fiche technique, ci aprs) puis
excuter le programme Joueur.
1.1) Etudier le programme Joueur.java. (voir en annexe). Commenter brivement les lignes importantes pour
expliquer chaque tape du programme.
1.2) Sur une feuille ( faire chez soi), reprsenter sous la forme dun graphe, les scnarios d'accs une base de
donne en utilisant les interfaces, classes et les mthodes de JDBC. Le graphe est dfini comme suit :
- un nud (rectangle) reprsente une interface ou une classe.
- un arc orient (flche) reprsente une mthode. Larc est tel que :
- son origine est l'interface dans laquelle la mthode est dfinie,
- sa destination est l'interface du rsultat de la mthode.
Le graphe doit contenir les classes, interfaces et mthodes du package java.sql qui sont utilises dans Joueur.java,
ainsi que les lments suivants : PreparedStatement, ResultSetMedatata, DatabaseMetadata, executeUpdate, int,
String et getMetaData.

2 Accs paramtr la base de donnes

29
Universit Pierre et Marie Curie - Paris 6
Master d'informatique - BDR

2.1) Excuter le programme MaxPrime (saisir une anne, par ex. 1992). Editer le fichier MaxPrime.java. Complter
len-tte avec vos nom et prnom. Complter tous les commentaires pour expliquer ce que fait le programme.
2.2) Copier et modifier le programme prcdent pour obtenir le programme MaxPrime2.java qui excute la mme
requte en boucle, en demandant chaque itration une nouvelle valeur lutilisateur. Le programme MaxPrime2
se termine lorsque la saisie de lutilisateur est vide. Pour amliorer les performances du programme, deux
conditions sont requises :
- la connexion vers le SGBD est ouverte une seule fois pendant toute la dure du programme (i.e., rutiliser le
mme objet de type Connection chaque itration).
- le SGBD analyse la requte une seule fois pendant toute la dure du programme (i.e., utiliser linterface
PreparedStatement pour prparer une requte paramtre ; utiliser la mthode setInt pour affecter une valeur un
paramtre de la requte).
Remarque : ne pas oublier dappeler le constructeur de MaxPrime2. (ie., remplacer new MaxPrime() par new
MaxPrime2().

3 Requte gnrique
3.1) Quel est le rle de linterface ResultSetMetaData et de sa mthode getColumnCount ?
3.2) Complter le programme Generique.java pour traiter une requte SQL quelconque passe en paramtre, et
afficher les valeurs des tuples du rsultat sous la forme val1 val2 valn (un tuple par ligne).
Tester lexcution avec la requte donnant tous les Joueurs ns en 1972 :
java Generique select * from Joueur2 where annaiss = 1972
3.3) Complter le programme pour afficher en en-tte le nom des attributs du rsultat.
Exemple dexcution (ne pas chercher tabuler le rsultat)
NUJOUEUR NOM PRENOM ANNAISS NATIONALITE
-----------------------------------------------------------
1 MARTINEZ Conchita 1972 Espagne
14 SAMPRAS Pete 1972 Etats-Unis
3.4) Ecrire le programme GeneriqueXHTML.java qui affiche le rsultat dune requte quelconque dans un tableau
format en XHTML. Tester lexcution en stockant le rsultat dans un fichier :
java GeneriqueXHTML select * from Joueur2 > resultat.html
Exemple de rsultat :
<?xml version="1.0" encoding="ISO-8859-1" ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-
transitional.dtd">
<html>
<head ><title>Rsultat</title></head>
<body>
<h3>La requete est : </h3> select * from Joueur2
<h3>le resultat est : </h3>
<table border="2">
<tr><th>NUJOUEUR</th><th>NOM</th><th>PRENOM</th><th>ANNAISS</th><th>NATIONALITE</th></tr>
<tr><td>1</td><td>MARTINEZ</td><td>Conchita</td><td>1972</td><td>Espagne</td></tr>
<tr><td>2</td><td>NAVRATILOVA</td><td>Martina</td><td>1957</td><td>Etats-Unis</td></tr>

<tr><td>14</td><td>SAMPRAS</td><td>Pete</td><td>1972</td><td>Etats-Unis</td></tr>
</table>
</body>
</html>
Visualiser graphiquement le rsultat dans un navigateur avec la commande :
netscape resultat.html (ou mozilla resultat.html)

4 Schma dune relation

4.1) Quel est le rle de linterface DatabaseMetaData et comment obtenir une instance de ce type partir dune
connexion ? Expliquer la mthode getColumns de linterface DatabaseMetaData. Dans un SGBD les relations sont
gnralement organises en hirarchie 2 niveaux (catalogue et schma). Ainsi, une relation appartient un
schma lui-mme appartenant un catalogue. Dans Oracle, l'organisation a un seul niveau : une relation appartient
un schma gal au nom de celui qui a cr la relation, il n'y a pas de niveau catalogue.
4.2) Crer le programme Schema.java qui affiche le nom et le type des attributs dune relation passe en
paramtre.
Exemple dexcution

30
Universit Pierre et Marie Curie - Paris 6
Master d'informatique - BDR

java Schema JOUEUR2 // crire le nom de la relation en MAJUSCULE

Le schma de JOUEUR2 est : // rsultat affich
NOM TYPE
-------------------------------
NUJOUEUR NUMBER
NOM VARCHAR2
PRENOM VARCHAR2
ANNAISS NUMBER
NATIONALITE VARCHAR2

5 Jointure inter-bases
Soit une deuxime base de donnes (situe sur un autre SGBD diffrent du premier), contenant la relation
SPONSOR (NOM, NATIONALITE) // nationalit des sponsors
La deuxime base de donnes est accessible seulement en lecture, par une connexion la base oracle en tant
quutilisateur anonyme avec le mot de passe anonyme.La relation Sponsor contient 100 000 tuples. Soit la
requte R1 :Donner le nom et la nationalit des joueurs avec le nom et la nationalit de leurs sponsors, dans
lordre des noms de joueur.
5.1) Quelle est la dure approximative dune lecture squentielle de la relation Sponsor ? Rpondre en crivant le
programme Generique2.java (obtenu en modifiant Generique.java), puis en utilisant la commande time :
time java Generique2 requete // mesure le temps de rponse de la requte
Veiller omettre l'affichage du rsultat dans le terminal pour viter de mesurer le temps d'affichage au lieu du
temps de lecture des donnes.
5.2) Ecrire R1 en SQL.
5.3) Pourquoi ne peut-on pas excuter cette requte R1 avec une seule connexion au SGBD ?
5.4) On veut obtenir la cardinalit du rsultat de R1 ? Donner en SQL une requte R2 telle :
R2 est une sous-expression de R1, et R2 peut tre excute entirement sur le premier SGBD
la cardinalit de R2 est gale celle de R1, i.e., card(R2) = card(R1)
Excuter R2 et donner la valeur de card(R1).
5.5) Complter le programme Sponsor.java pour traiter R1. Pendant tout le traitement de la requte R1, combien
dinstances de type Connection et Statement sont cres ? Combien de sous-requtes sont excutes ? Combien
de fois la relation Sponsor est-elle lue ? Quels sont les tuples transfrs depuis le SGBD vers lapplication java ?
Exemple dexcution :
java Sponsor //commande
CONNORS, Etats-Unis, Dunlop, Ecosse
CONNORS, Etats-Unis, Lacoste, France
EDBERG, Suede, Dunlop, Ecosse

SAMPRAS, Etats-Unis, Reebok, Angleterre
WILANDER, Suede, Dunlop, Ecosse
WILANDER, Suede, Kennex, USA
5.6) Mesurer le temps moyen dexcution du programme avec la commande time :
time java Sponsor //commande

real 0m3.957s user 0m1.530s // affiche le temps de rponse
5.7) Ecrire le programme SponsorTF.java pour traiter la requte R2 : Donner le nom et la nationalit des joueurs
avec le nom et la nationalit de leurs sponsors, dans lordre des noms de sponsor en utilisant lalgorithme de
jointure par tri fusion. Combien dinstances de type Connection et Statement sont cres pendant le traitement de
la requte ?
5.8) Comparer les temps de rponse de R1 et R2. Mesurer (en pourcentage) lcart entre le temps de rponse de
Sponsor et SponsorTF. Interprter le rsultat. Proposer une solution Sponsor2.java pour amliorer le temps de
rponse de la requte R2.
5.9) Dtailler une solution pour traiter R1 en transfrant les cls (voir cours : semi-jointure)
5.10) (facultatif) Proposer une implmentation pour d'autres algorithmes de jointure (grace join, hybrid hash join,
...).

31
Universit Pierre et Marie Curie - Paris 6
Master d'informatique - BDR

Fiche Technique pour les TME

Installer lenvironnement logiciel (Java, JDBC)
1.1) Rpertoire de travail.
Pour faciliter l'utilisation des divers outils, le nom des rpertoires et de vos fichiers de travail doivent tre identiques
pour tous les binmes. Pour crer l'arborescence de travail, sauvegarder l'archive jdbc.tar dans votre rpertoire
racine et extraire son contenu.
cd // aller dans le rpertoire $HOME
tar zxvf $BD_TOOL/jdbc-etu.tgz // installer larchive
cd jdbc // aller dans le rpertoire de travail
Dans la suite du TME, sauvegarder tous vos programmes dans le rpertoire ~/jdbc.
1.2) Editeur de texte:
Editer les fichiers avec emacs. Activer les options suivantes :
Programme en couleur : Menu Option > Syntax Highlighting
Reprage de la structure du programme : Menu Option > Paren Match Highlighting
1.3) Environnement java
Pour tester la compilation du code source en bytecode, compiler le programme de test Bonjour.java (cela cre le
fichier Bonjour.class)
javac Bonjour.java
Pour tester la machine virtuelle lancer l'excution du fichier Bonjour.class :
java Bonjour
Fichier Joueur.java
1 import java.sql.*;
2 import java.io.*;
3
4 public class Joueur {
5 String server = "frelon";
6 String port = "1521";
7 String database = "oracle";
8 String user = "p6lip000";
9 String password = "p6lip000";
10 String requete = "select nom, prenom from Joueur";
11 Connection connexion = null;
12
13 /** La mthode traiteRequete */
14 public void traiteRequete() {
15 try {
16 String url = "jdbc:oracle:thin:@" +server + ":" +port + ":" +database;
17 connexion = DriverManager.getConnection(url, user, password);
18 Statement lecture = connexion.createStatement();
19 ResultSet resultat = lecture.executeQuery(requete);
20 while (resultat.next()) {
21 String tuple = resultat.getString(1) + " " + resultat.getString(2);
22 out.println(tuple);
23 }
24 resultat.close();
25 lecture.close();
26 connexion.close();
27 }
28 catch(Exception e){ }
29 } }

32
bdr2010 - JDBC http://www-master.ufr-info-p6.jussieu.fr/2009/Ext/naacke/bdr2010/ind...

DerniresModifs | ChercherUnePage | | PagesSemblables | RtroLiens |

AdministrationDePhpWiki

JDBC

TME JDBC : Accs un SGBD depuis Java

Prparation

lire le sujet (ou voir poly). Seulement la partie JDBC (pas JSP)
lire les API (choisir le package java.sql)
installer les fichiers : tar zxvf $BD_TOOL/jdbc-etu.tgz
ceux qui utilisent Eclipse doivent rfrencer le jar /Infos/bd/client10/ojdbc14.jar dans leur
projet.

Sance 1

installer l'environnement de travail (voir le poly, tape 1 uniquement)

Commencer l'tape 1 du sujet: rpondre aux questions a) d) dans le fichier rapport.txt
question 1.1 : complter les lignes /* commentaires: */

Question frquente concernant la compilation java

Comment viter les warning du compilateur concernant le jeu de caractres UTF-8 ?

afin de pouvoir compiler des programmes java contenant des caractres accentus, le
jeu de caractres par dfaut doit tre europen (ISO-8859-1) :
export LC_CTYPE="fr_FR" ou export LC_ALL="fr_FR"
vrification du jeu avec la commande : locale charmap

Question 2 : MaxPrime2. Dfinir une requte paramtre :

la requte est une chane de caractres contenant un point d'interrogation ? pour chaque
paramtre
Exemple "select * from Joueur2 where annaiss = ? "
voir l'exemple dans la documentation de l'interface PreparedStatement

Questions frquentes concernant MaxPrime2:

Comment comparer 2 chanes de caractres a et b ?

utiliser la mthode : a.equals(b)
l'oprateur a==b ne compare pas le contenu des chanes de caractres mais leur
identifiant d'objet.
Comment convertir une chane de caractres en un nombre entier ?
int n = Integer.parseInt(chaine);

Questions 3 : Requte gnrique

L'excution du programme GeneriqueHTML doit produire ce rsultat

Sance 2

33
1 sur 2 08/02/2010 16:00
bdr2010 - JDBC http://www-master.ufr-info-p6.jussieu.fr/2009/Ext/naacke/bdr2010/ind...

Finir la question 3
Question 4: Schma d'une relation.
Question 5:
Ajuster l'ULR d'accs la 2eme base
String url2 = "jdbc:oracle:thin:@oracle2.ufr-info-p6.jussieu.fr:1521:ora2"; // base
des sponsors
jointure inter-bases
jointure par boucles imbriques

Sance 3

Finir la question 5
jointure par tri puis fusion. Etendre l'algorithme pour traiter le cas d'une qui-jointure
entre 2 attributs non uniques (i.e. 2 cls trangres).
jointure par transfert de cls (semi-jointure).
(facultatif) implmenter d'autres algorithmes de jointure, tout en utilisant JDBC.

Documentation diverse

Algorithmes de jointure (anglais)

Liens externes : un cours HTML (universit de Nice), un autre manuel HTML, un cours
java, Java 1.5 API, ...

retour vers: TmeJSP, LesTravauxDirigs, LesCours, l'Accueil.

Dernire modification le avril 7, 2009 5:01 .

diter | Dverrouiller la Page | Supprimer la Page | Vous tes connect en tant que
Historique | Diff | InfosDeLaPage | DebugInfo bdr | Dconnexion

34
2 sur 2 08/02/2010 16:00
bdr2010 - Tme 2 PC http://www-master.ufr-info-p6.jussieu.fr/2009/Ext/naacke/bdr2010/ind...

DerniresModifs | ChercherUnePage | | PagesSemblables | RtroLiens |

AdministrationDePhpWiki

Tme 2 PC

Transactions rparties : validation en 2 tapes (2 Phases

Commit)
Prparation

Voir le cours: protocole 2PC

Installer les fichiers du tme:
tar zxvf $BD_TOOL/tme-2PC.tgz
cd tme-2PC
source config-simjava
javac Exemple1.java
appletviewer exemple1.html

Dans les exercices suivants recopier les fichiers java et html :

cp Exemple1.java ExempleNN.java
cp exemple1.html exempleNN.html

puis diter les nouveaux fichiers pour remplacer le nom de la classe Exemple1 par ExempleNN

Veiller recompiler votre exemple avant chaque excution car les noms de classes Participant et
Coordinateur existent dans plusieurs exercices.

Documentation

la doc java et simjava (voir les classes Sim_entity et Sim_event du package eduni.simjava)

Exercice 1 : Prise en main du simulateur

Drouler la simulation faible vitesse (speed entre 100 et 150)

Expliquer brivement le fonctionnement du protocole implment dans le fichier Exemple1.java
Comprendre les mthodes utilitaires de la classe Entit : envoyer_message,
attendre_message_timeout, panne, date, ...

Exercice 2 : Tolrance aux pannes

Panne d'un participant

Dans un fichier Exemple2a.java, complter le protocole pour grer la panne d'un participant
avant de s'tre dclar prt. Suivre le diagramme de la diapo 13.
Le coordinateur dcide d'abandonner s'il n'a pas reu tous les votes dans un dlai de
300 units de temps.
Est-ce que le coordinateur attend que les participants en panne aient repris, avant de
rpondre l'application ?
Que se passe-t-il si un participant qui n'est pas en panne mais trop lent, reoit la

35
1 sur 2 08/02/2010 16:00
bdr2010 - Tme 2 PC http://www-master.ufr-info-p6.jussieu.fr/2009/Ext/naacke/bdr2010/ind...

dcision d'abandonner alors qu'il n'a pas encore envoy son vote au coordinateur ?

Dans un fichier Exemple2b.java, complter le protocole pour grer la panne d'un participant
aprs s'tre dclar prt (diapo 14).
Le coordinateur rpte sa dcision un participant qui la lui demande.

Panne du coordinateur (diapo 15)

Dans un fichier Exemple2c.java, complter le protocole pour grer la panne du coordinateur

aprs l'tape 1.

Exercice 3 : Gestion des dlais

Dure maximum de la validation

Dans un fichier Exemple3a.java, modifier le protocole pour rpondre l'application dans un

dlai born . Quelle sont les rponses possibles du coordinateur l'application, aprs un
temps D, lorsque la validation n'est pas termine ?

Rponse anticipe

Dans un fichier Exemple3b.java, modifier le protocole pour signaler, l'application, l'abandon

de la transaction ds qu'un participant a vot pour un abandon. Que fait le coordinateur
lorsqu'il reoit le prochain commit en provenance de l'application alors que les participants
n'ont pas encore fini la validation prcdente.

Exercice 4 : Coordonner plusieurs utilisateurs

Dans un fichier Exemple4.java, modifier le simulateur pour grer plusieurs transactions

provenant simultanment de 2 applications.

Exercice 5: Dcision majoritaire

Dans un fichier Exemple5.java, modifier le protocole pour dcider d'une validation ds que la
majorit des participants a vot pour une validation. Dans ce cas, que rpond le coordinateur
aux participants minoritaires qui ont vot pour l'abandon ?

Documentation diverse

tutorial simjava

LesTravauxDirigs, LesCours, Accueil

Dernire modification le avril 21, 2009 12:20 .

diter | Dverrouiller la Page | Supprimer la Page | Vous tes connect en tant que
Historique | Diff | InfosDeLaPage | DebugInfo bdr | Dconnexion

36
2 sur 2 08/02/2010 16:00