Menu

Next

Index/help

Les index

Introduction - Généralités

Considérons le SELECT suivant :

SELECT * 
FROM emp
WHERE nom = 'MARTIN'

Un tel moyen d'accès conduit à des temps de réponse prohibitifs pour des tables dépassant quelques centaines de lignes.

Une solution offerte par tous les systèmes de gestion de bases de données est la création d'index, qui permettra de satisfaire aux requêtes les plus fréquentes avec des temps de réponse acceptables.

Un index sera matérialisé par la création de blocs disque contenant des couples (valeurs d'index, numéro de bloc) donnant le numéro de bloc disque dans lequel se trouvent les lignes correspondant à chaque valeur d'index.

Structure d'un index

Tous les index oracle sont stockés sous forme d'arbres équilibrés (btree) : une structure arborescente permet de retrouver rapidement dans l'index la valeur de clé cherchée, et donc l'adresse de la ligne correspondante dans la table.

Dans un tel arbre, toutes les feuilles sont à la même profondeur, et donc la recherche prend approximativement le même temps quelle que soit la valeur de la clé.

Lorsqu'un bloc d'index est plein, il est éclaté en deux blocs. en conséquence, tous les blocs d'index ont un taux de remplissage variant de 50% à 100%. Sans index on balaie séquentiellement toute la table quelle que soit la position de élément recherché.

Utilisation des index

L'index accélère la recherche d'une ligne à partir d'une valeur donnée de clé, mais aussi la recherche des lignes ayant une valeur d'index supérieure ou inférieure à une valeur donnée, car les valeurs de clés sont triées dans l'index.

Exemple : Les requêtes suivantes bénéficieront d'un index sur le champ n_dept.

SELECT * FROM emp WHERE num = 16034  ;
SELECT * FROM emp WHERE num >= 27234 ;
SELECT * FROM emp WHERE num BETWEEN 16034  AND 27234;

Exemple : La requête suivante bénéficiera d'un index sur la colonne nom.

SELECT * 
FROM emp
WHERE nom LIKE 'M%'

Exemple : La requête suivante ne bénéficiera pas d'un index sur le champ nom.

SELECT * 
FROM emp
WHERE ename LIKE '?????????'

Valeurs NULL

Conversions

SELECT * FROM emp
WHERE salaire * 12 > 300000 ;

SQL est un langage typé, chaque type de données (numérique, caractère, date) ayant ses propres opérateurs, ses propres fonctions et sa propre relation d'ordre. En conséquence, si dans une expression, figurent à la fois un nombre et une chaîne de caractères, SQL convertira la chaîne de caractères en nombre. De même si dans une expression, figurent à la fois une chaîne de caractères et une date, SQL convertira la chaîne de caractères en date.

Or, dans un prédicat du type :

WHERE fonction(col_indexée) = constante

Ceci peut se produire , de façon insidieuse, lorsque SQL est obligé d'ajouter un appel à une fonction de conversion à cause d'une discordance de type.

Exemple : Le prédicat suivant ne bénéficiera pas d'un index sur le champ embauche.

SELECT * FROM emp
WHERE embauche LIKE '????'

WHERE TO_CHAR(embauche) LIKE '????'

Choix des index

1. les clés primaires
2. les colonnes servant de critère de jointure
3. les colonnes servant souvent de critère de recherche

1. les colonnes contenant peu de valeurs distinctes (index alors peu efficace)
2. les colonnes fréquemment modifiées

Créer un index

CREATE [UNIQUE]  INDEX nom_index
ON nom_table (nom_col1 , nom_col2, ...)
[PCTFREE nombre]
[COMPRESS | NOCOMPRESS]
[ROWS = nombre_lignes] ;

• L'option UNIQUE indique que l'on interdit que deux lignes aient la même valeur dans la colonne indexée.
• PCTFREE précise le pourcentage de place laissée libre dans les blocs d'index à la création de l'index. Cette place libre évitera une réorganisation de l'index des les premières insertions de nouvelles clés. La valeur par défaut est 20%.
• NOCOMPRESS indique que l'on ne veut pas comprimer les clés.
• nombre_lignes est une estimation du nombre de lignes, permettant d'optimiser l'algorithme de classement..

Un index peut porter sur plusieurs colonnes, la clé d'accès sera alors la concaténation des différentes colonnes.

On peut créer plusieurs index indépendants sur une même table.

Les requêtes SQL sont transparentes au fait qu'il existe un index ou non. C'est l'optimiseur du système de gestion de bases de données qui, au moment de l'exécution de chaque requête, recherche s'il peut s'aider ou non d'un index.

Index comprimé et non comprimé

En contrepartie, il faut parfois un traitement supplémentaire pour recomposer la clé lors des mises à jour de l'index.

Par défaut, les index sont comprimés, les avantages de réduction de taille l'emportant sur les inconvénients dans la plupart des cas.

sql sait exécuter certaines requêtes directement au niveau de l'index sans passer par le segment de données, si l'index est non comprimé et si tous les champs résultats de la requête sont dans l'index.

Exemple : L'index crée par :

CREATE INDEX x 
ON emp (num, nom)
NOCOMPRESS ;

SELECT nom
FROM emp
WHERE num < 17217 ;

Index concatene

Exemple :

CREATE INDEX xemp
ON (n_dept,num) ;

SQL sait utiliser un index concaténé même si le critère de recherche ne porte pas sur toutes les colonnes présentes dans l'index.

Exemple : L'index ci-dessus est utilisable si l'on ne connait que le numéro de département.

SELECT nom
FROM emp
WHERE n_dept = 20 ;

Supprimer un index

DROP  INDEX nom_index;

L'espace ne sera rendu à la partition que lors de la suppression de la table.

Dernière modification :