6 L`indexation

ALSI S25
Administration d’un SGBD Relationnel
STS2 IG
Création d’index
Le 16/04/17
page 1/9
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
PRINCIPE.................................................................................................................... 4
DIFFERENTS TYPES D’INDEX ..................................................................................... 5
6.2.1
6.2.2
6.2.3
6.3
GESTION DES INDEX .................................................................................................. 9
6.3.1
6.3.2
6.3.3
6.4
B-Arbre.......................................................................................... 6
Hachage ......................................................................................... 7
Index bitmap .................................................................................. 7
Création ...................................................................................... 9
Suppression ................................................................................ 9
Réorganisation ............................................................................ 9
QUAND UTILISER LES INDEX ? .................................................................................. 9
D:\769797532.doc
1
ALSI S25
Administration d’un SGBD Relationnel
STS2 IG
Création d’index
Le 16/04/17
page 2/9
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:\769797532.doc
ALSI S25
Administration d’un SGBD Relationnel
STS2 IG
Création d’index
Le 16/04/17
page 3/9
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:\769797532.doc
5
ALSI S25
Administration d’un SGBD Relationnel
STS2 IG
Création d’index
Le 16/04/17
page 4/9
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:\769797532.doc
ALSI S25
Administration d’un SGBD Relationnel
STS2 IG
Création d’index
Le 16/04/17
page 5/9
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 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.
En général, on spécifie un index creux pointant sur chaque page disque. Il faut néanmoins
essayer de respecter la contrainte de taille car il est préférable que l’index tienne en mémoire
centrale. Un index non dense requiert moins de mises à jour lors des opérations d’insertion et
de suppression. Il faut établir un compromis entre temps d'accès / espace mémoire.
D:\769797532.doc
ALSI S25
Administration d’un SGBD Relationnel
STS2 IG
Création d’index
Le 16/04/17
page 6/9
Exemple d’index dense non plaçant :
Il est possible de spécifier un index secondaire, en général sur des attributs non-clés. Ce type
d’index est utilisé si le fichier n'est pas trié sur la clé d'index. Il nécessite une indirection pour
regrouper les pointeurs. Ceci implique une optimisation.
6.2.1
B-Arbre
Un B-Arbre (B Balanced = équilibré) est une structure de table d’index qui a la forme d'un
arbre équilibré. Tous les chemins qui mènent de la racine à une feuille ont la même longueur.
Cf exemple précédent.
Lors de la création de l’arbre (au fur et à mesure des créations d’enregistrements), des places
libres sont créées dans l’index pour ne pas le modifier systématiquement.
Une variante, l’arbre B+ est la structure de stockage par défaut d’Oracle.
D:\769797532.doc
ALSI S25
Administration d’un SGBD Relationnel
STS2 IG
Création d’index
6.2.2
Le 16/04/17
page 7/9
Hachage
Le hachage a pour but d’éviter de parcourir un index pour accéder à une donnée.
L’application d'une fonction de hachage doit permettre à partir de la valeur clé d'un
enregistrement de trouver la position de cet enregistrement dans la structure contenant tous les
enregistrements (tableau, fichier).
Lors d'une insertion, l'application de la fonction de hachage permet de déterminer la place ou
l'enregistrement doit être inséré. Il peut se poser
des problèmes de collision (différentes
techniques sont alors utilisées (place libre suivante, chaînage, seconde fonction de hachage).
Au dessus d'un taux de remplissage de 70%, les techniques de hachage s'avèrent inefficaces.
Notion de cluster sur Oracle.
6.2.3
Index bitmap
Physiquement, un index « Bitmap » stocke pour chaque valeur distincte de l'index un champ
binaire contenant autant de bits que de lignes dans la table de données. Ce champ binaire est
constitué d'une suite de bits, chaque bit correspondant à une ligne de la table de données (avec
la valeur 1 si la ligne contient la valeur de l'index, 0 sinon). De plus, la taille de ce champ
binaire est optimisée par le moteur Oracle afin que des longues suites de zéros puissent être
compressées.
une table « employés », avec une clé primaire, le no d'employé, et quelques
attributs à faible cardinalité (les colonnes Sexe, Statut, Temps partiel et Service).
Exemple : Voici
No
Nom
Sexe
Statut
Temps partiel
Service
1234
Martin
H
Cadre
N
Comptabilité
5845
Dupond
F
Employé
N
Informatique
4785
Dupont
F
Employé
O
Commercial
6985
Peron
H
Employé
N
Comptabilité
4115
Durand
F
Cadre
O
Commercial
8021
Michel
H
Directeur
N
Informatique
325
Thomas
H
Cadre
O
Informatique
8921
Loiseau
F
Employé
N
Commercial
8010
Boileau
H
Employé
N
Comptabilité
401
Maréchal
F
Employé
N
Comptabilité
D:\769797532.doc
ALSI S25
Administration d’un SGBD Relationnel
STS2 IG
Création d’index
Le 16/04/17
page 8/9
Si l'on décide d'indexer ces attributs, l'utilisation des index « Bitmap » s'impose naturellement. On
obtiendrait alors les quatre index ci-dessous :

l'index Sexe :

H
1001011010 ...
F

l'index Temps partiel :
Oui
0010101000 ...
Non
1101011111 ...
0110100100 ...
l'index Statut :

Cadre
1000101000 ...
Employé
0111000111 ...
Directeur
0000010000 ...
l'index Service
Informatique
0100011000 ...
Comptabilité
1001000011 ...
Commercial
0010100100 ...
Ainsi, lors de l'exécution d'une requête mettant en jeu des conditions portant sur ces attributs, le
serveur Oracle utilisera directement ces index « Bitmap ».
Par exemple, si l'on demande « Quels sont les salariés Hommes, de statut cadre et travaillant à temps
partiel ? », le plan d'exécution de la requête s'appuiera sur l'opération suivante :
Le résultat de la requête indique que c'est le tuple correspondant à la 7e ligne de la table qui satisfait
la condition demandée.
Si ces mêmes index avaient été structurés de manière standard (format « B-Tree »), le serveur Oracle
aurait dû parcourir une grande partie des branches de l'arborescence de chacun des index avant de
fournir le résultat. Cette méthode de résolution est bien évidemment bien plus consommatrice en
ressources machines
D:\769797532.doc
ALSI S25
Administration d’un SGBD Relationnel
STS2 IG
Création d’index
Le 16/04/17
page 9/9
6.3 Gestion des index
6.3.1
Création
Syntaxe
CREATE [UNIQUE|BITMAP] INDEX nom_d'index ON table (colonne [,...n])
[TABLESPACE NomTablespace]
Exemples
CREATE TABLE commande
(
NoCommande
int NOT NULL,
NoEmploye
int NOT NULL,
DteCommande
date NOT NULL
DEFAULT GETDATE(),
ProduitCommande int NOT NULL,
QteCommandee int NOT NULL
)
CREATE INDEX Icommande ON commande (NoEmploye)
6.3.2
Suppression
6.3.3
Réorganisation
Syntaxe
Syntaxe
DROP INDEX nom_d'index [,…n]
ALTER INDEX nom_d'index REBUILD
Exemple
Exemple
DROP INDEX ICommande
ALTER INDEX Icommande REBUILD
6.4 Quand utiliser les index ?
Colonnes à indexer
 Clés primaires et étrangères
 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
 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
ATTENTION : Quand une fonction est appliquée au critère de recherche, l’index n’est pas
utilisé. Exemple :
CREATE INDEX IEmploye ON EMPLOYE (Nom)
SELECT *
FROM EMPLOYE
WHERE UPPER(nom) = ‘DUPONT’;
Oracle permet la création d’index qui prend en compte la fonction :
CREATE INDEX IEmploye ON EMPLOYE (UPPER(Nom))
D:\769797532.doc