1
Évaluation des Requêtes Survol

• Chapitre 12

2
Objectifs

• Catalogue
• Préliminaires
• Techniques de base pour traiter des requêtes
• Chemin daccès
• Correspondance dindexes
• Sommaire des algorithmes de traitement des
  requêtes
• Sélection
• Projection
• Join
• Introduction à loptimisation des requêtes
• Plans dévaluation
• Plans alternatifs

3
Évaluation des Requêtes Survol

• Évaluation dune requête SQL
• Analysée syntaxiquement, ensuite traduite en une
  forme étendue dalgèbre relationnelle, laquelle
  est enfin transformée en plan dévaluation.
• Plan dévaluation Arbre dopérations de
  lalgèbre relationnelle avec un choix
  dalgorithme pour chaque opération.
• Deux problématiques importantes dans
  loptimisation
• Énumération des plans alternatifs pour une
  requête
• Estimation des coûts de ces plans et choix de
  celui estimé être le moins cher
• Idéalement Trouver le meilleur plan.
  Pratiquement Éviter les pires plans!
• Approche utilisé Système R.

4
Quelques Techniques Communes

• Les algorithmes pour lévaluation des opérateurs
  relationnels utilisent largement quelques idées
  simples
• Indexes Utilisation des conditions des clauses
  WHERE pour puiser de petits ensembles de tuples
  (sélections, joins)
• Itération Scannage de tous les tuples dune
  relation (même sil y a un indexe). Parfois, le
  scannage est fait sur des entrées des données de
  lindexe plutôt que sur la relation elle-même.
• Partition Division dune instance de relation en
  un ensemble de relations plus petites de manière
  à appliquer une opération coûteuse sur ces
  petites relations plutôt que sur la grande
  relation originale.

5
Statistiques et Catalogues

• Lévaluateur a besoin dinfo sur les relations
  ainsi que les indexes impliquées. Les Catalogues
  contiennent
• tuples (NTuples) et pages (NPages) pour
  chaque relation
• valeurs de clés distinctes (NKeys) et pages
  (NPages) pour chaque index
• Hauteur de lIndex (Height), plus petites / plus
  grande valeurs de clé (Low/High) pour chaque
  index à arbre
• Catalogues rafraîchis périodiquement.
• Doù lon vivra avec de légères inconsistances !
• Dautres types dinfos détaillées (p.ex.,
  histogrammes des valeurs dans certains champs)
  sont aussi stockées.
• Histogramme structure donnant une approximation
  de la distribution des valeurs des données

6
Chemins dAccès et Correspondance dIndex

• Un chemin daccès est une méthode pour puiser les
  tuples
• Utilisation du Scannage du fichier, ou dun index
  qui correspond à (match) une sélection
  (WHERE) de la requête.
• Un index à arbre correspond à une conjonction de
  termes qui mentionnent seulement des attributs
  dun préfixe de la clé de recherche.
• P.ex., lindex à arbre sur lta, b, cgt correspond
  à la sélection a5 AND b3 et à a5 AND bgt6,
  mais pas à b3.
• Un index à hachage correspond à une conjonction
  de termes qui a un terme de la forme attribut
  valeur pour chaque attribut de la clé de
  recherche de lindex.
• P.ex., lindex à hachage sur lta, b, cgt
  correspond à a5 AND b3 AND c5 mais pas à
  b3, ni à a5 AND b3, ni à agt5 AND b3 AND c5.

7
Algorithmes dOpérations Relationnelles

• Quelles sont les algorithmes dévaluation des
  principaux opérateurs de lalgèbre relationnelle
  ?
• Supposez une distribution uniforme des valeurs d
  attributs (Cette supposition est naïve, mais
  simplifie la discussion !!)
• Détails au Chapitre 14
• Illustration des coûts estimés
• Exemple
• Sailors(sidinteger, sname string,
  rating integer, age real)
• Reserves(sid integer, bid integer, day
  date, rname string)
• Supposez
• longueur des tuples de Reserves 40 bytes
  tuples/pg 100 pgs 1000
• longueur des tuples de Sailors 50 bytes
  tuples/pg 80 pgs 500

8
Sélection

• Si aucun indexe nexiste, faire un scan. Sinon,
  trouver le chemin daccès le plus sélectif,
  puiser les tuples en lutilisant et appliquer
  tout terme de sélection restant à qui ne
  correspond aucun index choisi
• Chemin daccès le plus sélectif Index ou
  scannage de fichier qui requiert le plus petit
  nombre dentrées et sorties (I/Os) de pages
  possible.
• Facteur de sélection Fraction de tuples qui
  satisfont un terme conjoint.
• Tout terme qui correspond à un index utilisé
  réduit le nombre de tuples puisés tout autre
  terme dans la sélection est utilisé pour
  discriminer certains tuples puisés sans affecter
  le nombre de tuples/pages puisés.
• P.ex. daylt8/9/94 AND bid5 AND sid3. Un index à
  arbre B sur day peut être utilisé ensuite,
  bid5 et sid3 doivent être vérifiés pour chaque
  tuple puisé. De même, un index à hachage sur
  ltbid, sidgt pourrait être utilisé dans ce cas,
  cest daylt8/9/94 qui doit alors être vérifié.

9
Utilisation dun Index pour des Sélections

• Le coût dépend du de tuples qualifiés et du
  groupement de lindex.
• Le coût total est composé du coût pour trouver
  lentrée des données qualifiées (typiquement
  négligeable), plus le coût de puiser les
  enregistrements (peut être élevé dépendant du
  groupement de lindex).
• Dans lexemple ci bas, environ 10 de tuples sont
  qualifiés (100 pages, 10000 tuples). Avec un
  index groupé, le coût peut être de lordre de 100
  I/Os avec un index non groupé, il peut aller
  jusquà 10000 I/Os!

SELECT FROM Reserves R WHERE R.rname lt
C
10
Projection
SELECT DISTINCT R.sid,
R.bid FROM Reserves R

• Simplement effacer les attributs autres que sid
  et bid.
• La partie coûteuse est leffacement des
  duplicatas.
• Les systèmes SQL néliminent pas les duplicatas à
  moins de le spécifier explicitement avec le mot
  clé DISTINCT.
• Élimination par triage Trier Reserves par ltsid,
  bidgt et éliminer les duplicatas. (Optimisation
  possible éliminer des colonnes en passant
  pendant le triage)
• Élimination par hachage Hacher sur ltsid, bidgt
  pour créer des partitions. Charger ces
  partitions en mémoire principale, chacune à la
  fois, construire une structure de hachage en
  mémoire et éliminer les duplicatas.
• Si un index existe sur R.sid et R.bid à la fois,
  le triage peut même se faire sur les entrées
  dindexes !

11
Join Index Nested Loops
foreach tuple r in R do foreach tuple s in S
where ri sj do add ltr, sgt to result

•  Jointure à boucles imbriquées avec index.
• 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 (inner) et
  exploiter lindex.
• Pour chaque tuple de Reserves, le coût de la
  vérification de lindex sur Sailors (index
  probing) 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.

12
Exemples de Index Nested Loops

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

13
Join 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.

14
Exemple de  Sort-Merge Join 

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

15
Aspects Importants de lOptimisateur du Système R

• Impact
• Le plus couramment utilisé marche bien pour lt 10
  joins.
• Estimation des coûts Approximation au mieux.
• Statistiques maintenues dans les catalogues et
  utilisées pour estimer les coûts des opérations
  et la taille des résultats.
• Considère la combinaison des coûts CPU et I/O.
• Espaces des plans Trop large, doit être élagué.
• Considère seulement les left-deep plans.
• Ceux-ci permettent du pipelining des résultats
  dune opération dans une autre sans laide dun
  stockage temporaire.
• Évite le produit Cartésien.

16
Estimation et Facteur de Réduction
SELECT liste dattributs FROM liste de
relations WHERE terme1 AND ... AND termek

• Considérez un bloc de requête
• Maximum tuples du résultat produit des
  cardinalités des relations dans la clause FROM.
• Facteur de réduction (FR) associé avec chaque
  terme. Cardinalité du résultat Max tuples
  produit de tous les FRs.
• Supposition implicite tous les termes sont
  indépendants!
• FR(colvalue) 1/NKeys(I), avec lindex I sur
  col
• FR(col1col2) 1/MAX(NKeys(I1), NKeys(I2))
• FR(colgtvalue) (High(I)-value)/(High(I)-Low(I))

17
Exemple
Arbre de lA.R.
SELECT S.sname FROM Reserves R, Sailors S WHERE
R.sidS.sid AND R.bid100 AND S.ratinggt5

• coût 5005001000 I/Os
• Ceci nest pas le pire des plans!
• On rate beaucoup de choses à faire ici les
  sélections pourraient être faites plutôt, aucun
  indexe nest utilisé, etc.
• But de loptimisation Trouver des plans plus
  efficients qui donnent la même réponse que le
  plan original.

Plan
18
Plan Alternatif 1 (Aucun Index)

• Différence principale sélections le plutôt que
  possible.
• Avec 5 pages tampon, on a le coût suivant
• Scannage de Reserves (1000) écriture de temp
  T1 (10 pages, si on a 100 bateaux et une
  distribution uniforme).
• Scannage de Sailors (500) écriture de temp T2
  (250 pages, si on a 10 niveaux (ratings)).
• Triage de T1 (2210), triage de T2 (23250),
  et enfin merge (10250)
• Total 3560 I/Os de pages.
• Un join BNL donnerait un coût de 104250 coût
  total 2770.
• Avec des projections le plutôt que possible, T1
  ayant seulement sid et T2 seulement sid et
  sname on aurait
• T1 tient en 3 pages le coût de BNL descend en
  dessous de 250 pages total lt 2000.

19
Plan Alternatif 2 (Avec Indexes)
(On-the-fly)
sname
(On-the-fly)
rating gt 5
(Index Nested Loops,
with pipelining )
sidsid
(Use hash
Sailors
bid100
index do
not write
result to

• Avec un index groupé sur bid de Reserves, on a
  100,000/100 1000 tuples sur 1000/100 10
  pages.
• INL avec pipelining (rel. externe
  non-materialisée).

temp)
Reserves
20
Résumé

• Il existe plusieurs algorithmes alternatif
  dévaluation des opérateurs relationnel.
• Une requête est évaluée en la convertissant en un
  arbre dopérateurs et en évaluant les opérateurs
  de larbre en question.
• Deux parties majeures de loptimisation des
  requêtes
• Considère un ensemble de plans alternatifs.
• Doit élaguer lespace de recherche des plans
  typiquement seuls les left-deep plans
  seulement sont considérés.
• Estime les coûts de chacun des plans considérés.
• Doit estimer la taille des résultats et le coût
  pour chaque nœud du plan.
• Problématiques clé Statistiques, indexes,
  implémentation des opérateurs.