FirebirdSQL logo
 TABLEVIEW 

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 INDEX

Objectif

Pour créer un index pour une table.

Disponible en

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 ...]
Table 1. Paramètres de la commande CREATE INDEX.
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.

ALTER TABLE COUNTRY
ADD CONSTRAINT PK_COUNTRY PRIMARY KEY (ID);

docnext count = 20

Indices uniques

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.

Direction de l’indice

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 (DESC[ENDING]) peut être utile pour rechercher les valeurs les plus élevées (maximum, dernier, etc.).

Indices calculés ou indices par expression

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.

Indices partiels

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.

Restrictions sur les indices

La longueur maximale de la clé d’indexation est limitée à 1/4 de la taille de la page.

Limitations sur la longueur de la chaîne indexée

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.

Table 1. Longueur de la chaîne indexée et 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

Nombre maximum d’index par table

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.

Table 1. Nombre d’indices et nombre de colonnes

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

Qui peut créer un index ?

Exécuter l’opérateur CREATE INDEX peut :

  • Administrateurs

  • Le propriétaire de la table pour laquelle l’index est créé ;

  • Utilisateurs avec le privilège ALTER ANY TABLE.

Exemples

Example 1. Création d’un index
CREATE INDEX IDX_UPDATER ON SALARY_HISTORY (UPDATER_ID);
Example 2. Création d’un index avec des clés triées par ordre décroissant
CREATE DESCENDING INDEX IDX_CHANGE
ON SALARY_HISTORY (CHANGE_DATE);
Example 3. Création d’un index multi-segment
CREATE INDEX IDX_SALESTAT ON SALES (ORDER_STATUS, PAID);
Example 4. Création d’un index qui n’autorise pas les valeurs dupliquées
CREATE UNIQUE INDEX UNQ_COUNTRY_NAME ON COUNTRY (NAME);
Example 5. Création d’un indice calculable
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)
Voir aussi :

ALTER INDEX, DROP INDEX.

ALTER INDEX

Objectif

Pour faire passer l’index à l’état actif/inactif, reconstruisez l’index.

Disponible en

DSQL, ESQL.

Syntaxe
ALTER INDEX indexname {ACTIVE | INACTIVE};
Table 1. Paramètres de la commande ALTER INDEX.
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 …​ ACTIVE' reconstruit toujours l’index.Cette commande peut donc être utilisée dans le cadre de la maintenance de la base de données pour reconstruire les index créés automatiquement pour les contraintes.`PRIMARY KEY, FOREIGN KEY, UNIQUE pour lesquels le ALTER INDEX …​ INACTIVE n’est pas possible.

Utilisation de ALTER INDEX pour les index de restriction

La 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.

Qui peut faire un `ALTER INDEX' ?

L’opérateur ALTER INDEX peut être exécuté :

  • Administrateurs

  • Le propriétaire de la table pour laquelle l’index est construit ;

  • Utilisateurs avec le privilège ALTER ANY TABLE.

Exemples

Example 1. Index conversion à un état inactif
ALTER INDEX IDX_UPDATER INACTIVE;
Example 2. Retour de l’index à l’état actif
ALTER INDEX IDX_UPDATER ACTIVE;
Voir aussi :

CREATE INDEX, DROP INDEX.

DROP INDEX

Destination

Pour supprimer un index de la base de données.

Disponible en

DSQL, ESQL.

Syntaxe
DROP INDEX indexname
Table 1. Paramètres de la commande DROP INDEX.
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.

Qui peut supprimer l’index ?

La commande DROP INDEX peut être exécutée :

  • Administrateurs

  • Le propriétaire de la table pour laquelle l’index est construit ;

  • Utilisateurs avec le privilège ALTER ANY TABLE.

Exemples

Example 1. Suppression d’un index
DROP INDEX IDX_UPDATER;
Voir aussi :

CREATE INDEX, ALTER INDEX.

SET STATISTICS

Objectif

Nouveau calcul de la sélectivité de l’indice.

Disponible en

DSQL, ESQL.

Syntaxe
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é..

Sélectivité de l’indice

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éé (CREATE) ou activé (ALTER INDEX ACTIVE), les statistiques de l’index sont totalement cohérentes avec son contenu.

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.

Qui peut mettre à jour les statistiques ?

L’instruction SET STATISTICS peut être exécutée :

  • Administrateurs

  • Le propriétaire de la table pour laquelle l’index est construit ;

  • Utilisateurs avec le privilège ALTER ANY TABLE.

Exemples

Example 1. Recalcul de la sélectivité de l’indice IDX_UPDATER
SET STATISTICS INDEX IDX_UPDATER;
Voir aussi :

CREATE INDEX, ALTER INDEX.