SGBD - Unblog

Systèmes de Gestion de
Bases de Données
(SGBD)
Riadh Ben Messaoud
http://eric.univ-lyon2.fr/~rbenmessaoud
Université 7 novembre à Carthage
Faculté des Sciences Economiques et de Gestion de Nabeul
Licence Fondamentale IAG, 2ème année
2008 - 2009
Plan du cours
1.
2.
3.
4.
5.
6.
Introduction aux SGBDs
Architecture générale d'un SGBD
Organisation de données
Evaluation et optimisation des requêtes
Gestion de la concurrence / transactions
Gestion des pannes
FSEGN – Riadh Ben Messaoud
SGBD
2
Plan du cours
1.
2.
3.
4.
5.
6.
Introduction aux SGBDs
Architecture générale d'un SGBD
Organisation de données
Evaluation et optimisation des requêtes
Gestion de la concurrence / transactions
Gestion des pannes
FSEGN – Riadh Ben Messaoud
SGBD
3
Base de données ?

Une collection de données cohérentes entre elles,
généralement de taille importante.

Modélise une entreprise du monde réel
Entités (ex., étudiants, Briques)
 Associations (ex., Paul est inscrit en BD)


Un Système de Gestion de Bases de Données (SGBD)
est un logiciel destiné au stockage et à la manipulation de
bases de données.
FSEGN – Riadh Ben Messaoud
SGBD
4
Pourquoi un SGBD ?

Indépendance des données/applications et sûreté
d’accès aux données.

Temps de développement d’application réduit.

Intégrité des données et sécurité des accès.

Administration des données uniforme.

Concurrence des accès et reprise sur panne.
FSEGN – Riadh Ben Messaoud
SGBD
5
Modèle de données

Un modèle de données est un ensemble de concepts sur
les données.

Un schéma est une description d’un ensemble de données,
s’appuyant sur un modèle de données.

Le modèle relationnel est le plus répandu.
Concepts de base: relation, table avec des tuples et des
colonnes.
 Chaque relation a un schéma, qui décrit ses colonnes.


Les modèles objet et objet-relationnel sont utilisés pour
gérer des données complexes.

Les modèles semi-structurés se cherchent une place dans
les applications web, intégration de données, hétérogènes.
FSEGN – Riadh Ben Messaoud
SGBD
6
Niveaux d’abstraction

Plusieurs vues, un schéma
conceptuel et un schéma
physique.

Les vues (schéma externe)
décrivent comment l’utilisateur
voit les données.

Le schéma conceptuel
(schéma conceptuel) définit la
structure logique des données.

Le schéma physique (schéma
interne) décrit la structure
physique, de stockage, des
données.
ANSI – SPARC, 1975
FSEGN – Riadh Ben Messaoud
SGBD
7
Exemple : Base de données

Schéma conceptuel :




Schéma interne (physique)


élèves(ide: string, nom: string, prénom: string)
cours(idc: string, nomc:string, nbre_heures:integer)
inscription(ide:string, idc:string)
fichiers non séquentiels et index en b-arbres sur les attributs
clés.
Schéma externe (Vues) :

volume_elves(ide:string, nb_heures_tot:integer)
FSEGN – Riadh Ben Messaoud
SGBD
8
Indépendance des données

Les applications sont isolées des changements de
structure et du mode de stockage des données.

Indépendance logique des données : Protection des
changements de structure des données au niveau logique.

Indépendance physique des données : Protection des
changement de structure au niveau physique.

Un des plus importants bénéfices de l’utilisation des
SGBDs
FSEGN – Riadh Ben Messaoud
SGBD
9
Gestion de la concurrence

L’exécution concurrente de programmes est essentielle
pour un SGBD.

Les accès disque sont fréquents et relativement lents, il est
important que l ’unité centrale puisse exécuter de façon
concurrente les programmes des utilisateurs.

L’exécution partielle des actions de différents
programmes peut aboutir à des incohérences : ex.,
opération de débit en même temps que la réalisation d’un
virement.

Les SGBD assurent que la concurrence soit réalisée sans
problème : chaque utilisateur a l ’impression d’être seul à
travailler sur le système.
FSEGN – Riadh Ben Messaoud
SGBD
10
Gestion des transactions

Concept clé : transaction, une séquence atomique
d’actions sur une BD (lectures/écritures).

Chaque transaction doit laisser la BD dans un état cohérent
après l’avoir prise dans un état cohérent.

Les utilisateurs peuvent spécifier des contraintes d’intégrité
simples sur les données et le SGBD se charge de les garder
inviolables.

En dehors de ça, le SGBD n’a pas conscience de la sémantique
des données (ex., il ne comprend pas comment les intérêts
d’un compte bancaire sont calculés).

Le fait qu’une transaction préserve la cohérence de la BD est
au bout du compte de la responsabilité de l’utilisateur !
FSEGN – Riadh Ben Messaoud
SGBD
11
Concurrence des transactions

Les SGBD assurent que l’exécution de {T1, ... , Tn} soit
équivalente à une exécution en série T1’ ... Tn’.

Avant de lire/écrire un élément, chaque transaction demande à
émettre un verrou sur l’élément et attend que le SGBD lui
accorde ce verrou. Tous les verrous sont relâchés à la fin de la
transaction (protocole V2P strict.)

Idée : Si une action de Ti (ex., écrire X) affecte Tj (qui effectue
une lecture sur X), une des deux, disons Ti, obtient le verrou
sur X la première et Tj est forcée à attendre la fin de Ti; cette
façon de faire permet d’ordonner les transactions.

Et si Tj a déjà verrouillé Y et que Ti demande par la suite à
verrouiller Y? (Deadlock!) Ti ou Tj est abandonnée (aborted) et
remise en concurrence !
FSEGN – Riadh Ben Messaoud
SGBD
12
Atomicité

Les SGBD assurent l’atomicité (tout ou rien) même si un
crashes surgit au milieu d’une exécution de transaction.

Idée : garder un journal ou log (histoire) de toutes les
actions réalisées par le SGBD :

Avant qu’un changement ne soit réalisé sur la BD, l’action est
tracée dans un log file.

Après un crash, les effets d’une exécution partielle d’une
transaction sont défaites à l’aide du fichier log.
FSEGN – Riadh Ben Messaoud
SGBD
13
Journal

Les actions suivantes sont mémorisées dans le journal :
Ti écrit un élément: l’ancienne et la nouvelle valeur.
 Ti valide(commit)/abandonne(abort) : un enregistrement du
journal mémorise cette action.


Les enregistrements du journal contiennent l ’identifiant de
la transaction, ainsi il est facile de défaire une transaction
spécifique (ex., en cas de deadlock).

Le journal est souvent dupliqué et archivé sur un support
sûr.

Toute l’activité enregistrée dans le journal est gérée de
façon transparente par le SGBD.
FSEGN – Riadh Ben Messaoud
SGBD
14
Architecture d’un SGBD
Pages Web
Interface SQL
Applications
Commandes SQL
Exécuteur de plans
Parseur
Evaluateur d’opérateurs
Optimiseur
Gestionnaire de fichiers
Gestionnaire
de transactions
Gestionnaire de buffer
Gestionnaire
de verrous
Gestionnaire
de panne
Gestionnaire de disque
Fichiers index
Catalogue du système
Fichiers de données
FSEGN – Riadh Ben Messaoud
SGBD
15
Architecture d’un SGBD
Pages Web
Interface SQL
Applications
Commandes SQL
Exécuteur de plans
Parseur
Evaluateur d’opérateurs
Optimiseur
Gestionnaire de fichiers
Gestionnaire
de transactions
Gestionnaire de buffer
Gestionnaire
de verrous
Gestionnaire
de panne
Gestionnaire de disque
Fichiers index
Catalogue du système
Fichiers de données
FSEGN – Riadh Ben Messaoud
SGBD
Architecture
générale d'un
SGBD
16
Architecture d’un SGBD
Pages Web
Interface SQL
Applications
Commandes SQL
Exécuteur de plans
Parseur
Evaluateur d’opérateurs
Optimiseur
Gestionnaire de fichiers
Gestionnaire
de transactions
Gestionnaire de buffer
Gestionnaire
de verrous
Gestionnaire
de panne
Gestionnaire de disque
Fichiers index
Catalogue du système
Organisation
de données
Fichiers de données
FSEGN – Riadh Ben Messaoud
SGBD
17
Architecture d’un SGBD
Pages Web
Interface SQL
Applications
Commandes SQL
Exécuteur de plans
Parseur
Evaluateur d’opérateurs
Optimiseur
Gestionnaire de fichiers
Gestionnaire
de transactions
Gestionnaire de buffer
Gestionnaire
de verrous
Gestionnaire
de panne
Gestionnaire de disque
Fichiers index
Catalogue du système
Fichiers de données
FSEGN – Riadh Ben Messaoud
SGBD
Evaluation et
optimisation
des requêtes
18
Architecture d’un SGBD
Pages Web
Interface SQL
Applications
Commandes SQL
Exécuteur de plans
Parseur
Evaluateur d’opérateurs
Optimiseur
Gestionnaire de fichiers
Gestionnaire
de transactions
Gestionnaire de buffer
Gestionnaire
de verrous
Gestionnaire
de panne
Gestionnaire de disque
Fichiers index
Catalogue du système
Fichiers de données
FSEGN – Riadh Ben Messaoud
SGBD
Gestion de la
concurrence/
transactions
19
Architecture d’un SGBD
Pages Web
Interface SQL
Applications
Commandes SQL
Exécuteur de plans
Parseur
Evaluateur d’opérateurs
Optimiseur
Gestionnaire de fichiers
Gestionnaire
de transactions
Gestionnaire de buffer
Gestionnaire
de verrous
Gestionnaire
de panne
Gestionnaire de disque
Fichiers index
Catalogue du système
Gestion des
pannes
Fichiers de données
FSEGN – Riadh Ben Messaoud
SGBD
20
Résumé

Les SGBD sont utilisés pour maintenir et interroger un
volume de données important.

Quelques bénéfices : reprise sur panne, accès
concurrent, développement rapide d’applications,
intégrité et sécurité des données.

Les niveaux d’abstraction permettent l’indépendance des
données.

Un SGBD possède une architecture en couches.
FSEGN – Riadh Ben Messaoud
SGBD
21
Plan du cours
1.
2.
3.
4.
5.
6.
Introduction aux SGBDs
Architecture générale d'un SGBD
Organisation de données
Evaluation et optimisation des requêtes
Gestion de la concurrence / transactions
Gestion des pannes
FSEGN – Riadh Ben Messaoud
SGBD
22
Introduction
Rappel :
Une base de données relationnelle est constituée d’un ensemble de
relations où chaque relation possède un ensemble de n-uplets.

Physiquement ?
Les n-uplets d’une relation sont stockés dans un ou
plusieurs fichiers.
 Les fichiers peuvent être répartis sur un ou plusieurs
sites (BD distribuées).


Format de stockage des fichiers et performances
SGBD ?
Une utilisation optimale de la mémoire
 Un accès rapide
 Une mise à jour facile

FSEGN – Riadh Ben Messaoud
SGBD
23
Introduction

Schéma interne d’un SGBD


Définit les chemins d'accès aux données se trouvant dans les
fichiers de la BD
Problèmes ?
Allocation d'espace disque
 Accès aux n-uplets


Les n-uplets doivent êtres
transférés en mémoire centrale

Ce transfert se fait à travers une
zone particulière de la mémoire
 Mémoire tampon (buffer en anglais)
FSEGN – Riadh Ben Messaoud
SGBD
24
Introduction
Pages Web
Interface SQL
Applications
Commandes SQL
Gestionnaire
de transactions
Gestionnaire
de verrous
Exécuteur de plans
Parseur
Evaluateur d’opérateurs
Optimiseur
Gestionnaire de fichiers
Gestionnaire de buffer
Gestionnaire
de panne
Gestionnaire de disque
Fichiers index
Catalogue du système
Fichiers de données
FSEGN – Riadh Ben Messaoud
SGBD
25
Introduction
Pages Web
Interface SQL
Applications
Commandes SQL
Gestionnaire
de transactions
Gestionnaire
de verrous
Exécuteur de plans
Parseur
Evaluateur d’opérateurs
Optimiseur
Gestionnaire de fichiers
Gestionnaire de buffer
Gestionnaire
de panne
Gestionnaire de disque
Fichiers index
Catalogue du système
Fichiers de données
FSEGN – Riadh Ben Messaoud
SGBD
26
Visions des données

Vision des données par le SGBD : un ensemble
d’enregistrements (n-uplets) en mémoire.

Vision des données par le gestionnaire de fichiers : un
ensemble de pages mémoire

Vision des données par le gestionnaire de disque : un
ensemble de pages disque

Rôle du gestionnaire de buffer : passage des pages du
disque vers la mémoire (ou inversement)
FSEGN – Riadh Ben Messaoud
SGBD
27
Gestionnaire de buffer

Principaux objectifs :
Rendre les pages et les n-uplets qu’elles contiennent
accessibles en mémoire centrale
 Coordonner l’écriture des pages sur le disque en
coopération avec le gestionnaire de transactions
 Minimiser le nombre d’accès disques

Mémoire
tampon
FSEGN – Riadh Ben Messaoud
Disque
SGBD
28
Structure d’un buffer


Un buffer est formé d’une suite de cases contigües
dont chacune peut contenir une page
En mémoire centrale, une page est repérée par le
numéro de la case du buffer dans laquelle elle est
rangée
Mémoire centrale
Disque
FSEGN – Riadh Ben Messaoud
SGBD
29
Stratégie pour la gestion de buffer

Placer, au moment voulu, une page du disque vers la
mémoire et inversement

Politique de remplacement (ex. LRU, remplacer la page la
moins visitée)

Gestion des pages mises à jour

Partition de la mémoire

Vérification des droits sur les pages
FSEGN – Riadh Ben Messaoud
SGBD
30
Exemple
Mémoire
Disque
FSEGN – Riadh Ben Messaoud
SGBD
31
Exemple
Mémoire
Disque
FSEGN – Riadh Ben Messaoud
SGBD
32
Exemple
Mémoire
Disque
FSEGN – Riadh Ben Messaoud
SGBD
33
Exemple
Mémoire
Disque
FSEGN – Riadh Ben Messaoud
SGBD
34
Exemple
Mémoire
Modification
de la page
Disque
FSEGN – Riadh Ben Messaoud
SGBD
35
Exemple
Mémoire
Disque
FSEGN – Riadh Ben Messaoud
SGBD
36
Exemple
Mémoire
Date de montée
Date de dernier accès
…
Disque
FSEGN – Riadh Ben Messaoud
SGBD
37
Exemple
Mémoire
Page non accédée
depuis longtemps
Disque
FSEGN – Riadh Ben Messaoud
SGBD
38
Exemple
Mémoire
Disque
FSEGN – Riadh Ben Messaoud
SGBD
39
Exemple
Mémoire
Page non accédée
depuis longtemps
Disque
FSEGN – Riadh Ben Messaoud
SGBD
40
Recherche d’une page

L’opération de recherche d’une page :
a pour argument l’adresse p de la page cherchée ;
 retourne le numéro c de la case du buffer dans laquelle cette
page est rangée.

Mémoire centrale
c
Disque
p
FSEGN – Riadh Ben Messaoud
SGBD
41
Recherche d’une page

Cette opération se déroule selon les étapes suivantes :
Si la page p est dans la case c du buffer, retourner c. On
économise un accès disque.
2. Si la page p n’est pas dans le buffer, il faut la lire sur le
disque. On teste s’il existe une case libre pour la recevoir. Si
oui, soit c cette case.
3. Sinon, il faut libérer une case et donc renvoyer une page du
buffer sur le disque.
1.
1.
2.
Renvoyer la page la moins récemment utilisée (LRU)
Renvoyer la page la plus récemment utilisée (MRU)
Si la page à rejeter a été modifiée pendant son séjour en
mémoire centrale, il faut la réécrire sur le disque (gestionnaire
de transactions).
5. Transférer la page p du disque dans la case c et retourner c.
4.
FSEGN – Riadh Ben Messaoud
SGBD
42
Architecture d’un SGBD
Pages Web
Interface SQL
Applications
Commandes SQL
Exécuteur de plans
Parseur
Evaluateur d’opérateurs
Optimiseur
Gestionnaire de fichiers
Gestionnaire
de transactions
Gestionnaire de buffer
Gestionnaire
de verrous
Gestionnaire
de panne
Gestionnaire de disque
Fichiers index
Catalogue du système
Organisation
de données
Fichiers de données
FSEGN – Riadh Ben Messaoud
SGBD
43
Plan du cours
1.
2.
3.
4.
5.
6.
Introduction aux SGBDs
Architecture générale d'un SGBD
Organisation de données
Evaluation et optimisation des requêtes
Gestion de la concurrence / transactions
Gestion des pannes
FSEGN – Riadh Ben Messaoud
SGBD
44
Introduction
Pages Web
Interface SQL
Applications
Commandes SQL
Exécuteur de plans
Parseur
Evaluateur d’opérateurs
Optimiseur
Gestionnaire de fichiers
Gestionnaire
de transactions
Gestionnaire de buffer
Gestionnaire
de verrous
Gestionnaire
de panne
Gestionnaire de disque
Fichiers index
Catalogue du système
Organisation
de données
Fichiers de données
FSEGN – Riadh Ben Messaoud
SGBD
45
Introduction

Une BD doit assurer :

Le stockage des données


Conservation des données
Accès aux données
Structuration des données
 Moyens de manipulation des données


Afin d’accéder rapidement à un ensemble de n-uplets
vérifiant certaines conditions :
 Toute BD relationnelle est munie d’index qui permettent
d’associer chaque valeur d’un constituant à l’ensemble des
n-uplets qui possèdent cette valeur pour ce constituant.
FSEGN – Riadh Ben Messaoud
SGBD
46
Fichiers

Un fichier est enregistré sur un disque

Il peut s’étendre sur un ou plusieurs secteurs du disque

Un fichier possède un nom et est constitué par une suite de
blocs

On distingue quatre phases dans le cycle de vie d’un
fichier
L’allocation initiale
 L’expansion
 La contraction
 La réorganisation

FSEGN – Riadh Ben Messaoud
SGBD
47
Fichiers

L’allocation initiale :


Des blocs sont alloués au fichier lors de sa création
L’expansion :

Des blocs supplémentaires sont alloués au fur et à mesure des
besoins



La contraction :

Les blocs qui ne sont plus utilisés sont restitués



Bloc par bloc, à partir d’une pile de blocs libres
Par groupe de blocs contigus
Bloc par bloc sur la pile de blocs libres
Par groupe de blocs libres contigus ce qui permet d’éviter
l’éparpillement de la mémoire
La réorganisation :

On récupère les espèces mémoire libres qui ont pu
apparaître au cours des phases précédentes
FSEGN – Riadh Ben Messaoud
SGBD
48
Gestion des fichiers

Relation :


Champs :


Collection de pages ou blocs disque ou mémoire
Séquences d’octets de taille fixe ou variables représentant la
valeur d’un attribut de n-uplet sur le disque ou en mémoire
Enregistrement :

Collection de taille fixe ou variable de champs
FSEGN – Riadh Ben Messaoud
SGBD
49
Gestion des n-uplets

Les n-uplet d’une BD relationnelle sont rangés dans des
pages

Une page est stockée dans un bloc de fichier.

Supposons d’un bloc contient une seule page. L’adresse
physique d’un n-uplet est un quadruplet (s, f, b, r) où :
s est un identificateur de site
 f est un identificateur de fichier
 b et r sont des entiers positifs ou nuls

 Cette adresse désigne le rième n-uplet de la page enregistrée
dans le bième bloc du fichier f du site s
FSEGN – Riadh Ben Messaoud
SGBD
50
Structure d’une page

Une page est découpée en deux espaces :
La zone des n-uplets qui sont implantés dans un ordre
quelconque.
 Un répertoire implanté à la fin de la page. La ième case du
répertoire indique le déplacement du n-uplet i dans la page.

N-uplet (b,i)
k

i
1
Cette organisation permet l’évolution de la taille des
n-uplets sans qu’il soit nécessaire de modifier leurs
adresses dans la page.
FSEGN – Riadh Ben Messaoud
SGBD
51
Identification des n-uplets

Il est nécessaire d’identifier chaque n-uplet afin :



de le distinguer des autres n-uplets
de pouvoir y accéder à partir des index
Trois méthodes d’identification :
Par adressage direct
 Par adressage indirect
 Par la clé primaire

FSEGN – Riadh Ben Messaoud
SGBD
52
Identification des n-uplets

Identification par adressage direct

L’identificateur d’un n-uplet est son adresse physique

Cette méthode requiert la mise en place d’un pointeur de
suivi dans le cas où un n-uplet doit être changé de page suite
d’une mise à jour augmentant sa taille
N-uplet (b,i)
Numéro
de page
Déplacement
(i)
de bas de page
(bloc b)
FSEGN – Riadh Ben Messaoud
SGBD
53
Identification des n-uplets

Identification par adressage indirect

L’identificateur d’un n-uplet est un nombre entier attribué à
sa création.

Cette méthode nécessite la mise en place d’une table de
correspondance pour passer de l’identificateur à son adresse
physique.
(i)
N-uplet (b,i)
FSEGN – Riadh Ben Messaoud
SGBD
54
Identification des n-uplets

Identification par la clé primaire


Un index primaire permet d’assurer la correspondance entre
la clé et l’adresse physique du n-uplet.
Performances

L’identification par adressage direct est la méthode la plus
efficace. En effet :

Elle nécessite un seul accès disque pour accéder à un n-uplet
qui n’a pas de pointeur de suivi.

Elle nécessite deux accès disque pour accéder à un n-uplet
ayant un pointeur de suivi.

Elle ne nécessite pas de table de correspondance
FSEGN – Riadh Ben Messaoud
SGBD
55
Organisation des fichiers

Trois types de fichiers :
Fichiers séquentiels
 Fichiers accès direct
 Fichier séquentiels indexés (non-ordonnés)


Pour accéder à une page ou à un n-uplet dans un fichier,
on utilise les indexes

Un index est un fichier qui contient un ensemble
d’enregistrements formés d’une clé et d’une information
associée.
FSEGN – Riadh Ben Messaoud
SGBD
56

Dans une BD relationnelle, l’information associée est en
général un identificateur de n-uplet ou bien un ensemble
d’identificateurs de n-uplets (page).

Les indexes sont des fichiers qui contiennent les paires
Attribut(s) d'accès  Numéro de page

les indexes peuvent être
Séquentiels ou directs ou non-ordonnés
 Hachés
 Arbres B
 Autres

FSEGN – Riadh Ben Messaoud
SGBD
57
Plan du cours
1.
2.
3.
4.
5.
6.
Introduction aux SGBDs
Architecture générale d'un SGBD
Organisation de données
Evaluation et optimisation des requêtes
Gestion de la concurrence / transactions
Gestion des pannes
FSEGN – Riadh Ben Messaoud
SGBD
58
Introduction
Pages Web
Interface SQL
Applications
Commandes SQL
Exécuteur de plans
Parseur
Evaluateur d’opérateurs
Optimiseur
Gestionnaire de fichiers
Gestionnaire
de transactions
Gestionnaire de buffer
Gestionnaire
de verrous
Gestionnaire
de panne
Gestionnaire de disque
Fichiers index
Catalogue du système
Organisation
de données
Fichiers de données
FSEGN – Riadh Ben Messaoud
SGBD
59
Organisation des fichiers

Trois types de fichiers :
Fichiers séquentiels
 Fichiers accès direct
 Fichier séquentiels indexés (non-ordonnés)


Pour accéder à une page ou à un n-uplet dans un fichier,
on utilise les indexes

Un index est un fichier qui contient un ensemble
d’enregistrements formés d’une clé et d’une information
associée.
FSEGN – Riadh Ben Messaoud
SGBD
60
Organisation des fichiers
Architecture d’un gestionnaire de fichiers
Séquentiel
Ouvrir
Haché
Lire
Indexé 1
Ecrire
Adressage
ME 1
ME k
Indexé 2
Fermer
Méthodes
d’accès
Analyseur
Module
d’E/S
Disques
FSEGN – Riadh Ben Messaoud
SGBD
61
Organisation des fichiers
 Classification des index

Accès mono-critère

Arbres



Séquentiel par hachage



Séquentiel-indexé
Arbres B
Statique
Dynamique
Accès multi-critères

Arbres



Arbre k-d
Arbres R
Accès par hachage

Hachage partitionné
FSEGN – Riadh Ben Messaoud
SGBD
62
Accès par hachage statique

C’est la méthode la plus simple et la plus ancienne.

Se base sur l’utilisation d’une fonction de calcul qui,
appliquée à sa clé, détermine l’adresse de la page dans
laquelle sera placé un n-uplet.

Le fichier est de taille constante (fixée lors de sa création)
et est divisé en p pages de taille fixe L.

Dans une page (bucket), les n-uplets sont rangés à la suite
dans l’ordre d’arrivée.
FSEGN – Riadh Ben Messaoud
SGBD
63
Accès par hachage statique
Fichier haché statique (Static Hashed File)
Fichier de taille fixe dans lequel les n-uplets sont placés dans des
pages dont l’adresse est calculée à l’aide d’une fonction de
hachage fixe appliquée à la clé.
0
1
.
N-uplet
.
H
.
p-1
FSEGN – Riadh Ben Messaoud
SGBD
64
Accès par hachage statique

La fonction de hachage doit être choisie de façon à
distribuer uniformément les n-uplets dans les pages.

Plusieurs techniques :
Le pliage qui consiste à choisir et combiner les bits de la clé.
 Les conversions de la clé en nombre entier ou flottant avec
utilisation de la mantisse permettant d’obtenir un numéro de
page.
 Le modulo (la fonction la plus utilisée) qui consiste à prendre
pour numéro de page le reste de la division de la clé par le
nombre de pages.


Ces techniques peuvent avantageusement être combinées.
FSEGN – Riadh Ben Messaoud
SGBD
65
Accès par hachage statique

Un problème de débordement se pose lorsqu’une pages est
pleine.
Ne pas gérer les débordements (fichier saturé !)

Gestion des débordements :

L’adressage ouvert consiste à placer le n-uplet (qui devrait
aller dans une page pleine) dans le première page suivantes
ayant de la place libre.
 Le chaînage consiste à constituer une page logique par
chaînage d’une page de débordement à une page pleine.
 Le rehachage consiste à appliquer une deuxième fonction de
hachage lorsq’une page est pleine. Cette fonction conduit
généralement à placer les n-uplets dans des pages de
débordements.


Dans tous les cas, la gestion des débordements dégrade les
performances et complique la gestion des fichiers hachés.
FSEGN – Riadh Ben Messaoud
SGBD
66
Plan du cours
1.
2.
3.
4.
5.
6.
Introduction aux SGBDs
Architecture générale d'un SGBD
Organisation de données
Evaluation et optimisation des requêtes
Gestion de la concurrence / transactions
Gestion des pannes
FSEGN – Riadh Ben Messaoud
SGBD
67
Architecture d’un SGBD
Pages Web
Interface SQL
Applications
Commandes SQL
Exécuteur de plans
Parseur
Evaluateur d’opérateurs
Optimiseur
Gestionnaire de fichiers
Gestionnaire
de transactions
Gestionnaire de buffer
Gestionnaire
de verrous
Gestionnaire
de panne
Gestionnaire de disque
Fichiers index
Catalogue du système
Fichiers de données
FSEGN – Riadh Ben Messaoud
SGBD
Evaluation et
optimisation
des requêtes
68
Optimisation des requêtes

Exécution de requêtes :


Série d’opérations permettant d’extraire des données de la
base.
Optimisation de requêtes :

Activité permettant de choisir la meilleur stratégie d’exécution
d’une requête.
FSEGN – Riadh Ben Messaoud
SGBD
69
Phases d’exécution d’une requête
Requête en langage de haut niveau (SQL)
Catalogue du
système
Décomposition de la
requête
Expression en algèbre relationnelle
Optimisation de la
requête
Statistiques de
la base
Plan d’exécution
Génération du code
d’exécution
Code généré
Base de données
principale
Exécution du code
de la requête
Résultat de la requête
FSEGN – Riadh Ben Messaoud
SGBD
70
Exemple d’une requête

Quels sont les noms des commerciaux basés dans les filiale
de Londres ?

SQL :
SELECT e.nom FROM Employe e, Filiale f
WHERE e.id_filiale = f.id_filiale
AND e.position = ‘commercial’
AND f.ville = ‘Londres’


Employe contient 1000 n-uplets
Filiale contient 50 n-uplets

Il y a 50 commerciaux et 5 filiales à Londres

Trois requêtes sont possibles en algèbre relationnelle
Calcul du coût de chaque requête en terme d’E/S

FSEGN – Riadh Ben Messaoud
SGBD
71
Phase 1 : Décomposition

Transformation de la requête SQL en une requête en
algèbre relationnelle

Vérification syntaxique et sémantique de la requête

Utilisation du catalogue du système

Représentation de la requête par un arbre d’opérateurs
algébriques
FSEGN – Riadh Ben Messaoud
SGBD
72
Catalogue du système

Appelé également dictionnaire de données

Contient la description des données de la base

Pour chaque relation :





Pour chaque index



Nom de la relation, identificateur du fichier et structure du fichier
Nom et domaine de chaque attribut
Nom des index
Contraintes d’intégrité
Nom et structure de l’index
Attribut appartenant à la clé de recherche
Pour chaque vue


Nom de la vue
Définition de la vue
FSEGN – Riadh Ben Messaoud
SGBD
73
Catalogue du système

Contient également des données statistiques






Cardinalité de chaque relation
Nombre de pages de chaque relation
Nombre de valeurs distinctes et de clés de recherche pour
chaque index
Hauteur des index et structures arborescentes
Valeur minimum et valeur maximum de chaque clé de
recherche dans chaque index
Par exemple, sous Oracle 8 :
USER_ALL_TABLES
 USER_CONSTRAINTS

FSEGN – Riadh Ben Messaoud
SGBD
74
Arbre algébrique

Représentation des relations impliquées dans la requête par
les nœuds feuille de l’arbre

Représentation des résultats intermédiaires par les nœud
non feuilles

Représentation du résultat de la requête par la racine de
l’arbre

Ordre des séquences d’opérations : des feuilles vers la
racine
FSEGN – Riadh Ben Messaoud
SGBD
75
Arbre algébrique
FSEGN – Riadh Ben Messaoud
SGBD
76
Phase 2 : Optimisation

Equivalences d’expressions
FSEGN – Riadh Ben Messaoud
SGBD
77
Phase 2 : Optimisation

Equivalences d’expressions
FSEGN – Riadh Ben Messaoud
SGBD
78
Phase 2 : Optimisation

Equivalences d’expressions
FSEGN – Riadh Ben Messaoud
SGBD
79
Transformation d’un arbre algébrique
1.
Division des conjonctions de sélections
2.
Ré-ordonnancement des sélections en utilisant la
commutativité des sélections et la commutativité des
sélections et des projections
3.
Application des sélections les plus sélectives en premier
4.
Transformation des produits cartésiens en jointure
5.
Ré-ordonnancement des équi-jointure en utilisant
l’associativité des jointures
6.
Déplacement des projections et création des nouvelles
projections en utilisant la commutativité des sélections et
des projections
FSEGN – Riadh Ben Messaoud
SGBD
80
FSEGN – Riadh Ben Messaoud
SGBD
81
FSEGN – Riadh Ben Messaoud
SGBD
82
FSEGN – Riadh Ben Messaoud
SGBD
83
FSEGN – Riadh Ben Messaoud
SGBD
84
FSEGN – Riadh Ben Messaoud
SGBD
85
FSEGN – Riadh Ben Messaoud
SGBD
86
FSEGN – Riadh Ben Messaoud
SGBD
87
FSEGN – Riadh Ben Messaoud
SGBD
88
FSEGN – Riadh Ben Messaoud
SGBD
89
FSEGN – Riadh Ben Messaoud
SGBD
90
FSEGN – Riadh Ben Messaoud
SGBD
91
FSEGN – Riadh Ben Messaoud
SGBD
92
FSEGN – Riadh Ben Messaoud
SGBD
93
Statistiques

Pour chaque relation R :
Le nombre de n-uplets
 Le nombre de n-uplets par bloc
 Le nombre de blocs pour la relation


Pour chaque attribut A de R :
Le nombre de valeurs distinctes de A
 Valeurs min et max de A
 Nombre moyen de n-uplets satisfaisant un prédicat sur A


Pour un index I de R sur un attribut A :
Le nombre de niveaux pour I
 Le nombre de blocs utilisés pour I

FSEGN – Riadh Ben Messaoud
SGBD
94
FSEGN – Riadh Ben Messaoud
SGBD
95
Plan du cours
1.
2.
3.
4.
5.
6.
Introduction aux SGBDs
Architecture générale d'un SGBD
Organisation de données
Evaluation et optimisation des requêtes
Gestion de la concurrence / transactions
Gestion des pannes
FSEGN – Riadh Ben Messaoud
SGBD
96
Architecture d’un SGBD
Pages Web
Interface SQL
Applications
Commandes SQL
Exécuteur de plans
Parseur
Evaluateur d’opérateurs
Optimiseur
Gestionnaire de fichiers
Gestionnaire
de transactions
Gestionnaire de buffer
Gestionnaire
de verrous
Gestionnaire
de panne
Gestionnaire de disque
Fichiers index
Catalogue du système
Fichiers de données
FSEGN – Riadh Ben Messaoud
SGBD
Evaluation et
optimisation
des requêtes
97
Statistiques

Pour chaque relation R :
NTuples(R) : nombre de n-uplets
 Bfactor(R) : nombre de n-uplets par bloc
 NBlock(R) : nombre de blocs pour la relation


Pour chaque attribut A de R :
NDistinctA(R) : nombre de valeurs distinctes de A
 MinA(R) et MaxA(R) : valeurs min et max de A
 SCA(R) : nombre moyen de n-uplets satisfaisant un prédicat
sur A


Pour un index I de R sur un attribut A :
NLevelsA(I) : nombre de niveaux pour I
 NLBlocksA(I) : e nombre de blocs utilisés pour I

FSEGN – Riadh Ben Messaoud
SGBD
98
Coût de σp(R)
Stratégies :
 Recherche linéaire (fichier non ordonné sans index)
 Recherche binaire (fichier ordonné sans index)
 Condition d’égalité sur la valeur d’une fonction de hachage
 Condition d’égalité sur la clé primaire
 Condition d’inégalité sur la clé primaire
 Condition d’égalité sur la clé d’un index secondaire clustered
 Condition d’égalité sur la clé d’un index secondaire
unclustered
 Condition d’inégalité sur un index secondaire en arbre B+
FSEGN – Riadh Ben Messaoud
SGBD
99
Coût de σp(R)
FSEGN – Riadh Ben Messaoud
SGBD
100
Coût de R ∞F S
Stratégie d’implantations de la jointure :

Boucles imbriquées (Block nested loop join)

Boucles imbriquées en utilisant un index (idexed nested
loop join)

Tri-fusion (Sort-merge join)

Hachage (Hash join)
 Pour chaque algorithme, possibilité de le faire en multipassses
FSEGN – Riadh Ben Messaoud
SGBD
101
Cardinalité de R ∞F S

NTuples(R ∞F S) ≤ NTuples(R) * NTuples(S)

Lorsque le prédicat est R.A=S.B

Si A est clé de R : NTuples(R ∞F S) ≤ NTuples(S)

Si B est clé de S : NTuples(R ∞F S) ≤ NTuples(R)
Si ni A ni B ne sont clés :
NTuples(R ∞F S) ~ Max(SCA(R)*NTuples(S), SCA(S)*NTuples(R))

FSEGN – Riadh Ben Messaoud
SGBD
102
Boucles imbriquées
FSEGN – Riadh Ben Messaoud
SGBD
103
Boucles imbriquées
FSEGN – Riadh Ben Messaoud
SGBD
104
Boucles imbriquées
S’il y a Nbuffer places en mémoire, on monte les pages de R en mémoire
par paquets de (NBuffer – 2) pages
FSEGN – Riadh Ben Messaoud
SGBD
105
Boucles imbriquées
Les blocks de S sont montés en mémoire autant de fois qu’il y a de block
dans R
FSEGN – Riadh Ben Messaoud
SGBD
106
Boucles imbriquées
S’il existe un index sur l’attribut de jointure d’une des deux relations, on
l’utilise pour trouver les n-uplets participant à la jointure
FSEGN – Riadh Ben Messaoud
SGBD
107
Tri-Fusion

Première étape : Tri des relations sur l’attribut de jointure


Exemple : Tri externe ou tri par séries monotones
Deuxième étape : Equi-jointure des deux relations triées

En parcourant simultanément les deux relations page par page
FSEGN – Riadh Ben Messaoud
SGBD
108
Tri externe : première étape
FSEGN – Riadh Ben Messaoud
SGBD
109
Tri externe : première étape
FSEGN – Riadh Ben Messaoud
SGBD
110
Tri externe : première étape
FSEGN – Riadh Ben Messaoud
SGBD
111
Tri externe : première étape
FSEGN – Riadh Ben Messaoud
SGBD
112
Tri externe : première étape
FSEGN – Riadh Ben Messaoud
SGBD
113
Tri externe : première étape
FSEGN – Riadh Ben Messaoud
SGBD
114
Tri externe : première étape
FSEGN – Riadh Ben Messaoud
SGBD
115
Tri externe : première étape
FSEGN – Riadh Ben Messaoud
SGBD
116
Tri externe : première étape
FSEGN – Riadh Ben Messaoud
SGBD
117
Tri externe : première étape
FSEGN – Riadh Ben Messaoud
SGBD
118
Tri externe : première étape
FSEGN – Riadh Ben Messaoud
SGBD
119
Tri externe : première étape
FSEGN – Riadh Ben Messaoud
SGBD
120
Tri externe : première étape
FSEGN – Riadh Ben Messaoud
SGBD
121
Tri externe : première étape
FSEGN – Riadh Ben Messaoud
SGBD
122
Tri externe : première étape
FSEGN – Riadh Ben Messaoud
SGBD
123
Tri externe
Ensemble
de départ
Ensemble
d’arrivé
Après la première étape de tri, les pages sont triées par paquets de 2
 3 paquets triés de 2 pages, soit 2*5 E/S
FSEGN – Riadh Ben Messaoud
SGBD
124
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
125
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
126
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
127
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
128
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
129
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
130
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
131
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
132
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
133
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
134
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
135
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
136
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
137
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
138
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
139
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
140
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
141
Tri externe : deuxième étape
FSEGN – Riadh Ben Messaoud
SGBD
142
Tri externe
Après la deuxième étape de tri, les paquets sont triés 2 par 2  1
paquets trié de 4 pages et 1 paquet de 1 pages, soit 2 * 5 E/S
FSEGN – Riadh Ben Messaoud
SGBD
143
Fusion
FSEGN – Riadh Ben Messaoud
SGBD
144
Fusion
FSEGN – Riadh Ben Messaoud
SGBD
145
Fusion
FSEGN – Riadh Ben Messaoud
SGBD
146
Fusion
FSEGN – Riadh Ben Messaoud
SGBD
147
Fusion
FSEGN – Riadh Ben Messaoud
SGBD
148
Fusion
FSEGN – Riadh Ben Messaoud
SGBD
149
Fusion
FSEGN – Riadh Ben Messaoud
SGBD
150
Fusion
FSEGN – Riadh Ben Messaoud
SGBD
151
Fusion
FSEGN – Riadh Ben Messaoud
SGBD
152
Fusion
FSEGN – Riadh Ben Messaoud
SGBD
153
Fusion
FSEGN – Riadh Ben Messaoud
SGBD
154
Fusion
FSEGN – Riadh Ben Messaoud
SGBD
155
Fusion
FSEGN – Riadh Ben Messaoud
SGBD
156
Fusion
FSEGN – Riadh Ben Messaoud
SGBD
157
Fusion
FSEGN – Riadh Ben Messaoud
SGBD
158
Architecture d’un SGBD
Pages Web
Interface SQL
Applications
Commandes SQL
Exécuteur de plans
Parseur
Evaluateur d’opérateurs
Optimiseur
Gestionnaire de fichiers
Gestionnaire
de transactions
Gestionnaire de buffer
Gestionnaire
de verrous
Gestionnaire
de panne
Gestionnaire de disque
Fichiers index
Catalogue du système
Fichiers de données
FSEGN – Riadh Ben Messaoud
SGBD
Evaluation et
optimisation
des requêtes
159
Rappel
La résolution d’une requête se déroule en trois phases :

Traduction de la requête en un arbre d’opérateurs
relationnels (sélection, jointure, projection, etc.)

Optimisation de l’arbre de requête. Il s’agit de déterminer
un plan d’exécution de coût minimal. Deux procédures sont
mises en œuvres :
Transformation de l’arbre de requête en un arbre équivalent
en se basant sur les propriétés d’associativité et de
commutativité de l’algèbre relationnelle.
 Evaluation du coût de résolution de chaque opérateur.


Evaluation du plan d’exécution.
FSEGN – Riadh Ben Messaoud
SGBD
160
Etude de cas

Soit le schéma relationnel suivant :
BIBLIOTHEQUE(NOBIB, NOM, VILLE)
LIVRE(ISBN, TITRE, ANNEE, MATIERE)
POSSEDE(COTE, NOBIB, ISBN)
EMPRUNTE(NOM, COTE, DATE)
FSEGN – Riadh Ben Messaoud
SGBD
161
Etude de cas

Trouver la requête SQL qui permet d’extraire les
noms des emprunteurs des livres publiés après 1985
et possédés par une bibliothèque de Nabeul ?
SELECT E.NOM
FROM EMPRUNTE E, POSSEDE P, LIVRE L,
BIBLIOTHEQUE B
WHERE
L.ANNEE > 1985
AND B.VILLE = ‘Nabeul’
AND E.COTE = P.COTE
AND P.ISBN = L.ISBN
AND P.NOBIB = B.NOBIB
FSEGN – Riadh Ben Messaoud
SGBD
162
Etude de cas

Traduire la requête en un arbre d’opérateurs ?
E.NOM
L.ANNEE
B.VILLE
P.NOBIB
P.ISBN
E.COTE
FSEGN – Riadh Ben Messaoud
1985
‘Nabeul’
B.NOBIB
=
L.ISBN
=
P.COTE
=
E
>
=
P
SGBD
L
B
163
Etude de cas

Optimiser l’arbre d’opérateurs ?
E.NOM
L.ANNEE
B.VILLE
P.NOBIB
P.ISBN
E.COTE
FSEGN – Riadh Ben Messaoud
1985
‘Nabeul’
Division des
conjonctions de
sélections
B.NOBIB
=
L.ISBN
=
P.COTE
=
E
>
=
P
L
SGBD
B
164
Etude de cas

Optimiser l’arbre d’opérateurs ?
L.ANNEE
B.VILLE
>
=
1985
‘Nabeul’
FSEGN – Riadh Ben Messaoud
SGBD
L.ANNEE
>
1985
B.VILLE
=
‘Nabeul’
165
Etude de cas
E.NOM
Application des
sélections les plus
sélectives en premier
L.ANNEE
>
1985
B.VILLE
=
‘Nabeul’
P.NOBIB
P.ISBN
E.COTE
FSEGN – Riadh Ben Messaoud
SGBD
L.ISBN
=
P.COTE
=
E
B.NOBIB
=
P
L
B
166
Etude de cas
E.NOM
P.NOBIB
B.NOBIB
=
Création de nouvelles
projections
P.ISBN
E.COTE
L.ISBN
=
P.COTE
=
L.ANNEE
E
FSEGN – Riadh Ben Messaoud
P
=
L
SGBD
1985 B.VILLE
=
‘Nabeul’
B
167
Etude de cas
E.NOM
P.NOBIB
P.ISBN
=
B.NOBIB
L.ISBN
=
B.NOBIB
E.COTE
L.ISBN
P.COTE
=
E.NOM
E.COTE
L.ANNEE
E
P
FSEGN – Riadh Ben Messaoud
=
L
SGBD
1985 B.VILLE
=
‘Nabeul’
B
168
Plan du cours
1.
2.
3.
4.
5.
6.
Introduction aux SGBDs
Architecture générale d'un SGBD
Organisation de données
Evaluation et optimisation des requêtes
Gestion de la concurrence / transactions
Gestion des pannes
FSEGN – Riadh Ben Messaoud
SGBD
169
Architecture d’un SGBD
Pages Web
Interface SQL
Applications
Commandes SQL
Exécuteur de plans
Parseur
Evaluateur d’opérateurs
Optimiseur
Gestionnaire de fichiers
Gestionnaire
de transactions
Gestionnaire de buffer
Gestionnaire
de verrous
Gestionnaire
de panne
Gestionnaire de disque
Fichiers index
Catalogue du système
Fichiers de données
FSEGN – Riadh Ben Messaoud
SGBD
Evaluation et
optimisation
des requêtes
170
Rappel

Les langages de requêtes de haut niveau comme SQL sont
déclaratifs. L'utilisateur:
indique ce qu'il veut obtenir.
 n'indique pas comment l'obtenir.


Donc le système doit faire le reste:
Déterminer le (ou les) chemin(s) d'accès aux données,
 les stratégies d'évaluation de la requête
 Choisir la meilleure. Ou une des meilleures …


Deux niveaux d’optimisation
Optimisation logique
 Optimisation physique

FSEGN – Riadh Ben Messaoud
SGBD
171
Estimation des résultats intermédiaires

L’estimation des de la taille des résultats intermédiaire
d’une requête se base sur un modèle de coût

Le modèle le plus simple est celui qui suppose
l’uniformité de la distribution des valeurs
 l’indépendance des attributs

 En l’absence d’informations précises, Ces hypothèses
sont utilisées dans tous les optimiseurs des systèmes
FSEGN – Riadh Ben Messaoud
SGBD
172
Modèle de coût

Un modèle de coût nécessite de connaître au minimum :

Le nombre de valeurs d’un attribut A noté Tuple(A)

Les valeurs minimale et maximale d’un attribut A notées
min(A) et max(A)

Le nombre de valeurs distinctes de chaque attribut A noté
NDIST(A)

Le nombre de tuples de chaque relation R noté Tuple(R)
FSEGN – Riadh Ben Messaoud
SGBD
Modèle de coût

Le nombre de tuples d’une sélection est alors calculé par la
formule :
Tuple(s(R)) = P(Critère) * Tuple(R)

P(Critère) désigne de la probabilité que le critère soit vérifié,
appelée facteur de sélectivité du prédicat de sélection
Facteur de sélectivité
Coefficient associé à une opération sur une table représentant
la proportion de tuples de la table satisfaisant la condition
de sélection
FSEGN – Riadh Ben Messaoud
SGBD
Modèle de coût
Sélectivité des opérations de sélections (restrictions)
Tuple(s(R)) = s * Tuple(R)








s (A = valeur) = 1/NDIST(A)
s(A > valeur) = (max(A) - valeur) / (max(A) - min(A))
s(A < valeur) = (valeur - min(A)) / (max(A) - min(A))
s (A IN liste valeurs) = (1/NDIST(A)) * Card(liste valeurs)
s(P et Q) = s(P) * s(Q)
s(P ou Q) = s(P) + s(Q) - s(P) * s(Q)
s( not P) = 1 - s(P)
Le coût dépend de l'algorithme (index, hachage ou balayage).
FSEGN – Riadh Ben Messaoud
SGBD
Modèle de coût
Sélectivité des opérations de sélections (restrictions)

Exemple
SELECT *
FROM R1
WHERE A = valeur
 s = 1/NDIST(A) avec un modèle uniforme
FSEGN – Riadh Ben Messaoud
SGBD
Sélectivité des Projections
Sélectivité d’une projection

Le nombre de tuples d’une projection sur un groupe
d’attributs X est obtenu par la formule :
Tuple(p(R)) = (1-d) * Tuple(R)

d = probabilité de doubles qui peut être estimée en fonction
du nombre de valeurs distinctes des attributs composant X
FSEGN – Riadh Ben Messaoud
SGBD
Sélectivité des Jointures
Sélectivité d’une jointure

La taille d’une jointure est plus difficile à évaluer en général
Tuple(R1 ∞ R2) = p * Tuple(R1) * Tuple(R2)

p dépend du type de jointure et de la corrélation des
colonnes :
p = 0 si aucun tuple ne joint
 p = 1 / min(NDIST(A),NDIST(B)) si distribution uniforme
équiprobable des attributs A et B sur un même domaine
 p = 1 si produit cartésien


L'algorithme change radicalement les coûts
linéaire si index
 produit des tailles si boucles imbriquées.

FSEGN – Riadh Ben Messaoud
SGBD
Le calcul des tailles

Taille des tables de base dans le catalogue

Calcul des tailles à la compilation
application du coefficient de sélectivité
 hypothèse d’uniformité


Possibilité d’histogrammes
RunStat(<Table>, <attribut>)
 Stockage dans le catalogue de l’histogramme de distribution de
l’attribut
 Utilisation par le modèle de coût

FSEGN – Riadh Ben Messaoud
SGBD
179
Choix du meilleur plan
Schéma interne
Arbre
d'opérations
Plans d'exécution
Générateur de
Plans
Stratégie de
Recherche
Bibliothèque de
transformations
Heuristique
s
de choix
Modèle de coût
Plan d'exécution
Optimal
FSEGN – Riadh Ben Messaoud
SGBD
Choix du meilleur plan

Le nombre des plan d’exécution possibles peut être très
grand

L’ensemble des plans possibles est appelé espace des
plans

Le générateur de plan utilise une stratégie de recherche
pour le choix du meilleur plan dont le coût se rapproche au
mieux du minimum possible
FSEGN – Riadh Ben Messaoud
SGBD
181
Choix du meilleur plan
Algorithme de sélectivité minimale

Ordonnancement des jointures par ordre de sélectivité
croissante
Rels = liste des relations à joindre ;
p = plus petite relation ;
Tant que Rels non vide {
R = relation de selectivité minimum de Rels ;
p = join(R,p) ;
Rels = Rels - R ; } ;
Return(p) ;
FSEGN – Riadh Ben Messaoud
SGBD
182
Choix du meilleur plan
Programmation dynamique (DP)

Méthode de la recherche opérationnelle

Toute sous-politique d’une politique optimale est optimale

Si deux sous-plans équivalents sont construits, il suffit de
grader le meilleur et de le compléter pour atteindre le plan
optimal

Construit progressivement le plan optimal, par ajouts
successifs d’opérateurs
FSEGN – Riadh Ben Messaoud
SGBD
183
Choix du meilleur plan
Les stratégies de recherche
Stratégie
de recherche
Enumérative
Exhaustive
Augmentation
FSEGN – Riadh Ben Messaoud
Aléatoire
Amélioration
itérative
SGBD
Recuit
simulé
Génétique
184
Exemple
CINEMA (Cinéma, Adresse, Gérant)
SALLE (Cinéma, NoSalle, Capacité)

Avec les hypothèses suivantes :
Il y a 300 n-uplets dans CINEMA, occupant 30 pages.
 Il y a 1200 n-uplets dans SALLE, occupant 120 pages.

FSEGN – Riadh Ben Messaoud
SGBD
185
Exemple

On cherche les adresses des cinémas ayant des salles de
plus de 150 places

On suppose que 5% des cinémas ont cette capacité

En SQL, cette requête s'exprime de la manière suivante :
SELECT Adresse
FROM CINEMA, SALLE
WHERE SALLE.capacité >150
AND
CINEMA.cinéma = SALLE.cinéma
FSEGN – Riadh Ben Messaoud
SGBD
186
Exemple
Adresse
Coût :
capacité > 150
Jointure : 30 * 120 =
3600 pages
(boucles)
cinéma=cinéma
Cinema
FSEGN – Riadh Ben Messaoud
Salle
SGBD
187
Exemple
Coût :
Adresse
Sélection : 120 * 5% = 6 pages
Lectures : 120 + 6
Jointure : 30 * 6 = 180 pages
Lectures : 180
cinéma=cinéma
Total = 306 pages
Capacité > 150
Cinéma
Salles
FSEGN – Riadh Ben Messaoud
SGBD
188
Comment optimiser ?

Traduire une requête exprimée avec un langage déclaratif
en une suite d'opérations (typiquement opérateurs de
l'algèbre relationnelle).


En fonction (i) des coûts de chaque opération (ii) des
caractéristiques de la base, (iii) des algorithmes utilisés,
estimer la meilleure stratégie.

On obtient le plan d'exécution de la requête.

L'exécuter.
FSEGN – Riadh Ben Messaoud
SGBD
189
Conclusion

L'optimisation s'appuie sur des :
règles de ré-écriture des expressions de l'algèbre.
 connaissances sur l'organisation physique de la base (index,
hachage, …)
 statistiques sur la base (taille des relations par exemple).


Un modèle de coût permet de classer les différentes
stratégies envisagées.

Un domaine critique pour l’exécution et l’évaluation des
requêtes en un temps raisonnable

Un impératif associant l’utilisateur, le SGBD et le
compilateur / optimisateur de requêtes
FSEGN – Riadh Ben Messaoud
SGBD
190