6 L`indexation
publicité
ALSI S25 Administration d’un SGBD Relationnel STS2 IG Création d’index Le 16/04/17 page 1/6 SOMMAIRE 1 PRESENTATION DE LA SEQUENCE ................................................................... 2 1.1 1.2 DESCRIPTION DES SAVOIRS ....................................................................................... 2 COMPETENCES ATTENDUES ...................................................................................... 2 2 PROBLEMATIQUE ................................................................................................... 2 3 ORGANISATION PHYSIQUE DES FICHIERS .................................................... 2 4 ORGANISATION LOGIQUE DES FICHIERS ...................................................... 3 5 METHODES D’ACCES ............................................................................................. 4 6 L’INDEXATION ......................................................................................................... 4 6.1 6.2 6.2.1 6.2.2 PRINCIPE.................................................................................................................... 4 GESTION DES INDEX .................................................................................................. 5 Création .............................................................................................................. 5 Suppression ........................................................................................................ 5 6.3 6.4 DIFFERENTS TYPES D’INDEX ..................................................................................... 6 QUAND UTILISER LES INDEX ? .................................................................................. 6 D:\582687728.doc 1 ALSI S25 Administration d’un SGBD Relationnel STS2 IG Création d’index Le 16/04/17 page 2/6 PRESENTATION DE LA SEQUENCE 1.1 Description des savoirs Ce chapitre présente l’organisation logique des fichiers qui constituent une base de données. S24 Système de gestion de fichiers S25 Administration de la base de données 1.2 Compétences attendues Comparer les techniques d’organisation des données Administrer une BdD non répartie et en assurer la sécurité 2 PROBLEMATIQUE Les SGBD doivent gérer des volumes de données parfois considérables (plusieurs gigas-octets qui sont accédés de façon concurrente par plusieurs utilisateurs simultanément). Ceci impose des contraintes de gestion efficace des données avec un contrôle fin au niveau de l’utilisation afin d’assurer une certaine sécurité et un confort d’utilisation maximum. L’objectif est donc de stocker les données afin d’assurer les performances maximales au niveau des requêtes d’interrogation, d’insertion, de suppression et de mise à jour. 3 ORGANISATION PHYSIQUE DES FICHIERS Afin d’assurer la pérennité de l’information, les données de la base de données sont stockées sur un support de mémoire secondaire. Il existe différents types de mémoire : les mémoires accessibles directement par la CPU (MC, mémoirescaches) - mémoires volatiles -. les mémoires secondaires (disques, BM, ...) plus lentes d'accès mais en général avec une capacité plus grande. Non directement accessibles, - mémoires non volatiles -. Les données d'une base de données sont en général trop volumineuses pour tenir entièrement en mémoire centrale (RAM). Les données sont stockées sous la forme de fichiers d'enregistrements. Les échanges entre la mémoire secondaire et la mémoire centrale se font par transfert de page. Définition Une page est une granule d'allocation d'espace mémoire secondaire (unité de lecture/écriture sur le support). Chacune des pistes comporte la même quantité d'information. Un bloc (une page) représente l'unité de transfert entre disque et zone tampon (buffer) qui est une zone réservée en mémoire centrale. Il est possible de transférer plusieurs blocs à la fois (cluster). Le temps d'accès au premier bloc se situe entre 15 et 60 ms; il est de l'ordre de 1 à 2 ms pour le bloc suivant. D:\582687728.doc ALSI S25 Administration d’un SGBD Relationnel STS2 IG Création d’index Le 16/04/17 page 3/6 L’utilisation de plusieurs buffers permet d’améliorer les performances en réduisant considérablement les temps d'accès. Ceci est possible dans le cas d'une machine multiprocesseurs ou d'un processeur d'E/S dédié. Le Système de Gestion de Fichiers (SGF) gère les différentes méthodes d'accès. Il alloue de l’espace pour les fichiers. Un fichier peut être considéré comme un ensemble de pages sur le disque. Cette vision facilite la gestion en faisant correspondre des adresses relatives et des adresses physiques. @relative (n°page, déplacement) @absolue (n°cylindre, n°piste, n°secteur) Le gestionnaire d'entrées/sorties gère les différentes structures pour chaîner les différentes pages affectées à un fichier d'enregistrements. Un enregistrement n'est pas nécessairement de taille fixe (champ de taille variable ou champs optionnels). On utilise donc des séparateurs de champs et d'enregistrements pour faciliter le parcours de l’information. Un fichier peut contenir des enregistrements de différents types (cluster). On utilise des indicateurs de type d'enregistrements. Un bloc peut contenir plusieurs enregistrements (facteur de blocage). Le nombre d'enregistrements/bloc est soit fixe, soit variable (chaînage). Les blocs sont soit consécutifs, soit chaînés. 4 ORGANISATION LOGIQUE DES FICHIERS Le but est de minimiser le nombre d'accès pour retrouver un bloc particulier. Un descripteur de fichier donne le nom du fichier, le nom du propriétaire, la liste des droits associés, la date de création, la taille, l'organisation, le nombre de champs... L’organisation de fichiers concerne le mode de stockage des enregistrements (1 fichier, 1 organisation). L’organisation séquentielle Les enregistrements sont accessibles dans leur ordre de création. Pour atteindre un enregistrement, il faut d’abord lire tous les précédents. L’organisation relative Les enregistrements porte un numéro d’ordre ; Chaque enregistrement est accessible par son numéro d’ordre. Les enregistrements peuvent aussi se lire séquentiellement du plus petit au plus grand numéro d’ordre. Les enregistrements doivent être de taille fixe. L’organisation séquentielle indexée Quand un fichier est indexé, il est possible de retrouver un enregistrement précis en connaissant une partie des informations qu’il contient : c’est la clé d’index. Une table d’index donne la correspondance entre les valeurs des clés d’index et les enregistrements où on les trouve. D:\582687728.doc 5 ALSI S25 Administration d’un SGBD Relationnel STS2 IG Création d’index Le 16/04/17 page 4/6 METHODES D’ACCES Le type d’accès est choisi par le programmeur ou par le SGBD en tenant compte de l’organisation des fichiers. Il détermine la façon dont les enregistrements seront recherchés dans le fichier. Accès séquentiel On accède aux enregistrements les uns à la suite des autres dans leur ordre de création pour un fichier séquentiel dans l’ordre des numéros d’enregistrement pour un fichier relatif dans l’ordre alphanumérique d’une clé d’index pour les fichiers séquentiels indexés Accès direct L’accès direct n’est possible que sur les fichiers relatifs ou indexés. On accède directement à un enregistrement En connaissant son numéro d’ordre dans un fichier relatif En connaissant la valeur de sa clé d’index dans un fichier indexé. 6 L’INDEXATION 6.1 Principe L’indexation a pour but d’accélérer les traitements de recherche. Il existe différentes techniques (indexation, hachage) selon le contexte. L’évaluation d'une technique se fait selon des critères de temps d'accès, délai d'insertion, délai d'effacement, occupation mémoire. Un index contient le critère de recherche. Ici, index sur l’année des films. D:\582687728.doc ALSI S25 Administration d’un SGBD Relationnel STS2 IG Création d’index Le 16/04/17 Ici, index sur des noms de films. Un index est toujours trié. On parcourt l’index pour trouver la page de donnée. Exemple : je cherche les données du film Psychose. En accès séquentiel : Parcours des pages 1, 2 et 3 => 3 accès disque En accès indexé : Parcours de la page d’index et parcours de la page 3 => 2 accès disque. En accès indexé, le nombre d’accès disque est toujours identique quelque soit la donnée à rechercher et il est égal à la profondeur de l’arbre d’index + 1accès aux données. 6.2 Gestion des index CREATE TABLE ( NoFilm NomFilm DteCreation film INT NOT NULL, VARCHAR(60) NOT NULL, DATE NOT NULL ) 6.2.1 Création Syntaxe CREATE INDEX nom_d'index ON table (col1 [,col2,...]) 6.2.2 Suppression Syntaxe DROP INDEX nom_d'index ; D:\582687728.doc Exemple CREATE INDEX iFilm ON film (NoFilm) Exemple DROP INDEX iFilm; page 5/6 ALSI S25 Administration d’un SGBD Relationnel STS2 IG Création d’index Le 16/04/17 page 6/6 6.3 Différents types d’index Pour une même table, il est possible d'avoir plusieurs index : Un Index primaire (plaçant) : l’ordonnancement des clés dans l'index correspond à l'ordonnancement des enregistrements dans le fichier. L’index secondaire (non plaçant) : l’ordonnancement des clés dans l'index correspond à un ré-ordonnancement logique des enregistrements dans le fichier. L’index contient alors le ROWID de l’enregistrement à accéder. La table d’index peut contenir toutes les valeurs des clés d’index présentes dans le fichier de données. On parle alors d’index dense. Dans le cas contraire (une clé d’index pour plusieurs enregistrements), on parle d’index creux. Exemple d’index dense non plaçant : 6.4 Quand utiliser les index ? Colonnes à indexer Clés primaires et étrangères : la table d’index correspondante est créée automatiquement. Colonnes dans lesquelles des plages de valeurs de clés sont recherchées Colonnes dont l'accès s'effectue selon un ordre trié Colonnes à ne pas indexer Auxquelles il est rarement fait référence dans une requête. Il faut étudier les interfaces. Qui contiennent peu de valeurs uniques (à réserver aux index bitmap) Qui sont définies avec les types de données bit, text ou image Quand une fonction est appliquée au critère de recherche, l’index n’est pas utilisé. Exemple : SELECT * FROM EMPLOYE WHERE UPPER(nom) = ‘DUPONT’; Oracle permet la création d’index qui prend en compte la fonction. D:\582687728.doc
