Evaluation des Operations Relationnelles

1
Evaluation des Operations Relationnelles

• Chapitre 14, Section 14.4

2
Operations Relationnelles

• Comment implémenter les 5 opérations suivantes
• Sélection ( ) Sélectionne une partie des
  tuples de la relation.
• Projection ( ) Efface des colonnes
  indésirables de la relation.
• Join ( ) Permets la combinaison de deux
  relations.
• Différence ( ) Tuples dune 1ère relation ,
  moins ceux dune 2ème.
• Union ( ) Tuples dune 1ère relation , plus
  ceux dune 2ème.
• Agrégation (SUM, MIN, etc.) et GOUP BY

3
Rappel Exemple
Sailors (sid integer, sname string, rating
integer, age real) Reserves (sid integer, bid
integer, day dates, rname string)

• Reserves
• longueur des tuples de Reserves 40 bytes
• tuples/pg 100
• pgs 1000
• Sailors
• longueur des tuples de Sailors 50 bytes
• tuples/pg 80
• pgs 500

4
Joins à Egalite avec une colonne de Join
SELECT FROM Reserves R1, Sailors S1 WHERE
R1.sidS1.sid

• Expression algébrique R S.
• R S est large R S suivi dune sélection
  sera inefficient.
• Doù le join doit être optimisé avec doigté.
• Supposons quil y a
• M pages dans R, pR tuples par page de R
• N pages dans S, pS tuples par page de S.
• R est Reserves et S est Sailors.
• Des conditions plus complexes seront étudiées
  plutard.
• Coûts exprimés en terme de of I/Os (i.e. de
  pages affectées).

5
Join à Boucles Imbriquées Simples
foreach tuple r in R do foreach tuple s in S
do if ri sj then add ltr, sgt to result

• Pour chaque tuple de la relation externe R, nous
  scannons la relation interne S dans son
  entièreté.
• Coût M pR M N 1000 1001000500
  I/Os.
• Boucles imbriquées par page pour chaque page de
  R, obtenir chaque page de S ensuite sortir les
  paires de tuples correspondants ltr, sgt.
• Coût M MN 1000 1000500
• Si la plus petite relation (S) est externe, le
  coût sera 500 5001000

6
Boucles Imbriquées à Bloc

• Utilise une page comme tampon dentrée pour
  scanner la relation interne S, une page comme
  tampon de sortie et toutes les autres pages
  restantes pour contenir les blocs de la relation
  externe R.
• Pour chaque bloc de B-1 pages de R faire comme
  suit
• Scanner S et pour chaque tuple r dans le bloc de
  R correspondant à un tuple s dans la page en
  mémoire de S, ajouter ltr, sgt au résultat du
  join.

R S
Résultat
Table à hachage pour un bloc de R (k lt B-1 pages)
. . .
. . .
Page de output
Page de input pour S
7
Exemples de Boucles Imbriquées à Bloc

• Coût Scannage de R blocs de R scannage
  de S
• blocs de R
• Avec Reserves (R) comme relation externe et B-2
  100 pages
• Le coût du scannage de R est de 1000 I/Os.
• Nous avons un total de 10 blocs.
• Pour chaque bloc de R, nous scannons S cela
  donne 10500 I/Os.
• Avec Sailors comme relation externe et B-2100
  pages
• Le coût du scannage de S est de 500 I/Os.
• Nous avons un total de 5 blocs.
• Pour chaque bloc de S, nous scannons R cela
  donne 101000 I/Os.

8
Join à Boucles Imbriquées avec Index
foreach tuple r in R do foreach tuple s in S
where ri sj do add ltr, sgt to result

• Sil y a un index sur la colonne de la condition
  de join dune des relations (p.ex. S), faire de
  cette relation la relation interne et exploiter
  lindex.
• Coût M ( (MpR) coût pour trouver le tuple
  correspondant de S)
• Pour chaque tuple de Reserves, le coût de la
  vérification de lindex sur Sailors est denviron
  1.2 pour le hachage et 2-4 pour les arbres B.
  Le coût de trouver le tuple correspondant de
  Sailors dépend ensuite du groupement de lindex.

9
Exemples de Boucles Imbriquées avec Index

• Hachage (Alt. 2) de sid de Sailors (rel.
  interne)
• Scannage de Reserves 1000 I/Os de pages,
  1001000 tuples.
• Pour chaque tuple de Reserves 1.2 I/Os pour
  obtenir lentrée dindex, plus 1 I/O pour obtenir
  le tuple correspondant de Sailors. Total
  220,000 I/Os (100,000 (11.2)).
• Hachage (Alt. 2) de sid de Reserves (rel.
  interne)
• Scannage de Sailors 500 I/Os de pages, 80500
  tuples.
• Pour chaque tuple de Sailors 1.2 I/Os pour
  trouver la page de lindex contenant lentrée des
  données, plus le coût de puiser les tuples
  correspondants de Reserves. Supposez 2.5
  réservations par navigateur (100,000 / 40,000)
  le coût pour les puiser est de 1 ou 2.5 I/Os
  dépendant du fait que lindex est regroupé ou
  pas.

10
Sort-Merge (R S)
ij

•  Jointure à tri-fusion .
• Trier R et S selon leur colonne de join, ensuite
  les scanner afin de faire une fusion (suivant la
  colonne de join.), enfin sortir les tuples du
  résultat.
• Avancer le scannage de R jusquà ce que le tuple
  courant de R gt tuple courant de S, ensuite
  avancer le scannage de S jusquà ce que le tuple
  courant de S gt tuple courrant de R répéter
  cela jusquà ce que le tuple courrant de R
  tuple courrant de S.
• A ce moment, tous les tuples de R avec la même
  valeur que le groupe courant de S correspondent
  sortir tous ces tuples qui correspondent.
• Continuer le scannage de R et S.
• R est scanné une fois chaque groupe de S est
  scanné une fois pour chaque tuple correspondant
  de R.

11
Exemple de Join à Tri-Fusion

• coût M log M N log N (MN)
• Le coût du scannage (MN) pourrait devenir MN
• Avec 35, 100 ou 300 pages tampon, Reserves et
  Sailors peuvent toutes les deux être triées en 2
  passages coût total du join 7500.

12
Amélioration du Join àTri-Fusion

• Nous pouvons combiner la phase de fusion requise
  par le tri de R et S avec la phase de fusion
  requise par le join.
• Produire des runs de taille B pour R et S
  (passage 0).
• Allouer 1 page par run de chaque relation.
• Fusionner les runs de R séparément des runs de S
  fusionner les flux de données de R et S pendant
  quils sont générés (passage 1).
• Appliquer la condition de join en passant.
• Coût lecture écriture de chaque relation au
  passage 0 lecture de chaque relation lors du
  passage 1 écriture des tuples résultats.
• Dans lexemple, le coût descend de 7500 à 4500
  I/Os.
• En pratique, le coût du join à tri-fusion est
  linéaire.

13
Join à Hachage Assomption

• Suppositions faites
• Le de pages tampon est B 1 pour entrée et 1
  pour sortie
• Le de partitions est k
• Le de pages tampon utilisé par les partitions
  est B-2
• Hacher R et S sur les colonnes de join i et j

14
Join à Hachage

• Partition Repartir les deux relations R et S en
  utilisant la même fonction de hachage h les
  tuples de R dans la partition i de R ne peuvent
  être joints que avec ceux de la partition i de S.

• Vérification Lire une partition Ri de R en
  mémoire et ensuite la hacher en utilisant une
  fonction de hachage h2 (différente de h). Scanner
  la partition Si de S pour rechercher les tuples
  correspondants aux tuples de Ri (en utilisant h2
  pour vérifier la table de hachage de Ri).

15
Join à Hachage Algorithme

• // Calculer le join de deux relations R et S sur
  les colonnes Ri et Sj.
• // Repartir R en k partitions
• foreach tuple r de R do
• lire r et lajouter à la page tampon h(ri)
  // Forcer cette page vers le disque au
  // fur et à mesure quelle
  se remplit
• // Repartir S en k partitions
• foreach tuple s of S do
• lire s et lajouter à la page tampon h(sj)
  // Forcer cette page vers le disque au
  // fur et à mesure quelle
  se remplit
• //Phase de vérification (Probing/matching)
  
• for l1, , k do
• //Construire une table de hachage en
  mémoire pour Rl, utilisant h2
• foreach tuple r de Rl do
• lire r et linsérer dans la table de
  hachage de Rl utilisant h2(ri)
• // Scanner les tuples de Sl et vérifier les
  tuples correspondants à r dans Rl
• foreach tuple s de Sl do
• lire s et vérifier la table de hachage de
  Rl en utilisant h2(sj)
• pour tout tuple correspondant r dans Rl, sortir
  ltr,sgt
• réinitialiser la table de hachage pour préparer
  la prochaine partition

16
Observations sur le Join à Hachage

• Le partitions k lt B-1.
• B-2 gt taille de la plus large partition qui
  tienne en mémoire.
• Si la fonction de hachage h ne forme pas des
  partitions uniformes, quelques unes de ces
  partitions peuvent être si grandes quelles ne
  tiennent pas en mémoire.
• Pour résoudre ce problème, lalgorithme de
  hachage est appliqué récursivement afin
  deffectuer le join de ces trop larges partitions
  de R avec les partitions correspondantes de S.

17
Coût du Join à Hachage

• Phase de partition lecture écriture des deux
  relations 2(MN).
• Phase de vérification lecture des deux
  relations MN.
• Dans notre exemple, nous avons un total de 4500
  I/Os.
• Comparaison du tri-fusion et du hachage
• Le hachage peut couter plus si les partitions ne
  sont pas uniformes.
• Le hachage est supérieur si la taille des
  relations diffèrent énormément.
• Dautres facteurs font la différence (ex. le de
  tampons disponible).

18
Conditions plus Générales de Join

• Égalité impliquant plusieurs attributs (p.ex.
  R.sidS.sid AND R.rnameS.sname)
• Boucles imbriquées avec index construire un
  index sur ltsid, snamegt (si S est interne) voire
  utiliser un index existant sur sid ou sname.
• Tri-fusion et hachage trier / partitionner en
  utilisant la combinaison des deux colonnes de
  join.
• Conditions impliquant des inégalités (p.ex.
  R.rname lt S.sname)
• Boucles imbriquées avec index index B groupé
  nécessaire.
• Tri-fusion et hachage non applicables.
• Boucles imbriquées à bloc applicables ici!

19
Résumé

• Il existe plusieurs algorithmes alternatifs
  dévaluation des joins
• Boucles imbriquées (plusieurs variantes)
• Tri-fusion
• Hachage
• Leurs performances varient selon les
  circonstances.
• Tri-fusion et hachage non applicables en cas de
  conditions générales.
• Boucles imbriquées à bloc applicables dans ce
  cas.
• La plupart des joins pratiques étant des joins
  naturels, tri-fusion et hachage seront souvent
  usuels.