1 Motivation et introduction 5 Les SGBD 2

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données 4. IMPLEMENTATION DES STRUCTURES DE DONNEES 2 e partie Version 2 - Janvier 2012 Modifié le 10 avril 2013 Support du chapitre 4, Implémentation des structures de données de l'ouvrage Bases de données, J-L Hainaut, Dunod 2012. azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 1

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données 4. IMPLEMENTATION DES STRUCTURES DE DONNEES Contenu 4. 1 Introduction 4. 2 Les mémoires externes 4. 3 Organisation d'un espace de stockage 4. 4 Traitement séquentiel d'un fichier 4. 5 Les index 4. 6 Organisation séquentielle indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation (clustering) 4. 10 Un exemple : SQL Server de Microsoft azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 2

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 5 Les index azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 3

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 5 Les index Index structure technique associée à un fichier (une table par exemple) qui permet un accès sélectif et rapide aux enregistrements (ou lignes) de ce fichier qui possèdent des valeurs déterminées de certains champs. Ces champs constituent la clé de l'index. En SQL, l'extraction et la modification de données ne nécessitent pas la connaissance des index. On peut associer un nombre quelconque d'index à un fichier (table). Un index est dit identifiant si un identifiant a été déclaré sur la clé de cet index. azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 4

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 5 Les index améliorent les performances de certaines opérations sur les données mais induisent des coûts de stockage et de gestion. La requête select NOM, LOCALITE from CLIENT where NCLI = ’C 400’ • s'exécutera en 15 s sans index sur NCLI • et en 0. 025 s avec un index sur NCLI (600 fois plus rapidement !). Mais, pas de miracle : tout avantage a un coût ! Ici, le volume et le coût de gestion de l'index. La modification de lignes de la table CLIENT implique des opérations de modification de l'index sur NCLI. azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 5

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 5 Les index Un index convertit une valeur de la clé en l'ensemble des adresses des enregistrements qui possèdent cette valeur. Formellement, un index défini sur une colonne K de la table T matérialise une fonction f qui renvoie pour chaque valeur k de K un ensemble A d'adresses de lignes dans T. A = f(k) Si la colonne K est identifiante, A contient au plus une adresse. Les différentes technologies d'index se distinguent par la manière dont la fonction f est implémentée : table de conversion ou calcul. azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 6

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 5 Les index Deux classes d'index 1. les index primaires 2. les index secondaires azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 7

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 5 Les index Index primaire 1. le plus souvent identifiant 2. un seul index primaire par fichier 3. la structure du fichier dépend fortement de la présence d'un index primaire 4. difficile d'ajouter et de supprimer un index primaire au vol 5. faible volume, très bonnes performances Index secondaire 1. identifiant ou non identifiant 2. de 0 à N index secondaires par fichier 3. la structure du fichier est indépendante de la présence d'un index secondaire 4. facile d'ajouter et de supprimer un index secondaire au vol 5. volume plus important, moins bonnes performances 6. Certains SGBD n'offrent que des index secondaires azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 8

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 5 Les index Principales technologies des index primaires 1. organisation séquentielle indexée 2. organisation calculée azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 9

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 10

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Principes Un fichier séquentiel indexé de clé K est constitué • d'un fichier de base séquentiel ordonné sur le(s) champ(s) K • d'un index sur K azerty I. Concepts des bases de données fichier de base ordonné sur K Bases de données J-L Hainaut 2012 11

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Principes L’index matérialise une fonction f qui transforme une valeur k de la clé K en une adresse A. La fonction f est implémentée sous la forme d'une table de correspondance fournissant une adresse (celle d'une page) pour chaque valeur de K. azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 12

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Principes à démontrer ! L'organisation séquentielle indexée d'un fichier permet 1. la lecture séquentielle ordonnée des enregistrements du fichier de base 2. la lecture séquentielle ordonnée des valeurs de K (via l'index) 3. l'accès rapide à l'enregistrement tel que K = k 4. l'accès rapide aux enregistrements tel que K [k 1, k 2] 5. l'insertion rapide d'un enregistrement de clé K 6. la modification rapide de la valeur de K d'un enregistrement 7. la suppression rapide d'un enregistrement Cette organisation s'inspire de structure d'arbre dite B-tree (balanced tree) azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 13

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Principes Hypothèses simplificatrices 1. La clé K est un identifiant du fichier 2. Le fichier contient des enregistrements d'un seul type 3. Les enregistrements sont de taille fixe 4. La taille des enregistrements est inférieure à la moitié de celle des pages 5. Un enregistrement est entièrement compris dans une page azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 14

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Principes fichier de base index P 650 S 712 F 400 H 991 J 079 P 650 S 712 azerty I. Concepts des bases de données B 332 C 003 D 063 F 400 G 110 H 736 H 991 I 452 J 079 J 823 K 729 M 345 P 650 S 712 B 062 B 112 B 332 GOFFIN HANSENNE MONTI Namur Poitiers Genève B 2 C 1 B 2 B 512 C 003 GILLET AVRON Namur Poitiers B 1 C 123 B 512 C 400 C 003 D 063 C 123 MERCIER GILLET FERARD AVRON MERCIER Namur Poitiers Toulouse Genève C 1 B 2 F 010 F 011 F 400 TOUSSAINT PONCELET JACOB Poitiers Bruxelles C 1 B 2 C 2 F 375 G 110 BERNIER STEVEN Paris London C 1 B 2 H 109 H 736 DUPUIS ARNOULD Lausanne Paris C 2 B 1 Observations 1. les enregistrements de chaque page de base sont ordonnés selon K 2. une page de base est caractérisée par sa plus grande valeur de K. 3. les pages de base sont ordonnées selon K 4. l'index comprend n niveaux (n 1) 5. chaque page d'index comprend des entrées d'index 6. chaque niveau d'index jouit des propriétés 1à 3 K 111 K 729 VANBIST NEUMAN Lille Toulouse B 1 7. chaque entrée du niveau n de l'index comprend 1 valeur ki de K et 1 pointeur vers la page de base dont la plus grande valeur de K = ki L 422 M 162 M 345 FRANK MERCIER NOEL Namur Toulouse Liège C 1 C 2 B 1 8. chaque entrée du niveau m (m < n) de l'index comprend 1 valeur ki de K et 1 pointeur vers la page d'index de niveau m+1 dont la plus grande valeur de K = ki N 227 P 650 MARTIN ANTOINE Bruxelles Poitiers C 1 B 2 9. le 1 er niveau de l'index comprend 1 page S 127 S 712 VANDERKA GUILLAUME Namur Paris C 1 B 1 . . . 10. certaines pages ne sont pas pleines 11. la position d'un enregistrement est fonction de sa valeur de K Bases de données J-L Hainaut 2012 15

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Accès par clé (K = 'H 109') H 109 P 650 K = 'H 109' P 650 S 712 niveau 1 H 109 H 991 F 400 H 991 J 079 P 650 niveau 2 H 109 H 736 G 110 H 736 H 991 H 109 se trouve dans cette page ou nulle part ailleurs H 109 H 736 niveau 3 DUPUIS ARNOULD Lausanne Paris C 2 B 1 page de base Observation 1 : la recherche par clé nécessite la lecture de n+1 pages, quel que soit l'enregistrement. Observation 2 : si la recherche échoue, tout n'est pas perdu : elle indique la page dans laquelle l'enregistrement aurait dû se trouver. azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 16

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Accès par clé (K = k) k Kmax ? non oui i : = 1; p : = p 1 I : = 1 re entrée de la page [p] tel que I. K k R: = 1 er enreg. de la page [p] tel que R. K k non p : = I. ptr i=n? non oui R. K = k ? l'enregistrement recherché est absent du fichier oui R est l'enregistrement recherché i : = i + 1 azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 17

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Modes d'accès Les trois modes d'accès aux enregistrements ponctuel selon K intervalle selon K select * from CLIENT where NCLI = 'C 400' select * from CLIENT where NCLI like 'C%' azerty I. Concepts des bases de données séquentiel selon K select * from CLIENT order by NCLI Bases de données J-L Hainaut 2012 18

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Modifications Insertion d'un enregistrement 1. On identifie la page d'insertion 2. On tente d'y insérer l'enregistrement : cas 1 : la page contient de l'espace libre cas 2 : la page ne contient pas d'espace libre azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 19

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Modifications Insertion d'un enregistrement (K = H 555) K = 'H 555' P 650 S 712 F 400 H 991 J 079 P 650 G 110 H 736 H 991 - H 555 est absent - la page n'est pas pleine H 109 H 736 DUPUIS ARNOULD Lausanne Paris C 2 B 1 P 650 S 712 azerty I. Concepts des bases de données F 400 H 991 J 079 P 650 G 110 H 736 H 991 H 555 est inséré dans la page H 109 H 555 H 736 DUPUIS MARTINEZ ARNOULD Lausanne Madrid Paris Bases de données J-L Hainaut 2012 C 2 B 1 20

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Modifications Insertion d'un enregistrement (K = C 805) K = 'C 805' P 650 S 712 F 400 H 991 J 079 P 650 B 332 C 003 D 063 - C 805 est absent - la page est pleine C 123 C 400 D 063 MERCIER FERARD MERCIER Namur Poitiers Toulouse C 1 B 2 P 650 S 712 F 400 H 991 J 079 P 650 B 332 C 003 C 400 D 063 - la page est dédoublée nouvelle page C 123 C 400 MERCIER FERARD Namur Poitiers C 1 B 2 C 805 D 063 SMITH MERCIER Londres Toulouse B 1 0, 5 ancienne page azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 21

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Modifications Insertion d'un enregistrement La nouvelle page est-elle juste devant la page pleine ? Sauf miracle, non ! Où a-t-on trouvé cette page ? - parmi les pages libres réservées à la fin du fichier - parmi les pages préalablement libérées suite à la suppression d'enregistrements. En règle générale, la nouvelle page est physiquement plus ou moins éloignée de sa page soeur. azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 22

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Modifications Séquence physique et séquence logique 0 1 . . . 99 100 101 102 103 99. 997 99. 998 99. 999 . . . séquence physique des pages dans le fichier séquence logique de lecture Insertion dans la page 101, qui devient 101 (localisée en 99. 997) + 102 (localisée en 101): 0 1 . . . 99 100 101 102 103 99 100 102 103 104 99. 997 99. 998 99. 999 séquence physique . . . 101 séquence logique Les pages se suivent logiquement mais ne se suivent plus physiquement = ruptures physiques. azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 23

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Modifications Comment reconstituer la séquence logique ? Trois techniques 1. Les pages de bases sont chaînées via l'ajout d'un pointeur référençant la page de base suivante. Le parcours de cette chaîne livre les pages de base dans bon ordre. 2. Les pages du dernier niveau d'index sont chaînées via l'ajout d'un pointeur référençant la page d'index suivante (VSAM d'IBM). L'utilisation des pointeurs de ces pages parcourues selon cette chaîne livre les pages de base dans bon ordre. 3. L'accès aux pages de base s'effectue via le parcours de l'index (visite des noeuds en profondeur d'abord). azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 24

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Modifications Synthèse: insertion d'un enregistrement (K = k) Procédure 1. On recherche la page [p] dans laquelle l'enregistrement K = k devrait être stocké. 2. Si la page [p] n'est pas pleine, on y insère l'enregistrement. 3. Si la page [p] est pleine, on procède à son éclatement : 3. 1 on insère devant [p] une page vide [p'] (logiquement, pas physiquement) 3. 2 on répartit la charge de [p] entre les pages [p'] et [p] 3. 3 on insère l'enregistrement dans l'une de ces deux pages 3. 4 on insère une entrée relative à la page [p'] dans le niveau n de l'index Observations 1. le taux d'occupation de chaque page reste 0, 5 (sauf, éventuellement la dernière) 2. les pages logiquement consécutives ne le sont plus nécessairement physiquement 3. l'insertion d'une entrée d'index obéit à la même procédure azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 25

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Modifications Remarque: insertion en fin de fichier Scénario : les enregistrements sont insérés par valeurs croissantes de la clé K (insertion ordonnée) Dans ce cas, la gestion des pages pleines peut être différente de la procédure décrite plus haut. Exemple : Lors d'une insertion ordonnée, lorsque qu'une page (la dernière du fichier de base) est pleine, une page suivante lui est associée, qui contiendra les enregistrements suivants. Le taux d'occupation n'est plus de 50% mais peut être plus élevé : 90% ou même 100% (cf. Oracle, malgré l'appellation maladroite 90 -10 split) azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 26

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Modifications Suppression d'un enregistrement 1. On identifie la page de l'enregistrement à supprimer 2. On l'efface de sa page 3. On réorganise la page si nécessaire : cas 1 : la page garde un taux d'occupation 0, 5 cas 2 : le taux d'occupation de la page tombe sous 0, 5 cas 2. 1 : redistribution des enregistrements avec une page voisine cas 2. 2 : fusion de la page avec une page voisine azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 27

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Modifications Suppression d'un enregistrement (K = 'C 400') K = 'C 400' P 650 S 712 F 400 H 991 J 079 P 650 B 332 C 003 D 063 C 123 C 400 D 063 P 650 S 712 azerty I. Concepts des bases de données F 400 H 991 J 079 P 650 B 332 C 003 D 063 MERCIER FERARD MERCIER Namur Poitiers Toulouse C 1 B 2 Namur Toulouse C 1 0, 5 suppression C 123 D 063 MERCIER Bases de données J-L Hainaut 2012 0, 5 28

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Modifications Suppression d'un enregistrement (K = 'B 512') K = 'B 512' P 650 S 712 F 400 H 991 J 079 P 650 B 332 C 003 D 063 F 400 P 650 S 712 azerty I. Concepts des bases de données F 400 H 991 J 079 P 650 B 110 C 003 D 063 F 400 B 062 B 110 B 332 GOFFIN HANSENNE MONTI Namur Poitiers Genève B 2 C 1 B 512 C 003 GILLET AVRON Namur Poitiers B 1 < 0, 5 suppression puis répartition B 062 B 110 GOFFIN HANSENNE Namur Poitiers B 2 C 1 0, 5 B 332 C 003 MONTI AVRON Genève Poitiers B 1 0, 5 Bases de données J-L Hainaut 2012 29

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Modifications Suppression d'un enregistrement (K = 'H 109') K = 'H 109' P 650 S 712 F 400 H 991 J 079 P 650 G 110 H 736 H 991 F 375 G 110 BERNIER STEVEN Paris London C 1 B 2 0, 5 H 109 H 736 DUPUIS ARNOULD Lausanne Paris C 2 B 1 < 0, 5 suppression puis fusion 0, 5 P 650 S 712 azerty I. Concepts des bases de données F 400 H 991 J 079 P 650 H 736 H 991 libre F 375 G 110 H 736 BERNIER STEVEN ARNOULD Paris London Paris Bases de données J-L Hainaut 2012 C 1 B 2 B 1 0, 5 30

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Modifications Synthèse: suppression d'un enregistrement Procédure 1. On recherche la page [p] de l'enregistrement. 2. On efface l'enregistrement de la page [p]. 3. Si le taux d'occupation de la page [p] est < 0, 5 : 3. 1 on rééquilibre le contenu de [p] avec celui de la page précédente [p'] 3. 2 si le taux d'occupation des 2 pages est 0, 5, on corrige l'entrée d'index de niveau n relative à la 1 re page 3. 2 si le taux d'occupation d'une des deux pages est < 0, 5, on les fusionne 3. 3. 1 on transfère le contenu de la page [p'] vers [p] 3. 3. 2 on abandonne la page [p'], qui devient libre 3. 3. 3 on supprime l'entrée de [p'] dans le niveau n de l'index Observations 1. le taux d'occupation de chaque page reste 0, 5 2. on produit des pages vides réutilisables lors de l'insertion d'enregistrements 3. la suppression d'une entrée d'index obéit à la même procédure azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 31

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Modifications Modification de la valeur de K (k 1 k 2) d'un enregistrement Procédure 1. On supprime l'enregistrement K = k 1. 2. On crée un enregistrement K = k 2 de même contenu. Modification d'un champ extérieur à K : simple remplacement dans la page. azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 32

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances Caractéristiques et performances 1. Calcul des volumes 2. Calcul des temps de lecture 3. Calcul des temps de modification 4. Estimation des taux azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 33

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (volumes) Volume d'un fichier séquentiel indexé Les données 1. Nombre d'enregistrements : Nr 2. Taille des enregistrements : Lr 3. Taille de la clé K : Lk 4. Taille d'une entrée d'index (Lk + taille pointeur de page) : Li 5. Taux moyen d'occupation des pages de base : tb 6. Taux moyen d'occupation des pages d'index : ti Les grandeurs calculées 1. Taille du fichier (en pages) : Np 2. Taille du fichier de base : Npb 3. Taille de l'index : Npi 4. Nombre de niveaux d'index : n 5. Nombre d'entrée d'index au niveau m : Ni(m) 6. Nombre de pages d'index au niveau m : Npi(m) azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 34

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (volumes) Volume d'un fichier séquentiel indexé On a bien sûr : Np = Npb + Npi Fichier de base : calcul de Npb Capacité d'une page de base Mrpp = Lp / Lr Contenu moyen d'une page de base Nombre de pages de base Nrpp = tb x Mrpp Npb = Nr / Nrpp pages vides ignorées azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 35

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (volumes) Volume d'un fichier séquentiel indexé Index : calcul détaillé de Npi On a : Npi = Sm n Npi(m) Capacité d'une page d'index Contenu moyen d'une page de base Mipp = Lp / Li Nipp = ti x Mipp Taille du niveau n Nombre d'entrée de l'index Nombre de pages Ni(n) = Npb Npi(n) = Ni(n) / Nipp Taille d'un niveau m < n Nombre d'entrée de l'index Nombre de pages On atteint le niveau m = 1 lorsque Ni(m) = Npi(m+1) Npi(m) = Ni(m) / Nipp Npi(m) = 1 On en déduit le nombre de niveaux n azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 36

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (volumes) Volume d'un fichier séquentiel indexé Index : calculs simplifiés Evaluation de n (base quelconque) n = log(Npb) / log(Nipp) Evaluation de Npi En général Npi(n) >> Sm < n Npi(m) Donc Npi(n) = Npb / Nipp azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 37

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (volumes) Volume d'un fichier séquentiel indexé Pour les concepteurs pressés et pas trop regardants : On connait Nr, Li, tb, ti, Lp 1. Taille du fichier de base Npb = Nr / tb x Lp / Lr 2. Taille de l'index Npi(n) = Npb / (ti x Lp / Li ) 3. Nbre de niveaux d'index n = log(Npb) / log(ti x Lp / Li ) azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 Eviter de mémoriser ces formules ! 38

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (temps de lecture) Temps de lecture d'un fichier séquentiel indexé Trois opérations 1. Lecture séquentielle 2. Lecture par index d'un enregistrement 3. Lecture par index des enregistrements d'un intervalle de K Hypothèses réalistes 1. Disque partagé 2. Lecture anticipée d'une piste azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 39

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (temps de lecture) Temps de lecture d'un fichier séquentiel indexé 1. Lecture séquentielle selon K Préliminaire : coût du phénomène de ruptures n° physique 0 1 n° séq. logique 0 1 . . . 99 100 101 102 103 0, 184 99. 997 99. 998 99. 999 . . . 0, 184 éclatement de la page 101 en 101 (nouvelle) et 102 (ancienne 101) 0 1 . . . 99 100 101 102 103 99 100 102 103 104 0, 184 99. 997 99. 998 99. 999 . . . 101 0, 184 0 Pourquoi 0 ? 12, 3 azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 40

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (temps de lecture) Temps de lecture d'un fichier séquentiel indexé 1. Lecture séquentielle selon K Analyse 1. Lecture des pages du fichier de base selon leur séquence physique ne marche pas, car ruptures de séquence (séquence physique séquence logique) 2. On accède aux pages de base à partir de l'index (tout ou dernier niveau avec chaînage), qui est ordonné selon K 3. Temps de lecture = temps de parcours de l'index (ou dernier niveau) + temps d'accès aux pages de base 4 Mais, temps de parcours de l'index en général négligeable azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 41

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (temps de lecture) Temps de lecture d'un fichier séquentiel indexé 1. Lecture séquentielle selon K Calcul sans rupture de séquence On a déterminé : tlsf = tlsf 2 = Npb x tls 1 Calcul avec ruptures de séquence Soit pr la proportion de pages en rupture de séquence On a : tlsf = (1 - pr) x Npb x tls 1 + pr x Npb x tla 1 Exemple Npb = 250. 000; pr = 0, 2; Sans rupture : tlsf = 46 s Avec ruptures : tlsf = 652 s azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 42

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (temps de lecture) Temps de lecture d'un fichier séquentiel indexé 2. Lecture ponctuelle selon K Analyse 1. Examen naïf : on accède à 1 page pour chaque niveau d'index + 1 page de base 2. En pratique, on charge les premiers niveaux d'index dans le tampon pour éviter les accès au disque azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 43

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (temps de lecture) Temps de lecture d'un fichier séquentiel indexé 2. Lecture ponctuelle selon K Calcul naïf : tlk 1 = tla 1 x (n + 1) + 1 page pour tout le reste Calcul pratique Si on maintient les m premiers niveaux d'index dans le tampon : tlk 1 = tla 1 x (n-m+1) Le nombre de pages du tampon dédiées au processus est de Sj m Npi(j) + 1 Exemple Npb = 250. 000; n = 3; Npi(1) = 1; Npi(2) = 46; Npi(3) = 3. 361; Tampon de 1 page: tlk 1 = 12, 3 X 4, soit 49, 2 ms Tampon de 1 + 1 = 2 pages : tlk 1 = 12, 3 X 3, soit 36, 9 ms Tampon de 1 + 46 + 1 = 48 pages : tlk 1 = 12, 3 X 2, soit 24, 6 ms Tampon de 1 + 46 + 3. 361 + 1 = 3 409 pages : tlk 1 = 12, 3 X 1, soit 12, 3 ms les performances ont un coût azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 44

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (temps de lecture) Temps de lecture d'un fichier séquentiel indexé 3. Lecture par intervalle [k 1, k 2] selon K Analyse 1. Intervalle : NCLI >= 'K 111'; NCLI >= 'K 000' and NCLI <= 'K 999'; NCLI between 'K 000' and 'K 999'; NCLI like 'K%' 2. L'index est utilisé en deux temps : 2. 1 accès par l'index NCLI = k 1 (ou, plus généralement 1 er enregistrement qui satisfait la condition NCLI >= k 1) 2. 2 lecture séquentielle selon l'index tant que K <= k 2 azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 45

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (temps de lecture) Temps de lecture d'un fichier séquentiel indexé 3. Lecture par intervalle [k 1, k 2] selon K Nombre d'enregistrements et de pages dans l'intervalle • Probabilité qu'un enregistrement tombe dans l'intervalle : pik • Nombre d'enregistrements dans l'intervalle : nrik = pik x Nr • Ces enregistrements occupent : npik = (nrik - 1)/ Nrpp + 0, 5 + 1 pages consécutives Calcul du temps de lecture • séquence courte : tlik = tla 1 + (npik - 1) x ttr • séquence plus longue (lecture anticipée) : tlik = npik x tls 1 • séquence plus longue avec ruptures : voir texte azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 46

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (temps de modification) Temps de modification des données 1. Insertion d'un enregistrement Soit tsplit la proportion des insertions qui ont provoqué un éclatement tins = tl 1 k + (1 + 3 x tsplit) x tla 1 • lire page vide • recopier nouvelle page • maj de l'index • recopier page courante • accès à la page de base azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 47

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (temps de modification) Temps de modification des données 2. Suppression d'un enregistrement Soit tbal la proportion des suppressions qui ont provoqué un rééquilibrage Soit tmerge la proportion des suppressions qui ont provoqué une fusion tdel = (1 + 3 x tbal + 3 x tmerge) x tla 1 • lire page voisine • libérer page vide • maj de l'index • lire page voisine • recopier page voisine • maj de l'index • recopier page courante azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 48

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (temps de modification) Temps de modification des données 3. Modifier un enregistrement = une suppression + une insertion azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 49

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation des taux d'occupation Les calculs dépendent de 6 taux : • tb et ti (mais phénomène identique = t) • pr • tsplit • tbal • tmerge Comment les évaluer ? 1. Analytiquement (processus stochastiques) : complexe 2. Par simulation : plus simple, intuitif mais réclame un peu de programmation azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 50

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation des taux d'occupation Paramètres d'une simulation 1. Nombre initial de pages : Np 0 2. Taille des pages : Mrpp [en nombre d'enregistrements] 3. Taux d'occupation initial t 0 (ou nombre initial d'enregistrements par page Nrpp 0 = t 0 x Mrpp ) 4. Proportion d'insertions tins parmi des opérations insert-delete (ou %ins de 0 à 100); 100% = insertions seulement; 0% = suppressions seulement 5. Nombre d'opérations de la simulation Nop 6. Nombre d'exécutions Nsim (sous la forme de nombre de pas Npas et nombre d'opérations par pas Nopp) azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 51

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation des taux d'occupation Simulateur • tableau d'entiers PAGE(Npmax); PAGE(I) = nbre d'enregistrements de la page I • on initialise les Np 0 premières pages selon Nrpp 0 • on choisit une opération au hasard selon %ins • on choisit un enregistrement R au hasard (il se trouve dans la page P) • on simule l'opération sur R en modifiant PAGE(P) et ses voisines selon l'opération choisie • à la fin de chaque pas, on mémorise les statistiques • à la fin des Nsim exécutions, on calcule les moyennes des statistiques et on les sort dans un fichier de résultats Simulateur : ISAM-auto. dbm azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 52

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation des taux d'occupation Résultats d'une simulation (moyenne des Nsim exécutions) 1. Step : numéro du pas courant (de 0 à Npas) 2. Nop : nombre d'opérations exécutées 3. Nins : nombre d'insertions exécutées 4. Ndel : nombre de suppressions exécutées 5. Np : nombre actuel de pages 6. Nr : nombre actuel d'enregistrements 7. tocc : taux d'occupation actuel 8. Nsplit : nombre d'insertions avec éclatement 9. Nbal : nombre de suppressions avec rééquilibrage 10. Nmerge : nombre de suppressions avec fusion 11. Nerror : nombre d'erreurs (doit être 0) azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 53

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation des taux d'occupation Fichier de paramètres pour 3 simulations 'Simulations pour %ins = 70, 80, 90 'Np 0, Mrpp, Nrpp 0, Taux. Ins, Nbre. Pas, Nbre. Op. Pas, Nbre. Simul step Np Nr tocc Nsplit Nbal Nmerge 5000, 40, 28, 70, 2000, 5 5000, 40, 28, 80, 2000, 5 5000, 40, 28, 90, 2000, 5 simulation 1 résultats demandés ' commentaires azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 54

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation des taux d'occupation Résultats de la simulation 1 Start time = 01/12/2011 09: 38: 44 End time = 01/12/2011 09: 44: 58 STEP 0 1 2 3 4 5 6 7. . . Np 5000 5000 5001 Nr 140000 140794 141608 142430 143239 144017 144834 145673 tocc 0, 70397 0, 7080137 0, 7121235 0, 7161673 0, 7200552 0, 724112 0, 7282786 Nsplit 0 0 0, 2 0, 4 0, 6 Nbal 0 0 0 0, 2 Nmerge 0 0 0 0 197 198 199 200 10571 10597 10629 10656 297375 298191 298985 299797 0, 7033097 0, 7034819 0, 7032048 0, 7033539 5929, 6 5957, 4 5990, 6 6019, 6 3340, 2 3356, 8 3373, 8 3388, 6 359 360, 4 361, 2 363, 6 azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 55

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation de t Comment évolue t ? 1. En fonction du nombre d'opérations nop depuis la création du fichier. Durée de la simulation : nop = Nop. 2. En fonction de la taille des pages Mrpp [en nombre d'enregistrements] 3. En fonction du taux d'occupation t 0 à la création du fichier (Nr 0 = x ) 4. En fonction du % d'insertions %ins [0 -100] dans les opérations insert-delete (100 = insertions seulement; 0 = suppressions seulement) 5. Ne dépend pas de la taille du fichier (Np) mais de nop / Nr 0 azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 56

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation de t 1 pas = 1, 25 x Nr 0 / 100 Np 0 = 5 000; Mrpp = 40; t 0 = 0, 8; %ins = 50 -100; Nop = 400 000 azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 57

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation de t - les résultats sont-ils extrapolables ? oui : [k x Np 0, Mrpp, Nrpp 0, %ins, k x Nop] = [Np 0, Mrpp, Nrpp 0, %ins, Nop] [ Np 0 = 1 000; Mrpp = 40; t 0 = 0, 8; %ins = 90; Nop = 100 000] = [Np 0 = 5 000; Mrpp = 40; t 0 = 0, 8; %ins = 90; Nop = 500 000] azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 58

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation de t - Comportement asymptotique Que devient t lorsqu'on laisse fonctionner le fichier pendant très longtemps ? Il tend vers une constante t = q 1 pas = 5 x Nr 0 / 100 Np 0 = 5 000; Mrpp = 40; t 0 = 0, 8; %ins = 50 -100; Nop = 1 600 000 azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 59

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation de t - Comportement asymptotique La valeur asymptotique t = q dépend de %ins et Mrpp q = f(%ins, Mrpp) coût : 700 h de calcul azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 60

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation de t - Les abaques opérationnels La valeur asymptotique est un concept théorique : on ne laisse jamais un fichier fonctionner aussi longtemps sans réorganisation (pr > 0, 5 !!!). Seule la période de prime jeunesse (A, B, C) est utile. Simulations courtes. [Mrpp=40; t 0 = 0, 8; %ins = 90%] azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 61

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation de t - Les abaques opérationnels Abaques opérationnels : nous renseignent sur la période utile des fichiers. • Obtenus à partir d'une série de simulations. • Plus faciles à utiliser que des tableaux de chiffres • Permettent des interpolations Trois familles : • selon Mrpp • selon %ins • selon t 0 azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 62

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation de t - Les abaques opérationnels Abaques selon Mrpp 1 pas = Nr 0 / 100 t = f(%ins=55; t 0=0, 7; Mrpp=(10 -100); pas) azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 63

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation de t - Les abaques opérationnels Abaques selon %ins 0, 799 1 pas = Nr 0 / 100 22 azerty I. Concepts des bases de données t = f(%ins=50 -100; t 0=0, 7; Mrpp=20; pas) Bases de données J-L Hainaut 2012 64

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation de t - Les abaques opérationnels Abaques selon t 0 1 pas = Nr 0 / 100 t = f(%ins=55; t 0=0, 6 -1; Mrpp=20; pas) azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 65

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation de t, pr, tsplit , tbal, tmerge 100 t Pr 90 tsplit tbal tmerge 80 70 60 50 40 1 pas = Nr 0 / 100 30 20 10 200 190 180 170 160 150 140 130 120 110 100 90 80 70 60 50 40 30 20 10 0 0 [Np 0 = 11 250; Mrpp = 20; %ins = 90; t 0 = 0, 8; Nop = 360 000] azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 66

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (taux) Evaluation de pr, tsplit , tbal, tmerge 15 tsplit 14 tbal 13 tmerge 12 11 10 9 8 7 6 1 pas = Nr 0 / 100 5 4 3 2 1 200 190 180 170 160 150 140 130 120 110 100 90 80 70 60 50 40 30 20 10 0 0 [Np 0 = 11 250; Mrpp = 20; %ins = 90; t 0 = 0, 8; Nop = 360 000] azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 67

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (application) Application : évolution d'un fichier de base Nbre initial de pages Taille des pages Taux de chargement initial % des insertions Nbre d'opérations par jour Nbre de pas Nop = 2 x Nr 0 Tps lecture page suivante (prefetch) Tps lecture aléatoire 1 page. Tps lecture page suivante (rupture) Tps accès page de base av. insert Tps accès page de base av. delete Nbre initial d'enr. par page Nbre initial d'enr. Nbre total d'opérations Nbre d'opérations par pas Nbre d'insertions par pas Nbre de suppressions par pas Nbre de nouveaux enreg. par pas Nbre de jours par pas azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 68

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (application) Application : évolution d'un fichier de base Nbre de jours N° du pas Nbre d'enreg. Nbre d'enr. par page Nbre de pages Tps de lecture séquentielle Vitesse de lecture (enreg. /s) Tps d'une insertion Tps d'une suppression azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 69

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (application) Application : évolution d'un fichier de base Quand réorganiser ? Tps lecture page suivante (rupture) tlsr = 12, 3 ms tlsr = 4, 0 ms azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 70

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (application) Application : calcul volume et tps de lecture fichier SI Tps déplacement bras unitaire Tps déplacement bras moyen Vitesse de rotation Taille d'un secteur Nbre de secteurs/piste Nbre de pistes/cylindre Nbre de cylindre Capacité d'une piste Capacité d'un cylindre Capacité d'un disque (en MB) Tps d'une rotation Tps lecture d'un secteur aléatoire Tps lecture d'une piste aléatoires Modèle de calcul : ISAM. xls azerty I. Concepts des bases de données Taille d'une page Taille pointeur d'enreg. Taille pointeur de page Taux d'occupation fichier de base Taux d'occupation index Overhead page de base Overhead enreg. de base Overhead page d'index Overhead entrée d'index Overhead fichier Taille d'une extension Taille du tampon Nre de pages/piste Tps lecture d'une page aléatoire Nbre d'enreg. Taille d'un enreg. Taille de la clé Bases de données J-L Hainaut 2012 71

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (application) Application : calcul volume et tps de lecture fichier SI Taille enreg. Nbre max d'enreg. /page Nbre moyen d'enreg. /page Nbre de pages de base Taille entrée d'index Nbre max d'entrées/page Nbre moyen d'entrées/page Nbre de pages d'index Nbre de niveaux d'index Niveau 3 : nbre d'entrées Niveau 3 : nbre de pages Niveau 2 : nbre d'entrées Niveau 2 : nbre de pages Niveau 1 : nbre d'entrées Niveau 1 : nbre de pages azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 72

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances (application) Application : calcul volume et tps de lecture fichier SI Volume du fichier de base Volume de l'index Volume total réel. . . en MB. . . en disque Nbre de niveaux observés Nbre de niveaux calculés Taux d'occupation global Tps max de lect. séquentielle Idem réduit à l'enreg. Vitesse de lecture Tps d'accès par clé Tps min. de lect. séquentielle Idem réduit à l'enreg. Vitesse de lecture Accès par clé Idem en secondes Vitesse de lecture par clé Taille du tampon utilisée azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 73

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances Dégradation des performances d'un fichier séquentiel indexé Les opérations de modification entraînent principalement 2 phénomènes de dégradation des performances : 1. Chute des taux d'occupation tb et ti : Npb = Nr / (tb x Mrpp) augmente tlsf = Npb x tls 1 augmente n = log(Npb) / log(ti x Lp / Li ) augmente 2. Augmentation des ruptures de séquence : pr augmente tlsf = (1 - pr) x Npb x tls 1 + pr x Npb x tla 1 0, 184 ms 12, 3 ms Cependant : dégradation très progressive. azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 74

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances Taux : observations et recommandations générales 1. Valeur optimale de t 0 = 0, 7. t 0 = 1 admissible si aucune insertion (fichier en lecture seule par exemple) 2. Performances favorable pour les grandes valeurs de Mrpp (fichier de base et index). 3. t et pr deviennent défavorables à partir du pas 50, voire plus tôt. Réorganisation du fichier recommandée dès que nop se rapproche de 30 à 50% du nombre d’enregistrements au départ. p. ex. à partir de 400. 000 opérations dans un fichier de 1. 000 enregistrements azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 75

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée - Performances Réorganisation d'un fichier séquentiel indexé Reconditionnement du fichier : 1. taux d'occupation favorable (70 -80) uniforme (pour toutes les pages) 2. élimination des ruptures de séquences (pr = 0) Réorganisation par rechargement : Processus très simple et rapide (linéaire en Nr mais attention à pr !) 1. lecture séquentielle du fichier courant 2. rangement séquentiel des enregistrements dans le nouveau fichier, avec un taux d'occupation initial favorable (p. ex. t 0 = 0, 7 à 0, 8) 3. en parallèle, construction des différents niveaux d'index, avec un taux d'occupation initial favorable. azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 76

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 8 Les index secondaires 4. 5 Les index 4. 9 Les techniques d'agrégation 4. 6 Organisation séquent. indexée 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée 4. 6 Organisation séquentielle indexée Compléments 1. Adaptation aisée au cas où K n'est pas un identifiant (mais rarement appliqué) 2. Le fichier de base est instable : l'adresse d'un enregistrement peut évoluer au cours du temps 3. Application intéressante aux fichiers comportant des enregistrements de plusieurs types. azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 77

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 78

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Principes Deuxième technique de base d'implémentation d'un index primaire L'adresse d'un enregistrement est obtenue par une fonction de calcul azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 79

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Principes Considérant l'expression A = f(K), la fonction f est implémentée sous la forme d'une procédure de calcul fournissant une adresse (celle d'une page) pour chaque valeur de K. séquentiel indexé k conversion k p mémorisée azerty I. Concepts des bases de données calculé page [p] f k conversion k p calculée Bases de données J-L Hainaut 2012 80

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Principes à démontrer ! L'organisation calculée d'un fichier permet 1. la lecture séquentielle non ordonnée des enregistrements du fichier de base 2. l'accès rapide à l'enregistrement possédant une valeur de clé K = k 4. l'insertion rapide d'un enregistrement de clé K 5. la modification rapide de la valeur de K d'un enregistrement 6. la suppression rapide d'un enregistrement Cette organisation ne permet pas l'accès séquentiel ordonné azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 81

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Principes Hypothèses simplificatrices 1. La clé K est un identifiant du fichier 2. Le fichier contient des enregistrements d'un seul type 3. Les enregistrements sont de taille fixe 4. Un enregistrement est entièrement compris dans une page Ces contraintes sont aisées à lever azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 82

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Principes Nature de la fonction f ? K : valeurs de l'identifiant p : adresse de la page de l'enregistrement k K Mrpp : capacité d'une page Cas 1 K = {0, 1, 2, 3, . . . , 99998, 99999} p = k / Mrpp Cas 2 K = {1, 3, 5, 7, . . . , 99997, 99999} p = ((k - 1)/2) / Mrpp Cas 3 K = {1, 3, 4, 87, 162, 163, 164, 290, 1035, . . . , 99991} p= ? Cas 4 K = {'Anselme', 'Bernard', 'Catherine', 'Eve', . . . , 'Xavier'} p= ? azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 83

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Principes Nature de la fonction f ? La fonction f doit convertir un ensemble de valeurs quelconques en une suite d'entiers naturels également répartis dans l'espace des adresses En pratique, une telle fonction n'existe pas en toute généralité La fonction f doit convertir un ensemble de valeurs quelconques en une suite d'entiers naturels raisonnablement bien répartis dans l'espace des adresses Les pages du fichier seront donc inégalement occupées : • certaines pages resteront peu occupées voire vides • certaines pages déborderont. azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 84

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Principes Deux problèmes à résoudre : 1. Choix de la fonction f de calcul d'adresse 2. Gestion des débordements azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 85

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Principes La fonction de calcul d'adresse Objectif : répartir au mieux les valeurs de K dans l'espace des adresses. f(k) = f 3(f 2(f 1 k))) f 1 : numérisation - transforme k en nombre n 1 f 2 : hachage - transforme n 1 en n 2 de répartition régulière f 3 : cadrage - transforme n 2 en p, ajusté à Np azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 86

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Principes La fonction de calcul d'adresse Où ranger l’enregistrement d’identifiant "Charles" dans un fichier de 5. 000 pages ? • numérisation f 1 Codes ASCII des caractères de "Charles" = chaîne de 56 bits : 01000011 01101000 01100001 01110010 01101100101 01110011 XOR entre les 32 premiers bits et les 24 suivants étendus à 32 bits par des 0 On obtient le nombre 0010 1111 0000 1101 0010 0111 0010 = 789385842 On a donc f 1("Charles") = 789385842. • hachage f 2 Technique du pliage : f 2(789385842) = 78938 + 02485 = 81423. • cadrage f 3 Ajustement à un espace de [0, 4999] pages : f 3(81423) = 81423 mod 5000 = 1423. On a donc : f("Charles") = 1423 L’enregistrement sera rangé dans la page d’adresse p = 1 423. azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 87

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Principes Gestion des débordements Que faire d'un enregistrement dont l'adresse p désigne une page pleine ? Réponse : on le stocke dans une page dont l'adresse p' est dérivable de p. Exemple : on le stocke dans une page qui possède assez d'espace pour accueillir l'enregistrement et dont l'adresse p' est stockée dans la page [p]. On distingue donc : l’adresse de base p l’adresse de stockage p’ azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 88

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Principes Gestion des débordements azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 89

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Performances d'un fichier calculé Un fichier calculé est défini par 1. Npb le nombre de pages de base 2. Mrpp la taille des pages On connaît, à un moment déterminé, 1. Nr le nombre d'enregistrements qui ont été stockés (répartis entre espace de base et espace de débordement) 2. Nrpp le nombre moyen d'enregistrements envoyés dans une page de base; peut être > Mrpp On définit : tch = Nrpp / Mrpp, le taux de chargement du fichier (typiquement de 0, 5 à 2) On a : Nrpp = tch x Mrpp Nr = tch x Mrpp x Npb azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 90

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Performances d'un fichier calculé Volume du fichier On connaît : On cherche : Npb le nombre de pages de base Np le nombre total de pages On pose : Np = tap x Npb tap : taux d'accroissement du nombre de page dû aux débordements On détermine la fonction tap = f(Mrpp, tch) Disponible sous forme d'abaque azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 91

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Performances d'un fichier calculé Volume du fichier tap 2, 60 2, 40 2, 20 2, 00 Mrpp 50 5 10 15 20 25 30 35 40 45 50 1, 80 1, 62 5 1, 60 1, 40 1, 20 1, 00 0, 5 0, 6 azerty I. Concepts des bases de données 0, 7 0, 8 0, 9 1 1, 2 1, 3 1, 4 1, 5 1, 6 1, 7 1, 8 1, 9 2 tch Bases de données J-L Hainaut 2012 92

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Performances d'un fichier calculé Volume du fichier Exemple Mrpp = 20 Nr = 2 200 000 Npb = 100 000 tch = Nr / (Mrpp x Npb) = 1, 1 L'abaque nous donne, pour Mrpp = 20 et tch = 1, 1 tap = 1, 62 On a donc : Np = tap x Npb = 162 000 pages Taux d'occupation global t = Nr / (Mrpp x Np) = 0, 679 azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 93

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Performances d'un fichier calculé Temps de lecture séquentielle Ordre des enregistrements indifférent tlsf = Np x tls 1 azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 94

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Performances d'un fichier calculé Temps de lecture par clé nombre moyen de lectures de pages : nlp = f(Mrpp, tch) nlp 1, 7 1, 6 1, 5 Mrpp 1, 4 5 1, 3 50 1, 2 1, 135 1, 1 5 10 15 20 25 30 35 40 45 50 1, 0 0, 5 0, 6 0, 7 0, 8 0, 9 azerty I. Concepts des bases de données 1 1, 2 1, 3 1, 4 1, 5 1, 6 1, 7 1, 8 1, 9 2 tch Bases de données J-L Hainaut 2012 95

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Performances d'un fichier calculé Temps de lecture Exemple L'abaque nous donne, pour Mrpp = 20 et tch = 1, 1 nlp = 1, 135 On a donc : tlk 1 = nlp x tla 1 = 14 ms Vitesse de lecture : 71 enreg. /s azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 96

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 7 Organisation calculée - Comparaison des techniques primaires séquentiel indexé calculé très répandu plus rare moyen à excellent plusieurs milliers de pages 1 page faible bonne moyen, améliorable excellent accès par clé : constance tps constant variable tps d'accès par clé selon Npb logarithmique moyenne constante accès ordonné oui non accès par intervalle oui non dégradation progressive (pr !) progressive, faible, extensibilité oui rapide lente disponibilité occupation espace disque occupation mémoire centrale stabilité adresses accès par clé : temps moyen reconstruction azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 97

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 8 Les index secondaires azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 98

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 8 Les index secondaires Rappel Index primaire 1. le plus souvent identifiant 2. un seul index primaire par fichier 3. la structure du fichier dépend fortement de la présence d'un index primaire 4. difficile d'ajouter et de supprimer un index primaire au vol 5. faible volume, très bonnes performances Index secondaire 1. identifiant ou non identifiant 2. de 0 à N index secondaires par fichier 3. la structure du fichier est indépendante de la présence d'un index secondaire 4. facile d'ajouter et de supprimer un index secondaire au vol 5. volume plus important, moins bonnes performances 6. Certains SGBD n'offrent que des index secondaires azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 99

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 8 Les index secondaires Index (dans un livre) Rappel B base de données active 167 base de données temporelles 153, 170 between 67 les termes de la lettre B C cardinalité d’attribut 182 cardinalité d’un role 187 catalogue 147, 169 cellules 280 Chen 29, 205 chimpanzé 364 circuit de dépendances 312, 392 classe d’objets 201 classe fonctionnelle 184, 185, 242, 245 clé étrangère 34, 44, 77, 81, 156 close 145 coalescing 155 COBOL 18, 143 Codd 27 codomaine 351 colonne 32, 35, 44 azerty I. Concepts des bases de données le terme clé étrangère numéros des pages qui contiennent ce terme Bases de données J-L Hainaut 2012 100

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 8 Les index secondaires Index (dans une base de données) Rappel Index sur la colonne LOCALITE Bruxelles Genève Lille Namur Paris Poitiers Toulouse 11 03 12 01 06 14 15 16 02 07 09 04 05 08 10 13 numéros des lignes dont LOCALITE = 'Toulouse' les valeurs de LOCALITE, triées par ordre alphabétique azerty I. Concepts des bases de données 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 chaque ligne possède un numéro unique; l'accès à une ligne de numéro donné est très rapide Bases de données J-L Hainaut 2012 101

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 8 Les index secondaires Structure d'un index secondaire (non identifiant) Fichier de base Index (LOCALITE) Bruxelles Genève Lille Nv entrées Namur Paris Poitiers Toulouse F 400 Jacob . . . Bruxelles C 2. . . B 332 Monti . . . Genève B 2. . . B 062 Goffin . . . Namur B 2. . . K 111 Vanbist . . . Lille B 1. . . C 123 Mercier . . . Namur C 1. . . L 422 Frank . . . Namur C 1. . . S 127 Vanderka. . . Namur C 1. . . B 112 Hansenne. . . Poitiers C 1. . . S 712 Guillaume. . . Paris B 1. . . F 011 Poncelet . . . Poitiers B 2. . . F 010 Toussaint. . . Poitiers C 1. . . K 729 Neuman . . . Toulouse B 3. . . C 400 Férard . . . Poitiers D 063 Mercier . . . Toulouse A 1. . . C 003 Avron . . . Toulouse B 1. . . B 512 Gillet . . . Toulouse B 1. . . B 2. . . données de base Accès aux enregistrements (liste de pointeurs) Dictionnaire des valeurs Index du dictionnaire des valeurs ( séq. indexé ou calculé) azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 102

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 8 Les index secondaires Structure d'un index secondaire (identifiant) Index (NCLI) peut remplacer un index primaire azerty I. Concepts des bases de données Fichier de base B 062 F 400 Jacob . . . Bruxelles C 2. . . B 112 B 332 Monti . . . Genève B 2. . . B 332 B 062 Goffin . . . Namur B 2. . . B 512 K 111 Vanbist . . . Lille B 1. . . C 003 C 123 Mercier . . . Namur C 1. . . C 123 L 422 Frank . . . Namur C 1. . . C 400 S 127 Vanderka. . . Namur C 1. . . D 063 B 112 Hansenne. . . Poitiers C 1. . . F 010 S 712 Guillaume. . . Paris B 1. . . F 011 Poncelet . . . Poitiers B 2. . . F 400 F 010 Toussaint. . . Poitiers C 1. . . K 111 K 729 Neuman . . . Toulouse B 3. . . K 729 C 400 Férard . . . Poitiers L 422 D 063 Mercier . . . Toulouse A 1. . . S 127 C 003 Avron . . . Toulouse B 1. . . S 712 B 512 Gillet . . . Toulouse B 1. . . B 2. . . Bases de données J-L Hainaut 2012 103

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 8 Les index secondaires Un fichier peut posséder plusieurs index secondaires Index (NCLI) azerty I. Concepts des bases de données Fichier de base B 062 F 400 Jacob . . . Bruxelles C 2. . . B 112 B 332 Monti . . . Genève B 2. . . B 332 B 062 Goffin . . . Namur B 2. . . B 512 K 111 Vanbist . . . Lille B 1. . . Bruxelles C 003 C 123 Mercier . . . Namur C 1. . . Genève C 123 L 422 Frank . . . Namur C 1. . . Lille C 400 S 127 Vanderka. . . Namur C 1. . . Namur D 063 B 112 Hansenne. . . Poitiers C 1. . . F 010 S 712 Guillaume. . . Paris B 1. . . F 011 Poncelet . . . Poitiers B 2. . . F 400 F 010 Toussaint. . . Poitiers C 1. . . K 111 K 729 Neuman . . . Toulouse B 3. . . K 729 C 400 Férard . . . Poitiers L 422 D 063 Mercier . . . Toulouse A 1. . . S 127 C 003 Avron . . . Toulouse B 1. . . S 712 B 512 Gillet . . . Toulouse B 1. . . Index (LOCALITE) Paris Poitiers Toulouse B 2. . . Bases de données J-L Hainaut 2012 104

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 8 Les index secondaires Représentation pratique des listes de pointeurs Toulouse une entrée théorique Toulouse Toulouse Toulouse ner entrées réelles azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 105

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 8 Les index secondaires Les index bitmap Chaque entrée K = k comporte une chaîne B de Nr bits. Si l'enregistrement i est tel que K = k, alors B(i) = 1 Index (LOCALITE) Bruxelles 100000000 Genève 010000000 Lille 0001000000 Namur 001011100000 Paris 000010000000 Poitiers 0000000101101000 Toulouse 00000010111 Chaînes très longues compression par technique sans pertes (LZN par exemple) azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 106

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 8 Les index secondaires Les index identifiants augmentés Les entrées contiennent des valeurs de champs additionnelles. Index (NCLI + [LOCALITE, CAT]) Fichier de base B 062 Bruxelles C 2 F 400 JACOB Bruxelles C 2 B 112 Genève B 2 B 332 MONTI Genève B 2 B 332 Namur B 2 B 062 GOFFIN Namur B 2 B 512 Lille B 1 K 111 VANBIST Lille B 1 C 003 Namur C 123 MERCER Namur C 123 Namur C 1 L 422 FRANK Namur C 1 C 400 Namur C 1 S 127 VANDERKA Namur C 1 D 063 Poitiers C 1 B 112 HANSENNE Poitiers C 1 F 010 Paris B 1 S 712 GUILLAUME Paris B 1 F 011 Poitiers B 2 F 011 PONCELET Poitiers B 2 F 400 Poitiers C 1 F 010 TOUSSAINT Poitiers C 1 K 111 Toulouse B 3 K 729 NEUMAN Toulouse B 3 K 729 Poitiers C 400 FERARD Poitiers L 422 Toulouse A 1 D 063 MERCIER Toulouse A 1 S 127 Toulouse B 1 C 003 AVRON Toulouse B 1 S 712 Toulouse B 1 B 512 GILLET Toulouse B 1 B 2 azerty I. Concepts des bases de données Objectif : éviter l'accès au fichier de base. select NCLI, LOCALITE, CAT from CLIENT where NCLI between k 1 and k 2 and CAT like 'B%'; B 2 Bases de données J-L Hainaut 2012 107

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 8 Les index secondaires Performances d'un index secondaire Un index secondaire est constitué de : • un dictionnaire de valeurs; pour chacune des Nv entrées théoriques, ner entrées réelles : • une valeur de K de longueur Lk • une sous-liste de R' pointeurs • un index primaire pour l'accès au dictionnaire de valeurs (séquentiel indexé ou calculé) On dispose donc de tous les éléments pour calculer : • le volume de l'index • le temps d'accès aux enregistrements selon le critère "where K = k" • le temps d'accès aux enregistrements selon le critère "where K between k 1 and k 2" azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 108

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 9 Les techniques d'agrégation azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 109

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 9 Les techniques d'agrégation Proximité logique proximité physique Proximité logique : • enregistrements d'un fichier lus successivement • enregistrements lus dans un certain ordre • enregistrements couplés par des associations Proximité physique : • enregistrements dans la même page • enregistrements dans des pages successives Objectif : minimiser le nombre d'accès physiques azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 110

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 9 Les techniques d'agrégation Proximité logique proximité physique enregistrements d'un fichier lus successivement select NCLI, NOM, LOCALITE from CLIENT; enregistrements rangés séquentiellement dans l'espace; chaque page est lue une seule fois azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 111

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 9 Les techniques d'agrégation Proximité logique proximité physique enregistrements lus dans un certain ordre select NCLI, NOM, LOCALITE from CLIENT order by NCLI; les enregistrements sont rangés dans l'espace par valeurs croissante de NCLI; chaque page est lue une seule fois azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 112

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 9 Les techniques d'agrégation Proximité logique proximité physique enregistrements couplés par des associations select M. NCOM, DATECOM, QCOM, NPRO from COMMANDE M, DETAIL D where M. NCOM = D. NCOM; les enregistrements COMMANDE et DETAIL de mêmes valeurs de NCOM sont rangés dans la (les) même(s) page(s); plusieurs enregistrements utiles dans une même page; azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 113

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 9 Les techniques d'agrégation Deux techniques très répandues : • le clustering index (p. ex. DB 2, SQL Server) • le cluster (p. ex. Oracle) azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 114

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 9 Les techniques d'agrégation Le clustering index • index secondaire sur une clé K; • table de base a priori en vrac MAIS : (tant qu'à faire) on cherche à ranger les lignes selon les valeurs croissantes de K; • lors du chargement initial, on laisse de l'espace libre dans chaque page; • les insertions se font autant que possible de manière à respecter l'ordre selon K; • après un certain temps, l'ordre est de moins en moins respecté; • nécessite une réorganisation de temps en temps; azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 115

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 9 Les techniques d'agrégation Le clustering index • au début, performances aussi favorables que le séquentiel indexé (lecture d'une page pour Nrpp lignes); • au cours du temps, les performances se dégradent et se rapprochent de celles d'une table en vrac (lecture d'une page pour chaque ligne); • un seul clustering index par table • exclut tout index primaire favorise les requêtes incluant : • order by K • group by K • K between k 1 and k 2 • K >= k 1 • K like 'MAC%'; azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 116

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 9 Les techniques d'agrégation Le cluster • basé sur une colonne (ou des colonnes) K commune(s) à une ou plusieurs tables; • les lignes de même valeur de K sont rangées dans la même page; si nécessaire dans des pages successives d'une chaîne de pages • on associe un index au cluster Index (NCOM) Fichier de base : clusters <COMMANDE(NCOM), DETAIL(NCOM)> 30177 30178 K 111 22/12/2008 30178 CS 464 0025 30179 C 400 22/12/2008 30179 CS 262 0060 30179 PA 60 0020 30182 30184 30182 S 127 23/12/2008 30182 PA 60 0030 30185 30184 C 400 23/12/2008 30184 CS 464 0120 30184 PA 45 0020 30186 30185 F 011 02/01/2009 30185 CS 464 0260 30185 PA 60 0015 30185 PS 222 0600 30187 azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 117

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server 4. 9 Les techniques d'agrégation Le cluster • un seul cluster par table • exclut tout index primaire favorise les requêtes incluant : • des jointures • des sous-requêtes • group by K • K = k 1 et, si l'index du cluster est en séquentiel indexé : • K between k 1 and k 2 • K >= k 1 • K like 'MAC%'; azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 118

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server de Microsoft azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 119

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données . . . 4. 5 Les index 4. 6 Organisation séquent. indexée 4. 7 Organisation calculée 4. 8 Les index secondaires 4. 9 Les techniques d'agrégation 4. 10 Un exemple : SQL Server de Microsoft voir annexe H azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 120

1. Motivation et introduction 5. Les SGBD 2. Concepts des bases de données 3. Modèle relationnel et normalisation 4. Implémentation des structures de données Fin du module 4 Module suivant : 5 : Les systèmes de gestion de bases de données azerty I. Concepts des bases de données Bases de données J-L Hainaut 2012 121

azerty Bases de données J-L Hainaut 2012 122