Sur de tables à forte volumétrie, valider ou revalider des contraintes de clés étrangères peut être une opération extrêmement longue  si elle n’est pas exécutée en parallèle.

On peut également mettre ces contraintes en mode RELY si le mode d’alimentation le permet afin d’éviter d’avoir à revalider les contraintes après des opérations de chargement en parallèle par exemple.

Comme l’explique d’ailleurs ici Christian Antognini (foreign-keys-and-library-cache-locks), la bonne pratique pour diminuer le temps de verrouillage d’une table pendant ces opérations est de procéder en 2 étapes :

1. ALTER TABLE … ENABLE NOVALIDATE CONSTRAINT …
2. ALTER TABLE … ENABLE VALIDATE CONSTRAINT …

La 1ère étape est immédiate, la seconde peut être parallélisée !

Ceci nous amène au sujet connexe que voulais présenter ici : la modification en parallèle de contraintes NOT NULL

Sur une table de plus de 6 milliards de lignes, j’ai voulu ajouter une contrainte NOT NULL sur les 2 colonnes ID1 et ID2 simultanément.

L’ordre à exécuter est très simple : ALTER TABLE ma_table MODIFY (id1 NOT NULL, id2  NOT NULL);

Malheureusement, l’ordre s’est exécuté en 90 mn sans le moindre parallélisme, ce qui est beaucoup trop long dans un contexte de production ou de MEP.

J’ai donc cherché à paralléliser cette commande, mais ma base étant en version 11gR2, ce ne fut pas si évident que je l’avais imaginé.

En version 12c

Les choses sont très simples, il suffit de positionner au préalable du parallélisme sur la table : ALTER TABLE ma_table PARALLEL 8

A noter à ce sujet, que forcer le parallélisme DML et DDL au niveau de la session n’est pas suffisant pour que ça fonctionne, la note MOS 2264469.1 : Do Not Use Parallel Query In ALTER TABLE MODIFY (column Not Null)  l’explique :

il faut uniquement utiliser ALTER TABLE … PARALLEL X

et ne pas utiliser :

ALTER SESSION FORCE PARALLEL DML PARALLEL 8
ou
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8
ni
ALTER SESSION FORCE PARALLEL DDL PARALLEL 8

Et en version 11gR2 alors ?

Tout cela est très bien, mais quand la base est en version 11gR2 comme la mienne, que faut-il faire ?

Après de multiples tentatives et en repartant de la bonne pratique présentée au début, j’ai fini part déterminer les ordres qui fonctionnent et conduisent à la parallélisation de l’ordre de check de la contrainte. Il faut donc procéder en 2 étapes :

1/ Ajout des contraintes check NOT NULL nommées en NOVALIDATE sur les 2 colonnes

Le mot « nommées » ci-dessus est important car c’est en nommant les contraintes C1 et C2 que les colonnes ID1 et ID2 passent à NULLABLE dans le dictionnaire Oracle. Ce n’est pas le cas avec un simple ADD CONSTRAINT.

Trouver la syntaxe ci-dessous m’a pris un peu de temps…

ALTER TABLE ma_table MODIFY ID1 constraint  C1 NOT NULL NOVALIDATE
ALTER TABLE ma_table MODIFY ID2 constraint  C2 NOT NULL NOVALIDATE

L’opération est immédiate.

2/ Validation des contraintes en parallèle avec positionnement du parallélisme DDL et au niveau de la table

ALTER TABLE ma_table  PARALLEL 8;
ALTER SESSION FORCE PARALLEL DDL PARALLEL  8;

Puis :

ALTER TABLE ma_table MODIFY CONSTRAINT C1 VALIDATE;
ALTER TABLE ma_table MODIFY CONSTRAINT C2 VALIDATE;

On n’oublie pas d’enlever le parallélisme sur la table…

ALTER TABLE ma_table NOPARALLEL;

3/ Résultat

La durée d’exécution entièrement concentrée sur l’étape 2 passe à 9 mn  au lieu de 90 mn  avec un parallélisme de 8  (pour 6Mds de lignes) !

L’ordre SQL sous-jacent exécuté en parallèle pour valider la contraint NOT NULL est :

 select /*+ all_rows ordered dynamic_sampling(2) */ A.rowid, :1, :2, :3 from "ma_table" A where not ( "ID1" IS NOT NULL)

Vérification du résultat final :

SQL> desc ma_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NOT NULL VARCHAR2(20)
ID2  NOT NULL DATE

Le gain en temps de traitement valait bien la peine de se pencher sur ce cas !