Création d'index concurrente dans Firebird 5

Actuellement, lors de la construction d’un index, toute modification des données de la table est interdite. Cela est nécessaire pour créer un index correct, sans manquer les nouvelles clés insérées pendant la construction.

L’algorithme de construction d’index concurrent permet de réduire considérablement la durée pendant laquelle ce verrou de lecture est requis. Bien qu’il n’élimine pas totalement la nécessité de bloquer les modifications concurrentes des données de la table, la durée de ce blocage est bien moindre par rapport à l’algorithme traditionnel non concurrent. Cela est particulièrement utile en environnement de production, où le blocage de l’activité des utilisateurs pendant de longues périodes est impractical ou impossible.

En raison de la charge concurrente et de la complexité supplémentaire, la création d’index concurrent peut prendre plus de temps que l’approche traditionnelle.

Syntaxe

Le nouveau mot-clé optionnel non réservé CONCURRENTLY est utilisé pour spécifier la création d’index concurrent.

Créer un nouvel index

Code

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]  INDEX indexname ON tablename  {(col [, col ...]) | COMPUTED BY ()}  [WHERE ]  [CONCURRENTLY]

Activer un index existant

Code

ALTER INDEX indexname {ACTIVE | INACTIVE}  [CONCURRENTLY]

Notez que CONCURRENTLY ne peut être utilisé qu’avec ACTIVE.

Validation de l’unicité

Il est difficile, voire impossible, de vérifier l’unicité d’un index pendant sa construction concurrente. Par conséquent, lorsqu’un index unique est créé (ou activé) de manière concurrente, il est immédiatement marqué comme NOT VALIDATED dans le catalogue système. L’unicité de l’index est validée après la fin de la construction de l’index, lorsque l’index contient les clés de tous les enregistrements de la table.

Si la vérification de l’unicité réussit, la marque NOT VALIDATED est effacée.

Si la vérification de l’unicité échoue, l’index reste actif et est marqué comme NOT VALIDATED. Par la suite, l’utilisateur peut trouver et corriger les doublons, puis valider à nouveau l’unicité, ou supprimer l’index si cela est approprié.

Notez qu’un index unique NOT VALIDATED n’autorise toujours pas l’insertion de doublons dans la table.

Pour valider un index unique, exécutez

Code

ALTER INDEX indexname VALIDATE UNIQUE

Cette instruction effectue une analyse de l’index pour trouver les clés en double et vérifier les enregistrements correspondants. Si aucun doublon n’est trouvé, la marque NOT VALIDATED est effacée et l’index est considéré comme validé. Sinon, la marque NOT VALIDATED reste en place et l’index peut être validé à nouveau plus tard.

La commande isql SHOW INDEX affiche désormais la marque NOT VALIDATED, si elle est présente.

Contraintes de table

La création concurrente d’index pour les contraintes de table, telles que PRIMARY|UNIQUE|FOREIGN KEY, n’est pas encore prise en charge. Il est possible de prendre en charge PRIMARY|UNIQUE KEY à l’avenir, mais cela est peu probable pour FOREIGN KEY.

Exemples

Préparez une table avec quelques données

Code

create table tab (  id int not null,  f1 varchar(64));
insert into tab values (1, '1');
insert into tab values (1, '2');commit;

créez un index en utilisant la clause CONCURRENTLY

Code

create index idx_tab_f1 on tab(f1) concurrently;

créez un index unique sur une colonne contenant des doublons

Code

create unique index idx_tab_id on tab(id);

cela génère une erreur :

Code

Statement failed, SQLSTATE = 23000
attempt to store duplicate value (visible to active transactions) in unique index "IDX_TAB_ID"
-Problematic key value is ("ID" = 1)

créez le même index unique en utilisant CONCURRENTLY

Code

create unique index idx_tab_id on tab(id) concurrently;

cela génère un avertissement :

Code

Validation of unique index "IDX_TAB_ID" failed.
-attempt to store duplicate value (visible to active transactions) in unique index "IDX_TAB_ID"
-Problematic key value is ("ID" = 1)

vérifiez si l’index existe :

Code

show index idx_tab_id;
IDX_TAB_ID UNIQUE NOT VALIDATED INDEX ON (ID)

notez la marque NOT VALIDATED.

Validez l’index :

Code

alter index idx_tab_id validate unique;
Statement failed, SQLSTATE = 23000
unsuccessful metadata update-Validation of unique index "IDX_TAB_ID" failed.
-attempt to store duplicate value (visible to active transactions) in unique index "IDX_TAB_ID"
-Problematic key value is ("ID" = 1)

Corrigez l’enregistrement en double et validez à nouveau

Code

update tab set id = 2 where f1 = '2';
commit;
alter index idx_tab_id validate unique;

aucune erreur.

Vérifiez l’état de l’index :

Code

show index idx_tab_id;
IDX_TAB_ID UNIQUE INDEX ON (ID)

Téléchargez HQbird et essayez-le dès maintenant : /fr/download-hqbird