Vous me direz qu’il faut sans doute être maniaque pour s’occuper des lignes chaînées d’une table ; si on vous pose la question de supprimer (ou réduire) le nombre de lignes chaînées de votre base de données, que répondez-vous ?

Oracle 10g Administrator’s Guide décrit une manière simple de supprimer les lignes chaînées :

• Créer une table « CHAINED_ROWS » pour stocker les informations sur les lignes chaînées à l’aide du script UTLCHAIN.SQL
• Lancer la commande ANALYZE TABLE … LIST CHAINED ROWS INTO …
• Créer une table intermédiaire qui stocke les lignes chaînées
• Supprimer les lignes chaînées de la table originale
• Re-Insérer les lignes supprimées précédemment dans la table.

Si les lignes ne sont pas chaînées à cause d’un problème de taille, alors cette opération réduira les lignes qui s’étendent sur plusieurs blocs. Et si vous faites les 2 dernières opérations dans la même transaction, les utilisateurs n’y verront probablement rien !

Sauf que si vous avez des clés étrangères… ça ne marche pas ! Ou pire si vous avez des clés étrangères avec ON DELETE CASCADE ou ON DELETE SET NULL, vous perdrez des informations… Et pourtant :

• Si possible utilisez des clés étrangères, ça aide l’optimiseur
• Si possible n’utilisez pas ON DELETE CASCADE ou ON DELETE SET NULL, préférez gérer les suppressions par votre application, même si elle est écrite en PL/SQL, ça évite de supprimer des lignes sans s’en rendre compte.

Les contraintes DEFERRABLE peuvent vous aider dans cette situation :
Regardez ce qu’on peut faire avec les tables EMP et DEPT de scott. Une contrainte DEFFERED n’est valider qu’au moment du COMMIT. Vous pouvez donc supprimer une ligne à condition de la réinsérer avant d’exécuter le commit.

Etape 1 : Modifier les contraintes du schéma pour qu’elle soient DEFERRABLE

Malheureusement en 10g, il n’est pas possible de modifier une contrainte pour la rendre DEFERRABLE ; il faut la supprimer et la recréer. Voici un exemple avec le schéma SCOTT. Notez qu’il faudra, si vos contraintes ne sont pas déjà positionnées, probablement planifier une indisponibilité pour passer vos contraintes DEFERRABLE. Vous pouvez aussi utiliser DBMS_REDEFINITION qui règle le problème des lignes chaînées tout seul. Quoiqu’il en soit, voici le script qui « modifie » les contraintes :

alter table emp
 drop constraint FK_DEPTNO;

alter table dept
 drop constraint PK_DEPT;

alter table dept
 add constraint PK_DEPT
   primary key (deptno)
 deferrable initially immediate
 enable validate;

alter table emp
 add constraint FK_DEPTNO
   foreign key (DEPTNO)
   references DEPT(DEPTNO)
 deferrable initially immediate
 enable validate;

Etape 2 : DEFERRABLE mais not DEFERRED

Si vous ne positionnez pas les contraintes à DEFERRED, vous avez une erreur lors de la suppression de la ligne à cause de la clé étrangère

select count(*) from emp
 where deptno=30;

COUNT
-----
  6

select * from dept
 where deptno=30;

DEPTNO DNAME LOC
------ ----- -------
 30 SALES CHICAGO

delete from dept
 where deptno=30;

ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found

Etape 3 : DEFERRED

Si vous ne positionnez les contraintes à DEFERRED, vous pouvez supprimer puis insérer la ligne sans que la contrainte soit violée puisqu’elle est validée au moment du commit et qu’à ce moment, la ligne est de nouveau dans la table :

set constraints all deferred;

Constraint set.

select count(*) from emp
where deptno=30;

COUNT(*)
----------
       6

select * from dept
 where deptno=30;

  DEPTNO DNAME          LOC
---------- -------------- -------------
      30 SALES          CHICAGO

delete from dept
 where deptno=30;

1 row deleted.

insert into dept(deptno, dname, loc)
 values (30, 'SALES', 'CHICAGO');  2

1 row created.

commit;

Commit complete.

Etape 4 : Attention

DEFERRABLE ne règle pas tout :

• Si la clause ON DELETE est utilisée, vous devrez utiliser une autre méthode
• Si vos contraintes ne sont pas DEFERRABLE, il vous faudra les supprimer et recréer. Il est probable (analysez votre application) que vous deviez suspendre les accès aux données pour le faire…

Si ce post ne répond pas à vos attentes, sans doute que DBMS_REDEFINITION est l’outil qu’il vous faut… J’en parlerai très bientôt !