1

Arbre-B et Arbre B+ Clés non dupliquées

Ordre inférieur

Hauteur supérieur

Liste chaînée des clés

Parcours plus rapide d'un intervalle

Insertion couteuse

1

40

25 60

30 50 53 7010 20

Bloc 0 Bloc 3 Bloc 1 Bloc 4

Bloc 2 Bloc 5

Bloc 6

10

Bloc 0

20

25 40 60

Bloc 2

25

Bloc 3

30 40

Bloc 1

50 53 60

Bloc 4

70

Arbre-B

Arbre-B+

1

Index bitmap

Permet d’accéder facilement aux n-uplets dans les colonnes ont une valeur V couleurYeux = brun et sexe = M

10111001 et 11010100 = 10010000

=> masque associé aux combinaison de valeurs Utile pour des valeurs peu modifiées Généralement considéré pour un faible nombre de valeur

En pratique compressé, donc s’adapte bien à un grand nombre de valeur Utilisé en data-mining

2

indice noPermis sexe couleurYeux bitmapsexe = M

bitmapsexe = F

bitmapcouleurYeux= bleu

bitmapcouleurYeux= brun

bitmapcouleurYeux= rouge

1 G111 M brun 1 0 0 1 0

2 G555 M bleu 1 0 1 0 03 G222 F brun 0 1 0 1 0

4 G888 M brun 1 0 0 1 0

5 G777 F brun 0 1 0 1 0

6 G666 M rouge 1 0 0 0 1

7 G333 F bleu 0 1 1 0 0

8 G444 F brun 0 1 0 1 0

2

Hachage Fonction h(clé de hachage) => l'adresse d'un paquet

Fichier = tableau de paquets

TH : taille de l'espace d'adressage primaire

TAB[TH - 1]

Habituellement paquet = bloc

Pas d’index à traverser : O(1) en meilleur cas

Sélection uniquement par égalité (pas intervalle)

33

Hachage statique

4

60 Erable argenté 15.99

90 Pommier 25.99

81 Catalpa 25.99

70 Herbe à puce 10.99

40 Epinette bleue 25.99

10 Cèdre en boule 10.99

20 Sapin 12.99

50 Chêne 22.99

95 Génévrier 15.99

80 Poirier 26.99

0

1

2

clé = 10

h(10) = 10 MOD 3 = 1

4

Problème de débordement dû aux collisions

Méthode de résolution des collisions

Adressage ouvert

AC+1, AC+2,....., n-1, 0, 1, ....AC-1

Chaînage

5

60 Erable argenté 15.99

90 Pommier 25.99

81 Catalpa 25.99

70 Herbe à puce 10.99

40 Epinette bleue 25.99

10 Cèdre en boule 10.99

43 Magnolia 28.99

20 Sapin 12.99

50 Chêne 22.99

95 Génévrier 15.99

80 Poirier 26.99

0

1

2

52 Pin 18.99

5

Fonction de hachage Répartition uniforme des clés dans tab[TH - 1]

h(clé) = clé MOD TH

h(clé) = (∑ si) MOD TH

si est une sous-séquence des bits de la clé

Clé non numérique

représentation binaire vue comme un entier

66

2

Hachage vs indexage O(1) en meilleur cas vs O(log(N))

Pas d’espace supplémentaire d’index pour le hachage

Gaspillage d’espace si TH trop grand

Performance dégradée si TH trop petit (chaînage)

Gestion plus délicate

déterminer h et TH

maintenance : réorganisations

Clé non numérique ?

représentation binaire vue comme un entier

77

Fonction de hachage préservant la relation d'ordre

Hash Tidy functions

clé1 < clé2 h(clé1) < h(clé2)

Connaissances préalables au sujet de la distributiondes clés

88

Hachage dynamique

Adaptation de TH et h aux variations du volume des données

similaire arbre-B

division et fusion de paquets (blocs)

99

Hachage dynamique

Adaptation de TH

suite d’expansions

Début de la dième expansion, d {0, 1, …}

TH passera de 2d à 2d+1

adresse du paquet : hd(clé) = bd-1, bd-2,…, b1, b0

10

101002

111012

000012

110102

011112

110112

002

012

102

112

p

d = 2

10

Insertion de h(clé) = 101012

Bloc #012 déborde

Division du bloc p = #002 (pas #012)

p := p+1

11

101002

111012

000012

110102

011112

110112

002

012

102

112

p

111012

000012

110102

011112

110112

0002

012

102

112

p

101012

101002

Zône primaire Zône d'expansion

1002

Division du bloc 002

11

Insertion de h(clé) = 101112

Bloc #112 déborde

12

111012

000012

110102

011112

110112

0002

012

102

112

p

101012

101002

1002

000012

110102

011112

110112

0002

0012

102

112

p

101112

101002

Zône primaire Zône d'expansion

1002

Division du bloc 012 (p = 01

2)

101012

111012

1012

12

3

Insertion de 110002, 110012 et 101102

13

000012

110102

011112

110112

0002

0012

102

112

p

101112

101002

1002

101012

111012

1012

110002

000012

110012

110102

101102

011112

110112

0002

0012

102

112

p

101112

101002

Zône primaire Zône d'expansion

1002

101012

111012

1012

13

Insertion de 100102

Bloc #102 déborde et est divisé

14

110002

000012

110012

110102

100102

011112

110112

0002

0012

0102

112

p

101112

101002

Zône primaire Zône d'expansion

1002

Division du bloc 102 (p = 10

2)

101012

111012

1012

101102

1102

110002

000012

110012

110102

101102

011112

110112

0002

0012

102

112

p

101112

101002

1002

101012

111012

1012

14

Insertion de 011012 Bloc #1012 déborde

zone d ’expansion !

Fin de l ’expansion

p := 0

d := d+1 = 3

15

110002

000012

110012

110102

100102

011112

110112

0002

0012

0102

112

p

101112

101002

1002

101012

111012

1012

101102

1102

110002

000012

110012

110102

100102

110112

0002

0012

0102

0112

p

101002

Zone primaire

1002

Division du bloc 112

101012

111012

1012

101102

1102

011012

011112

101112

1112

d = 3

15

Tableau comparatif des organisations

16

Critère Séquentiel Arbre-B+ primaire

Hachage statique

Hachage dynamique

Sélection par égalité sur clé unique

O(N/FB) Cas moyen : O(N/FB/2)

O(log (N)) Meilleur cas :O(1) Pire cas : O(N/FB)

Meilleur cas :O(1) Pire cas : O(N/FB)

Sélection par égalité sur clé non unique

O(N/FB) O(log (Card(clé)+ Sel/FB)

Meilleur cas : O(Sel/FB) Pire cas : O(N/FB)

Meilleur cas : O(Sel/FB) Pire cas : O(N/FB)

Sélection par intervalle O(N/FB) O(log (Card(clé)+ Sel/FB)

O(N/FB) O(N/FB)

Itération sérielle O(N/FB) O(N) O(N/FB) O(N/FB)

Itération séquentielle O(tri) O(N/FB) O(tri) O(tri)

Insertion/suppression O(1) O(log N) Meilleur cas :O(1) Pire cas : O(N/FB)

Meilleur cas :O(1) Pire cas : O(N/FB)

Taux d'occupation mémoire secondaire

Maximal approche 100%

En moyennne : 66% (2/3)

~80% À ajuster Pire cas non borné

~69% (ln 2) Peut augmenter au prix d'une diminution de performance.

Autres considérations Plusieurs index secondaires possibles sur la même table

Distribution des clés par fonction de hachage ? Problème avec tables volatiles Resp. du DBA

Disponibilité restreinte

16

Indexation sous OracleOracle propose comme index les différentes structures d’index présentées précédemment

Par défaut l’index est un arbre B+

Il est possible d’organiser une table en arbre B (plaçant)

Le hachage (non dynamique) existe aussi

Index bitmap

17

Création d’indexUn index est créé automatiquement pour les clé primaires et les colonnes d’une contrainte unique

Création d’un index explicitement

CREATE INDEX emp_ename ON emp(ename)

TABLESPACE users

STORAGE ( INITIAL 20K NEXT 20k

PCTINCREASE 75 );

18

4

Arbre B et B+ Dès qu’on utilise une commande PRIMARY KEY,

Oracle crée un arbre B+ sur la clé primaire

L’index est stocké dans un segment d’index

On peut organiser la table en arbre B avec l’option ORGANIZATION INDEX sur la clé primaire

plus efficace car évite les accès par adresse

moins valable pour les enregistrements de grande taille

19

Index Bitmap sous Oracle

BITMAP JOIN INDEX:

CREATE BITMAP INDEX sales_cust_gender_bjix

ON sales(customers.cust_gender)

FROM sales, customers

WHERE sales.cust_id = customers.cust_id

LOCAL NOLOGGING COMPUTE STATISTICS;

20

HachageStructure appelée Hash Cluster. Utilisée en deux étapes:

On crée la structure avec tous ses paramètres

On affecte une ou plusieurs tables à la structure

Attention, le hachage dans Oracle n’est pas dynamique

21

Création d’un Hash ClusterCREATE CLUSTER HachEmp (id INT)

SIZE 500 HASHKEYS 500;

La clé de hachage est de type INTEGER ,

Oracle fournit automatiquement une fonction avec de bonnes propriétés,

Nombre de valeurs de la fonction précisé par HASHKEYS,

Taille de chaque bloc estimée par SIZE

22

Affectation d’une colonne à un hash cluster

CREATE TABLE emp (idEmp INT, ... )

CLUSTER HachEmp (idEmp)

Assez délicat à paramétrer

Demande un contrôle régulier

23

Evaluation et optimisation de requêtes

24

5

Algèbre Relationnelle : Implémentation Rappel de l'algèbre

Structures d'indexation et placement

Algorithme de sélection

Algorithme de jointure

2525

Etape du traitement d'une requête

Toute requête SQL est traitée en trois étapes :

1. Analyse et traduction de la requête. On vérifie qu’elle est correcte,

elle est ensuite exprimé sous la forme d’opérations (algèbre relationnel).

2. Optimisation : comment agencer au mieux les opérations ? Quels sont

les algorithmes à utiliser ? Un plan d’exécution est obtenu.

3. Exécution de la requête : le plan d’exécution est compilé et exécuté.

26

Langages de requêtes

Deux langages de requêtes à fondements mathématiques sont à la base de SQL :

Calcul relationnel:

Permet aux utilisateurs de décrire le résultat souhaité ,

Non opérationnel, déclaratif

Algèbre relationnelle:

Plus opérationnel que le calcul relationnel

Permet de représenter les plans d’exécution.

2727

Calcul Relationnel Dérivé de la logique Calcul relationnel de domaine

Requêtes de la forme {X | Formule(X, Y) } X est un vecteur de variables non quantifiées (x1, x2, …) Y est un vecteur de variables quantifiées ((/) y1, y2, …) Chaque variable représente une colonne de table

Ex: {(x1,x2) | y1 (Emp(x1,x2,…,y1) ⋀ y1 = ‘25/10/2004’) }

Calcul relationnel de tuple Une variable correspond à un n-uplet Ex: {(X.empno, X.ename) |

Emp(X) ⋀ X.dateEbauche = ‘25/10/2004’ }

2828

Algèbre relationnelleOpérations de base: Sélection ( ) Sélectionne un sous-ensemble des lignes d’une relation.

Projection ( ) Efface des colonnes d’une relation [et élimine les doubles].

Produit Cartésien ( X ) Permet de combiner deux relations.

Différence ( - ) Elimine les tuples de R1 contenus dans R2

Union ( ) Constitue une relation R avec les tuples de R1 et ceux de R2

2929

Algèbre relationnelleOpérations additionnelles:

Jointure (⋈) Combinaison de produit cartésien et sélection sur colonnes

comparables (=, <, >, ...)

Intersection Constitue une relation R avec les n-uplets appartenant à la fois à

R1 et R2

Chaque opération retournant une relation

=> les opérations peuvent être composées

3030

6

Division C = A / B

C(X) est la division de A (X, Y) par B (Y) ssi C contient tous les n-uplets (x) tels que (y) B, (x, y) A

Equivalent SQL de la division:SELECT F# FROM A JOIN B ON A.P# = B.P#

GROUP BY F#

HAVING COUNT(DISTINCT *) = (SELECT COUNT(DISTINCT *) FROM B);31

F# P#F1 P1

F1 P2F2 P1

F2 P3

P#P1P2

S#F1

Quels sont les fournisseurs de toutes les pièces de B

A

B

C

31

Exécutions des sélections

Cas sans indexFiltrage séquentiel de la relation

Algorithme Select (R, Q) :Foreach block b de R do {

Read (b);

Foreach n-uplet n de b

{ if CheckCondition (n, Q)

then result = result n ;} ;

}3232

Sélection via index Cas avec index

unidimensionnel (Arbre-B, Arbre-B+)

Variante : Cas avec hachage

3333

Utilisation d'index B+-tree Intersection et union de listes d'adresses de n-uplets

Accès aux n-uplets dont les identifiants sont retenus

Vérification du reste du critère

Exemple choix 1: Utilisation de tous les index (JOB = "INGENIEUR") AND (AGE > 30) AND (deptno = 5)

L = J (A1 A2 … Ap) D

Accéder aux n-uplets de L

Exemple choix 2: Choix du meilleur index Accès via l'index (JOB = "INGENIEUR")

Vérification du reste des critères sur les n-uplets résultats

3434

Utilisation d'index Bitmap Détermination des adresses des n-uplets candidats

Très utile pour les agrégats

3535

Exécution des jointures Opération essentielle des SGBDR

Plusieurs méthodes

Sans index : réduire les balayages

Optimiser les comparaisons

Créer dynamiquement des index ou tables de hachage

Avec index Profiter au mieux des index

Contraintes : Réduire I/O et temps CPU

3636

7

Jointure Les mêmes types d’algorithmes peuvent êtres

utilisés pour les autres opérateurs binaires (union, intersect, …)

Algorithmes : Nested Loop Join

Index Join

Sort Merge Join

Hash Join

3737

Optimisation de Requêtes Introduction

Arbres relationnels

Restructuration algébrique

Modèle de coût

Choix du meilleur plan

Conclusion

38

Architecture Type SGBD

ANALYSEUR

META-BASE

CONTROLE

OPTIMISEUR

EXECUTABLE

SYNTAXESEMANTIQUESCHEMA

VUESINTEGRITEAUTORISATIONS

ORDONNANCEMENTELABORATIOND'UN PLAN

EXECUTIONMETHODES D'ACCES

39

Quelles informations utiliser pour l’optimisation ?

Le traitement s'appuie sur les éléments suivants : Le schéma logique de la base: description des tables, des

contraintes d'intégrité, … Le schéma physique de la base: indexes et chemins d'accès,

tailles des blocs, … Des statistiques: taille des tables, des index, distribution des

valeurs Par exemple: R1: 10000 n-uplets (site A) et R2: 200 n-uplets (site B)

=>Pour réaliser R1 ⋈ R2 - il vaut mieux transférer R2

Des statistiques: taux de mises-à-jour, workload Les particularités du système: parallélisme, processeurs

spécialisés, … Des algorithmes: peuvent différer selon les systèmes

40

Etapes de l'optimisation

(1) Obtention d’une représentation canonique (2) Réécriture = transformation par :

simplification ordonnancement des opérations élémentaires

(3) Planning = construction des plans d'exécution candidats choix des algorithmes pour chaque opérateur, calcul du coût de chaque plan, choix du meilleur plan trop de plans possibles, utilisation d’heuristique

Etapes 1 et 2 : indépendantes des données Etape 3 : dépendante des données

41

Arbres relationnels

PRODUIT CARTESIEN

JOINTURE

RESTRICTION

E.JOB "Ingénieur"

PROJECTION

E.SAL, E.JOB

DIFFERENCE

—

B2B1

UNION

B1 B2

U

E

=

=E. DEPTNO D.DEPTNO

E D

E D

E

TRI

E

E.DEPTNO, E.JOB

AGREGAT

E

E.DEPTNO, E.JOB

COUNT(*),AVG(SAL)

42

8

Exemple d'arbre Coût d'exécution:

N projets dont Nm à Metz

M dept dont Mi "Investissement"

K emp

N + Nm*M + Nm*M*K

+ Nm*Mi*K + …

comparaisons de n-uplets

RESULTAT

E.ENAME, P.PNAME

E.AGE

D.NOM

30

" INVEST"

>

=

D.DEPTNO E.DEPTNO=

EMP E

=

P.DEPTNO D.DEPTNO

P.LOC "METZ"

PROJET P

=

DEPT D

43

Typologie des arbres

Arbre linéaire droit Arbre linéaire gauche Arbre ramifié

44

Restructuration algébrique

Problème : suivant l'ordre des opérateurs algébriques dans un

arbre, le coût d'exécution est diffèrent

Pourquoi?

1. le coût des opérateurs varient en fonction du volume des données traitées: plus le nombre de n-uplets des relations traitées est petit, plus les coûts cpu et d'E/S sont minimisés

2. certains opérateurs diminuent le volume des données (restriction, projection, …)

45

Commutativité des Jointures

R S S R

46

Associativité des jointures Il existe environ N! arbre de jointure de N relations

Cas des produits cartésiens

R S

T

S T

R

47

Groupage des Restrictions

Ai = a

Aj = b

Ai = aet

Aj = b

Une cascade de projections/sélections peut se réécrire en une opération de filtrage (un seul scan de données)

48

9

Descente des Projections

Il est possible de descendre les projections, mais les attributs utilisés par la suite doivent être conservés

Ai = a

A1, … Ap

Ai = a

A1, … Ap

Ai,A1,… Ap

49

Descente des restrictions

<==> A1 = vi

A1 = vi

R1 (.. Ai..) R2 (.. Bj..) R1 (.. Ai..)

R2 (.. Bj..)

Les projections/sélections sont des réducteurs importants, d’où l’importance de le pousser vers les feuilles.

50

Règles de Restructuration Commutativité des jointures

Associativité des jointures

Regroupement des restrictions

Descente des projections et restrictions

Semi-commutativité des restrictions et jointures

Semi-distributivité des projections / jointures

Distributivité des restrictions / unions ou différences

Distributivité des projections / unions

51

Heuristique d'Optimisation Appliquer d'abord les opérations réductrices

(restrictions et projections) en les groupant sur chaque relation

1. Dégrouper les restrictions

2. Descendre les restrictions

3. Grouper les restrictions aux feuilles

4. Descendre les projections

Ordre des unions, différences et jointures ?

52

Exemple d'Arbre OptimiséCoût d'exécution réduit

E.ENAME, P.PNAME

E.AGE

D.NOM30

" INVEST"

>

=

E.DEPTNO D.DEPTNO=

DEPT D

Résultat

=

P.DEPTNO E.DEPTNO

P.LOC " METZ"

PROJET P

=

EMP E

P.DEPTNO, P.PNAME E.DEPTNO, E.ENAME

D.DEPTNO

E.DEPTNO, E.ENAME, P.PNAME

RESULTAT

E.ENAME, P.PNAME

E.AGE

D.NOM

30

" INVEST"

>

=

D.DEPTNO E.DEPTNO=

EMP E

=

P.DEPTNO D.DEPTNO

"METZ"

PROJET P

=

DEPT D

53

P.LOC

Ordonnancement des Jointures HEURISTIQUES : Choix des relations de taille minimum

Jointures précalculées d’abord (index)

Semi-jointures (où les projections ne concernent qu’une des relations de la jointure) plus réductrices

ORDONNANCEMENT DES AGREGATS

Permutations difficiles

Profiter des tris des jointures, dédoublement, etc..

Gains importants pour MIN et MAX

54

10

Ordonnancement des jointuresExemple:

Quels clients ont commandé le produit 415 ?

Q1 = ename( idProd=415 (Produit) ⋈ (Commande ⋈ Client))

Hypothèse: 60 clients + 400 commandes

60 ⋈ 400 → 400 puis 1 ⋈ 400 → 10 (clients)

il vaut mieux faire:

Q2 = ename(( idProd=415 (Produit) ⋈ Commande) ⋈ Client)

1 ⋈ 400 → 10 puis 10 ⋈ 60 → 10

55

MODELE DE COUT Facteur de sélectivité S

Proportion de n-uplets du produit cartésien des relations touchées qui satisfont une condition.

Exemple:SELECT *

FROM R1, R2

=> S = 1

SELECT *

FROM R1

WHERE A = valeur

=> S = 1 / CARD(A) avec un modèle uniforme

56

Sélectivité des Restrictions TAILLE ((R)) = s * TAILLE(R) avec:s (A = valeur) = 1 / CARD(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/CARD(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).

57

Sélectivité des Jointures TAILE( R1 ⋈ R2) = p * TAILLE(R1) * TAILLE(R2)

p dépend du type de jointure et de la corrélation des colonnes :

p = 0 si aucun n-uplet n’est joint

p = 1 / MAX(CARD(A),CARD(B)) si distribution uniforme équiprobable des attributs A et B sur un même domaine (col. join.)

p = 1 si produit cartésien

L'algorithme utilisé change radicalement les coûts

linéaire si index,

produit des tailles si boucles imbriquées.

58

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

59

Hoix du meilleur plan

Générateur de

Plans

Arbre d'opérations

Heuristiques

de choix

Plan d'exécution

Optimal

Schéma interne

Plans d'exécution

Stratégie de

Recherche

Bibliothèque de

transformations

Modèle de coût

60

11

Sélectivité minimumRel = liste des relations à joindre ;

p = plus petite relation ;

Tant que Rel non vide {

R = relation de selectivité minimum de Rel ;

p = join(R,p) ;

Relations = Relations - R ; } ;

Return(p) ;

61

ItérateursTous les SGBD s'appuient sur un ensemble d'opérateurs physiques, ou itérateurs. Tous fournissent des interfaces semblables :

open : initialise les tâches de l'opérateur, positionne le curseur avant le premier résultat à fournir;next : retourne l'enregistrement courant et se positionne sur l'enregistrement suivant;close : libère les ressources.

On appelle itérateurs ces opérateurs. Ils sont à la base des plans d'exécution.

62

Plan d’exécutionUn plan d'exécution est un arbre d'itérateurs.

Chaque itérateur consomme une ou deux sources, qui peuvent être soit d'autres itérateurs, soit un fichier d'index ou de données ;

Un itérateur produit un flux de données à la demande, par appels répétés de sa fonction next.

Un itérateur peut appeler les opérations open, next et close sur ses itérateurs sources.

La production à la demande évite d'avoir à stocker des résultats intermédiaires.

63

Itérateur: parcours séquentiel

On lit, bloc par bloc, le fichier.

Quand un bloc est en mémoire, on traite les enregistrements qu'il contient.

Sous forme d'itérateur :

La commande open place le curseur au début du fichier et lit le premier bloc ;

La commande next renvoie l'enregistrement courant, et avance d'un cran ; on lit un nouveau bloc si nécessaire ;

close libère les ressources.

64

Itérateur: traversée d’index et accès direct

Index : l'itérateur prend en entrée une valeur, ou un intervalle de valeurs.

on descend jusqu'à la feuille (open) ;

on renvoie l'adresse courante lors du premier appel de next, on se décale d'un enregistrement dans la feuille courante (éventuellement, on lit le bloc-feuille suivant) ;

Accès direct : s'appuie sur un itérateur qui fournit des adresses d'enregistrement

65

Exemple de plan d’exécutionRequête:

Q1 = deptno(Dept ⋈ sal>50 000 (Emp))

Etapes: Itérateurs de parcours séquentiels; Itérateur de traversée d’index; Itérateur de jointure avec index; Itérateur d’accès direct par adresse; Itérateur de projection

Le plan est exécuté par simple appel (open; next; close) sur la racine (itérateur de projection).

66

12

Rôle des itérateurs

Principes essentiels :

Production à la demande : le serveur n'envoie un enregistrement au client que quand ce dernier le demande ;

Pipelinage : on essaie d'éviter le stockage en mémoire de résultats intermédiaires : le résultat est calculé au fur et à mesure.

Conséquences : temps de réponse minimisé (pour obtenir le premier enregistrement) mais attention aux plans bloquants (ex. plans avec un tri).

67

Tri externe

Le tri (dans table) externe est utilisé,

pour les algorithmes de jointure (sort-merge)

l'élimination des doublons (clause DISTINCT)

pour les opérations de regroupement (GROUP BY)

ORDER BY

Opération très coûteuse sur de grands jeux de données.

68

Tri-fusion Etape 1: Lire une page, la trier, l’écrire.

Un seul buffer utilisé

Coût : une lecture + une écriture du fichier.

Etape suivantes (2, 3, …,):

trois buffers utilisés

Main memory buffers

INPUT 1

INPUT 2

OUTPUT

DiskDisk

69

Tri-fusion A chaque étape, on lit et on

écrit une page dans le fichier

N pages dans le fichier

=> nombre de passes

Coût total:

Idée: Divide and conquer: trier les sous-fichier et fusionner

log2 1N

2 12N Nlog

Fichier d’entrée

1-page runs

2-page runs

4-page runs

8-page runs

PASS 0

PASS 1

PASS 2

PASS 3

9

3,4 6,2 9,4 8,7 5,6 3,1 2

3,4 5,62,6 4,9 7,8 1,3 2

2,3

4,6

4,7

8,9

1,3

5,6 2

2,3

4,4

6,7

8,9

1,2

3,5

6

1,2

2,3

3,4

4,5

6,6

7,8

Tri-fusion général

Pour trier N pages en utilisant B buffers:

Pass 0: utiliser les B buffers. Produire blocs triés

Pass 1,2, …, etc.: fusionner les B-1 blocs.

N B/

B Main memory buffers

INPUT 1

INPUT B-1

OUTPUT

DiskDisk

INPUT 2

. . . . . .. . .

Plus de 3 buffers

71

Coût du tri-fusion général

Nombre de passes:

Coût= 2N * (# Nombre de passes)

Avec 5 buffers, pour trier un fichier de 108 pages:

Pass 0: = 22 sous-fichiers triés de 5 pages (le dernier sous-fichier n’a que 3 pages)

Pass 1: = 6 sous-fichiers de 20 pages chacun (le dernier contient 8 pages seulement)

Pass 2: 2 sous-fichiers, 80 pages et 28 pages

Pass 3: un fichier trié de 108 pages

1 1 log /B N B

108 5/

22 4/

72

13

Principaux algorithmes de jointureJointure sans index

Le plus simple : jointure par boucles imbriquées

Le plus courant : jointure par tri-fusion

Parfois le meilleur : jointure par hachage

Jointure avec index

Avec un index : jointure par boucles indexée.

Avec deux index : on fait comme si on avait un seul index

73

Jointures par boucles imbriquéesS’il n’y a pas d’index, on effectue un parcours séquentiel des blocs de la table R1, puis pour chaque n-uplet de R1, de R2.

Ex: jointure dept et emp dans index

Emp deptno

1 1

2 2

3 5

4 6

5 2

Emp deptno

1 1

2 2

3 5

4 6

5 2

Compta 1

RH 2

Comparaison

Création association

dname deptno

74

Boucles imbriquées : Gestion de la mémoire

Le maximum de mémoire est alloué à la table intérieure de la boucle imbriquée.

Si la table intérieure tient en mémoire: une seule lecture des deux tables suffit.

75

Jointure par tri-fusionPlus efficace que les boucles imbriquées pour de grosses tables

On trie les deux tables sur les colonnes de jointures

On effectue la fusion

C'est l’opération de tri qui coûte cher

Important : on ne peut obtenir de résultat tant que le tri n'est pas fini.

76

Jointure par hachagele plus efficace dans le meilleur des cas

Très rapide quand une des deux tables est petite (n fois la taille de la mémoire avec n petit, <3)

Pas très robuste (efficacité dépend de plusieurs facteurs)

Algorithme en option dans ORACLE. Il est indispensable d'avoir des statistiques pour optimiser (fonction de hachage, nombre de casier, taille des casiers, …)

77

Principe de la jointure par hachageIdée de base:

On hache la plus petite des deux tables en n fragments.

On hache la seconde table, avec la même fonction, en n autres fragments.

On réunit les fragments par paire, et on fait la jointure.

78

14

Jointure avec index sur une tableAvec un index, on utilise les boucles imbriquées indexées.

On balaye la table non indexée

Pour chaque ligne, on utilise l'attribut de jointure pour traverser l'index sur l'autre table.

Avantages :

Très efficace (un parcours, plus des recherches par adresse)

Favorise le temps de réponse et le temps d'exécution

79

Deux tables indexées

On pourrait penser à la solution suivante :

Fusionner les deux index : on obtient des paires d'adresses

Pour chaque paire, aller chercher la ligne A, la ligne B

Problématique car beaucoup d'accès aléatoires (si mauvaise sélectivité, contre-performant d’utiliser l’index)

En pratique :

On se ramène à la jointure avec un index

On prend la petite table comme table extérieure.

80

RésuméQu'est-ce qu'un plan d'exécution ?

C'est un programme combinant des opérateurs physiques (chemins d'accès et traitements de données).

Il a la forme d'un arbre : chaque noeud est un opérateur qui

prend des données en entrée

applique un traitement

produit les données traitées en sortie

81

La phase d'optimisation proprement dite :

Pour une requête, le système a le choix entre plusieurs plans d'exécution.

Ils diffèrent par l'ordre des opérations, les algorithmes, les chemins d'accès.

Pour chaque plan on peut estimer :

le coût de chaque opération

la taille du résultat

Objectif : diminuer le plus vite possible la taille des données manipulées.

82

Laisser le choix au système du plan d’exécution Il y a autant de plans d'exécution que de sous-blocs dans

une requête. Exemple: cherchons tous les produits dont le coût est

inférieur à 500 € commandé par Scott Tiger

SELECT pname

FROM produit P, commande R, Client C

WHERE C.nom = 'Tiger' AND C.prenom= 'Scott'

AND P.idProduit = R.idProduit

AND R.idClient = C.idClient

AND P.prix < 500;

Pas d’imbrication: un bloc => choix lancé au système

83

2ème version (2 blocs) Un niveau d’imbrication sans nécessité

Moins d’optimisations possibles

SELECT pname

FROM produit P, commande R

WHERE P.idProduit = R.idProduit

AND P.prix < 500

AND R.idClient IN (

SELECT C.idClient FROM client C

WHERE C.nom = 'Tiger' AND C.prenom= 'Scott'

);

84

15

3ème version (2 blocs) Utilisation de exists

SELECT pname

FROM produit P, commande R

WHERE P.idProduit = R.idProduit

AND P.prix < 500

AND EXISTS(

SELECT C.idClient FROM client C

WHERE C.nom = 'Tiger' AND C.prenom= 'Scott'

AND C.idClient = R.idClient

);

85

4ème version (3 blocs) 2 niveau d’imbrications

On force le plan d’exécution: mauvaise solution

SELECT pname FROM produit

WHERE prix < 500

AND idProduit IN

(

SELECT idProduit FROM commande

WHERE idClient IN (

SELECT idClient FROM client

WHERE nom = 'Tiger' AND prenom= 'Scott'

)

);

On parcours tous les produits dont le prix est inférieur à 500 €

Pour chaque produit, on cherche les client ayant commandé le produit: pas d’index disponible

Ensuite pour chaque client on regarde si c’est Scott Tiger

86

Optimiseur Oracle L’optimiseur ORACLE suit une approche classique :

Génération de plusieurs plans d’exécution.

Estimation du coût de chaque plan généré.

Choix du meilleur et exécution.

Tout ceci est automatique, mais il est possible d’influer, voire de forcer le plan d’exécution.

87

Estimation du coût d’un plan d’exécution

Beaucoup de paramètres entrent dans l’estimation du coût :

Les chemins d’accès disponibles.

Les opérations physiques de traitement des résultats intermédiaires.

Des statistiques sur les tables concernées (taille, sélectivité). Les statistiques sont calculées par appel explicite à l’outil ANALYZE.

Les ressources disponibles.

88

Optimiseur basé sur des règles

Oracle mode RULE

Priorité des règles

89

Rang Chemin d'accès

1 Sélection par ROWID

2 Sélection d'une ligne par jointure dans une organisation parindex groupant ou hachage hétérogène (CLUSTER)

3 Sélection d'une ligne par hachage sur clé candidate(PRIMARY ou UNIQUE)

4 Sélection d'une ligne par clé candidate

5 Jointure par une organisation par index groupant ouhachage hétérogène (CLUSTER)

6 Sélection par égalité sur clé de hachage (HASH CLUSTER)

7 Sélection par égalité sur clé d'index groupant (CLUSTER)

8 Sélection par égalité sur clé composée

9 Sélection par égalité sur clé simple d'index secondaire

10 Sélection par intervalle borné sur clé indexée

11 Sélection par intervalle non borné sur clé indexée

12 Tri-fusion

13 MAX ou MIN d'une colonne indexée

14 ORDER BY sur colonne indexée

15 Balayage

89

Paramètres optimiseurPrincipaux paramètres :

OPTIMIZER_MODE (RULE, CHOOSE, FIRST_ROW, ALL_ROWS).

SORT_AREA_SIZE (taille de la zone de tri).

HASH_AREA_SIZE (taille de la zone de hachage).

HASH_JOIN_ENABLED considère les jointures par hachage.

90

16

Création de statistiques Utiliser les fonctions de DBMS_STATS

Calcul de la taille et du nombre de lignes :ANALYZE TABLE Film COMPUTE STATISTICS

FOR TABLE;

Analyse des index :ANALYZE TABLE Film COMPUTE STATISTICS

FOR ALL INDEX;

Analyse de la distribution des valeurs :ANALYSE TABLE Film COMPUTE STATISTICS

FOR COLUMNS titre, genre;

91

Algorithme de jointure sous OracleORACLE peut utilise trois algorithmes de jointures :

Boucles imbriquées quand il y a au moins un index.

Opération NESTED LOOP.

Tri/fusion quand il n’y a pas d’index.

Opération SORT et MERGE.

Jointure par hachage.

Opération HASH JOIN

92

L’outil EXPLAINL’outil EXPLAIN donne le plan d’exécution d’une requête. La description comprend :

Le chemin d’accès utilisé.

Les opérations physiques (tri, fusion, intersection,...).

L’ordre des opérations.

Il est représentable par un arbre.

93

EXPLAIN: exempleEXPLAIN PLAN

SET STATEMENT_ID=’SelSansInd’ FOR

SELECT * FROM Produit

WHERE prix = 500;

Le résultat de EXPLAIN :

0 SELECT STATEMENT

1 TABLE ACCESS FULL PRODUIT

94

EXPLAIN: exempleEXPLAIN PLAN

SET STATEMENT_ID=’SelInd’ FOR

SELECT * FROM Produit

WHERE idProduit = 15;

Le résultat de EXPLAIN :

0 SELECT STATEMENT

1 TABLE ACCESS BY ROWID PRODUIT

2 INDEX UNIQUE SCAN IDX-

PRODUIT-ID95

EXPLAIN: exempleEXPLAIN PLAN SET

STATEMENT_ID=’JoinSelIndex’ FOR

SELECT R.idProduit

FROM Client C, Commande R

WHERE R.idClient = C.idClient

AND nom = ’Scott’;

Le résultat de EXPLAIN :

0 SELECT STATEMENT

1 NESTED LOOPS

2 TABLE ACCESS BY ROWID CLIENT

3 INDEX RANGE SCAN IDX-NOM

4 TABLE ACCESS BY ROWID COMMANDE

5 INDEX RANGE SCAN IDX-COMMANDE

96

17

Analyse des coûtsAvec l’utilitaire TKPROF, on obtient :

Le coût CPU

Le nombre d’entrées/sorties physiques

Le nombre d’entrées/sorties en mémoire

Le nombre de tris

…

97