Un index est un objet de base de données utilisé pour récupérer plus rapidement les données d’une table ou pour accélérer le tri dans une requête.
De plus, les index sont utilisés pour fournir des restrictions d’intégrité — PRIMARY KEY, FOREIGN KEY, UNIQUE.
Cette section décrit comment créer des index, les mettre à l’état actif/inactif, supprimer des index et collecter des statistiques (recalcul de la sélectivité) pour les index.
CREATE INDEXPour créer un index pour une table.
DSQL, ESQL.
CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]
INDEX indexname ON tablename
{(<column_list>) | COMPUTED [BY] (<value_expression>)}
[WHERE <search_condition>]
<column_list> ::= col [, col ...]
| Paramètre | Description |
|---|---|
indexname |
Nom de l’index. Peut contenir jusqu’à 63 caractères. |
tablename |
Le nom de la table pour laquelle l’index est construit. |
col |
Colonne de la table. Les champs BLOB, ARRAY et calculés ne peuvent pas être utilisés comme colonnes. |
value_expression |
Une expression contenant les colonnes du tableau. Les valeurs de cette expression seront les clés de l’index. |
search_condition |
Une condition de recherche contenant les colonnes de la table. Utilisée pour définir un sous-ensemble d’enregistrements de la table à indexer. |
L’instruction CREATE INDEX crée un index pour la table, qui peut être utilisé pour accélérer la recherche, le tri et/ou le regroupement.
L’index peut également être utilisé lors de la définition de contraintes telles que les contraintes de clé primaire, de clé étrangère ou d’unicité.
Un index peut être construit sur des colonnes de tout type autre que BLOB et les tableaux.
Le nom de l’index doit être unique parmi tous les noms d’index.
|
Note
|
Indices dans les clés
L’ajout d’une contrainte de clé primaire, d’une contrainte de clé étrangère ou d’une contrainte d’unicité créera implicitement un index du même nom. Par exemple, l’instruction suivante créera implicitement un index PK_COUNTRY. |
Si le mot-clé UNIQUE est spécifié lors de la création de l’index, celui-ci garantit l’unicité des valeurs des clés.
Un tel indice est appelé indice unique.
Un indice unique n’est pas une restriction de l’unicité.
Les index uniques ne peuvent pas contenir de valeurs de clés dupliquées (ou de combinaisons de valeurs de clés dupliquées dans le cas d’un index composite, multi-colonnes ou multi-segments). Les valeurs NULL dupliquées sont autorisées selon la norme SQL-99, également dans les index multisegments.
Tous les index dans Firebird sont unidirectionnels.
Un index peut être construit dans l’ordre croissant et décroissant.
Les mots-clés ASC[ENDING] (abrégé en ASC) et DESC[ENDING] sont utilisés pour indiquer la direction de l’index.
Par défaut, un index ascendant ASC[ENDING] est créé.
Il est acceptable de définir un index ascendant et descendant sur la même colonne ou le même jeu de clés en même temps.
|
Tip
|
Un index décroissant ( |
Lorsque vous créez un index au lieu d’une ou plusieurs colonnes, vous pouvez également spécifier une seule expression en utilisant la clause COMPUTED BY.
Un tel index est appelé index calculé ou index basé sur les expressions.
Les index calculés sont utilisés dans les requêtes dans lesquelles la condition dans les clauses WHERE, ORDER BY, ou GROUP BY est exactement la même que l’expression dans la définition de l’index.
Une expression dans un index calculé peut utiliser plusieurs colonnes de la table.
Si vous pouvez spécifier une clause optionnelle WHERE lors de la création d’un index qui spécifie une condition de recherche,
qui restreint le sous-ensemble des entrées de la table à indexer. De tels index sont appelés index partiels.
La condition de recherche doit contenir une ou plusieurs colonnes du tableau.
La définition d’un index partiel peut inclure la spécification UNIQUE. Dans ce cas, chaque clé de l’index doit être unique. Cela garantit l’unicité pour un sous-ensemble de lignes de la table.
Un index partiel ne peut être utilisé que dans les cas suivants :
La condition WHERE comprend exactement la même expression logique que celle définie pour l’index ;
La condition de recherche définie pour l’index contient des expressions logiques combinées par OR, et l’une d’entre elles est explicitement incluse dans la condition WHERE ;
La condition de recherche définie pour l’index spécifie IS NOT NULL, et la condition WHERE inclut une expression pour le même champ qui est connue pour ignorer NULL.
La longueur maximale de la clé d’indexation est limitée à 1/4 de la taille de la page.
La longueur maximale d’une chaîne indexée est inférieure de 9 octets à la longueur maximale de la clé. La longueur maximale de la chaîne indexée dépend de la taille de la page et du jeu de caractères.
Taille de la page |
Longueur maximale de la chaîne indexée pour le jeu de caractères, octet/symbole |
||||
|---|---|---|---|---|---|
1 |
2 |
3 |
4 |
6 |
|
4096 |
1015 |
507 |
338 |
253 |
169 |
8192 |
2039 |
1019 |
679 |
509 |
339 |
16384 |
4087 |
2043 |
1362 |
1021 |
681 |
32768 |
8183 |
4091 |
2727 |
2045 |
1363 |
Pour chaque table, le nombre maximal possible d’index est limité et dépend de la taille de la page et du nombre de colonnes dans l’index.
Taille de la page |
Nombre d’index en fonction du nombre de colonnes dans l’index |
||
|---|---|---|---|
1 |
2 |
3 |
|
4096 |
203 |
145 |
113 |
8192 |
408 |
291 |
227 |
16384 |
818 |
584 |
454 |
32768 |
1637 |
1169 |
909 |
Exécuter l’opérateur CREATE INDEX peut :
Le propriétaire de la table pour laquelle l’index est créé ;
Utilisateurs avec le privilège ALTER ANY TABLE.
CREATE INDEX IDX_UPDATER ON SALARY_HISTORY (UPDATER_ID);CREATE DESCENDING INDEX IDX_CHANGE
ON SALARY_HISTORY (CHANGE_DATE);CREATE INDEX IDX_SALESTAT ON SALES (ORDER_STATUS, PAID);CREATE UNIQUE INDEX UNQ_COUNTRY_NAME ON COUNTRY (NAME);CREATE INDEX IDX_NAME_UPPER ON PERSONS
COMPUTED BY (UPPER (NAME));Un tel index peut être utilisé pour des recherches sans distinction de casse.
SELECT *
FROM PERSONS
WHERE UPPER(NAME) STARTING WITH UPPER('Iv');CREATE INDEX IT1_COL ON T1 (COL) WHERE COL < 100;Si la clause WHERE de la sélection est exactement la même expression que celle spécifiée dans l’index, l’index sera utilisé, sinon non.
SELECT * FROM T1 WHERE COL < 100;
-- PLAN (T1 INDEX (IT1_COL))L’exemple suivant crée un index qui n’inclura pas les valeurs NULL.
CREATE INDEX IT1_COL2 ON T1 (COL) WHERE COL IS NOT NULL;Cet index peut être utilisé par presque tous les prédicats de recherche, à l’exception de IS NULL et IS NOT DISTINCT FROM, puisque les autres expressions ignorent NULL.
SELECT * FROM T1 WHERE COL > 100;
-- PLAN (T1 INDEX IT1_COL2)Un index partiel peut être créé sur plusieurs valeurs de colonnes en les énumérant dans IN ou en combinant plusieurs expressions avec l’opérateur OR.
CREATE INDEX IT1_COL3 ON T1 (COL) WHERE COL = 1 OR COL = 2;SELECT * FROM T1 WHERE COL = 2;
-- PLAN (T1 INDEX IT1_COL3)ALTER INDEXPour faire passer l’index à l’état actif/inactif, reconstruisez l’index.
DSQL, ESQL.
ALTER INDEX indexname {ACTIVE | INACTIVE};
| Paramètre | Description |
|---|---|
indexname |
Nom de l’index. |
La commande ALTER INDEX met l’index dans l’état actif/inactif.
Il n’est pas possible de modifier la structure et l’ordre de tri des clés.
INACTIVE
En sélectionnant "INACTIF", l’index passe de l’état actif à l’état inactif. La mise en état d’inactivité d’un index est similaire en action à la commande DROP INDEX, sauf que la définition de l’index est stockée dans la base de données. Un index participant à une contrainte ne peut pas être rendu inactif.
L’index actif ne peut être rendu inactif que si aucune requête n’utilise cet index, sinon l’erreur object in use est retournée.
L’activation d’un index inactif est également sans danger.
Cependant, si des transactions actives modifient la table, la transaction contenant l’instruction ALTER INDEX échouera si elle a l’attribut NO WAIT.
Si la transaction est en mode WAIT, elle attendra que les transactions concurrentes se terminent.
D’un autre côté, si notre instruction ALTER INDEX commence à reconstruire l’index à COMMIT, alors les autres transactions modifiant cette table échoueront ou attendront en fonction de leurs attributs WAIT/NO WAIT.
La même situation se produira lors de l’exécution de CREATE INDEX.
|
Tip
|
Mettre un index dans un état inactif peut être utile lors de l’insertion, de la modification ou de la suppression en masse d’enregistrements de la table pour laquelle l’index est construit. |
ACTIVE
En sélectionnant l’alternative ACTIF, l’index passe de l’état inactif à l’état actif. Lorsque l’index est déplacé d’inactif à actif — l’index est reconstruit.
|
Tip
|
Même si l’index est dans l’état actif, la commande |
ALTER INDEX pour les index de restrictionLa conversion forcée des index créés pour les contraintes PRIMARY KEY, FOREIGN KEY et UNIQUE n’est pas autorisée.
Cependant, l’exécution de la commande ALTER INDEX … INACTIVE fonctionne aussi bien pour les index de contraintes que les autres outils pour les autres index.
L’opérateur ALTER INDEX peut être exécuté :
Le propriétaire de la table pour laquelle l’index est construit ;
Utilisateurs avec le privilège ALTER ANY TABLE.
ALTER INDEX IDX_UPDATER INACTIVE;ALTER INDEX IDX_UPDATER ACTIVE;DROP INDEXPour supprimer un index de la base de données.
DSQL, ESQL.
DROP INDEX indexname
| Paramètre | Description |
|---|---|
indexname |
Nom de l’index |
L’instruction DROP INDEX supprime un index existant de la base de données.
S’il existe des dépendances pour un index existant (s’il est utilisé dans une contrainte), la suppression ne sera pas effectuée.
La commande DROP INDEX peut être exécutée :
Le propriétaire de la table pour laquelle l’index est construit ;
Utilisateurs avec le privilège ALTER ANY TABLE.
DROP INDEX IDX_UPDATER;SET STATISTICSNouveau calcul de la sélectivité de l’indice.
DSQL, ESQL.
SET STATISTICS INDEX indexname
Paramètres pour la commande SET STATISTICS.
| Paramètre | Description |
|---|---|
indexname |
Nom de l’index. |
L’opérateur SET STATISTICS recalcule la valeur de sélectivité pour l’index spécifié..
La sélectivité de l’index est une estimation du nombre de lignes qui peuvent être sélectionnées lors de la recherche de chaque valeur d’index. Un index unique a une sélectivité maximale, car il ne peut sélectionner plus d’une ligne pour chaque valeur de clé d’index. La pertinence de la sélectivité de l’index est importante pour sélectionner le plan d’exécution de requête le plus optimal pour l’optimiseur.
La sélectivité de l’index peut devoir être recalculée après une insertion, une modification ou une suppression massive d’un grand nombre d’enregistrements dans une table, car elle devient non pertinente.
|
Note
|
Notez que dans Firebird les statistiques de l’index ne sont pas automatiquement recalculées après des changements massifs de données, ni dans aucune autre condition.
Lorsqu’un index est créé ( |
Le recalcul de la sélectivité de l’indice peut être effectué sous un parallélisme élevé sans risque de l’endommager.
Cependant, il faut garder à l’esprit qu’avec un parallélisme élevé, les statistiques calculées peuvent devenir obsolètes dès que l’exécution de l’opérateur est terminée. SET STATISTICS.
L’instruction SET STATISTICS peut être exécutée :
Le propriétaire de la table pour laquelle l’index est construit ;
Utilisateurs avec le privilège ALTER ANY TABLE.
SET STATISTICS INDEX IDX_UPDATER;