Discussion :

[cmako]

Bonjour,

J'ai une DB avec plusieurs tables et des relations entre elles avec des FK.

Je veux vider toute la DB en effaçant les contenu de toutes les tables sans faire de DROP TABLE.

Je commence à effacer toutes les tables enfants, et je termine par les parents (ceux qui sont référencés par les FK des enfants). En faite j'efface à l'ordre inverse du remplissage de la DB.

J'arrive à effacer la plupart des tables en faisant
DELETE FROM table CASCADE;

Sauf que 3 tables résistent et ne veulent pas s'effacer. Toutes les 3 sont est référencée par FK par 2 tables respectivement qui sont déjà vides.
La commande DELETE s’exécute sans fin, j'ai même laissé tourner la nuit, le lendemain le DELETE n'a toujours pas fini d'effacer la table.

Je ne comprends pas: pour quelle raison je n'arrive pas à effacer ces tables qui sont référencées par des FK des tables vides. D'autres tables se sont effacées sans problème et d'autres bloquent. Parmi les tables effacées figure même une table référencée par 5 autres tables.

Le seul moyen que j'ai trouvé de les effacer c'est de faire un ALTER TABLE table DISABLE CONSTRAINT et ensuite effacer, mais je n'ai toujours pas compris pourquoi il y a eu un tel blocage.

Il faut dire aussi qu'en environnement de DEV j'ai le droit de faire ALTER TABLE, mais en prod je ne les aurais pas, donc j'aimerais bien comprendre ce qui se passe.

Quelqu'un a une idée?

[Alan TANGUY]

As-tu des FK récursives (ex : fils qui référence le père dans la même table) ?
Des triggers on delete ?

[Waldar]

Il manque probablement des index sur les clefs étrangères, très impactant notamment pour les DELETE CASCADE.

[aieeeuuuuu]

N'auriez-vous pas oublié de valider la transaction dans laquelle vous avez vidé les tables référençantes ?

[cmako]

Il n'y a pas d'index sur les FK. Par contre j'ai essayé avec et sans CASCADE, ça ne change rien.
Il n'y a pas de FK récursives.

Les tables qui ne s'effacent pas sont les 3 dernières de la liste des DELETE.

Et ce sont les 3 premières tables par lesquelles on commence les INSERT en DB

[cmako]

N'auriez-vous pas oublié de valider la transaction dans laquelle vous avez vidé les tables référençantes ?

Non, j'ai tout essayé, j'ai fait un COMMIT, j'ai même tué les autres connexions - rien à faire.

[Alan TANGUY]

Le tablespace d'undo est assez grand ?

[mnitu]

...
DELETE FROM table CASCADE;
...

Comprenez-vous que cascade est juste un alias de la table <table>

[Waldar]

Bien vu mnitu, j'étais passé à côté !

[pachot]

Comprenez-vous que cascade est juste un alias de la table <table>

Bien vu
Alors il faudrait voir si ça va plus vite avec

delete from test fast;

Il n'y a pas d'index sur les FK

Alors pour chaque enregistrement de la table mère, il faut faire un full table scan de la table fille pour vérifier. Et un full scan sur une table qui a eu beaucoup de données (même si il n'y en a plus) est coûteux.

[cmako]

Alors pour chaque enregistrement de la table mère, il faut faire un full table scan de la table fille pour vérifier. Et un full scan sur une table qui a eu beaucoup de données (même si il n'y en a plus) est coûteux.

Mais pourquoi fait-il un full tablescan? Les FK de la table mère référencent les PK des tables filles. Il y a donc bien un index unique, qui doit être à jour et donc le moteur DB doit parcourir celui-ci et non pas la table complète !?

Sinon, y a-t-il une solution pour éliminer le problème?

[skuatamad]

Sinon, y a-t-il une solution pour éliminer le problème?

Oui en créant un index sur les FK.

Sinon une meilleur méthode (à mon sens) serait de disable les FK, truncate les tables (réinitialise le High Water Mark) puis ré-enable les FK.

[pachot]

Bonjour,

Les FK de la table mère référencent les PK des tables filles.

Pour moi, j'appelle table mère celle qui est référence. Donc la suppression dans la table mère doit vérifier qu'il n'y a pas de lignes qui deviendraient orphelines dans la table fille.
Cordialement,
Franck.

[cmako]

Bonjour,

Pour moi, j'appelle table mère celle qui est référence. Donc la suppression dans la table mère doit vérifier qu'il n'y a pas de lignes qui deviendraient orphelines dans la table fille.
Cordialement,
Franck.

Je ne comprends pas trop la réponse. Je comprends que ça doit vérifier les tables filles. Mais, les tables filles sont toutes vides !!!
J'ai aussi remarqué que c'est une seule table qui bloque toutes les autres.
Les 4 tables en question ont, entre autres, une même table fille qui bloque le DELETE.
Lorsque je désactive les FK de cette table, les tables mères s'effacent. Mais les tables mères ont d'autres filles qui pourtant ne posent pas de problèmes.

[skuatamad]

Vérifiez l'indexation des FK de cette table fille.

Votre table est vide mais les blocs sont toujours présents puisque vous avez fait des delete, donc si les FK ne sont pas indexées Oracle doit full scan la table composée de nombreux bloc vide (c'est le concept de High Water Mark)

[cmako]

Vérifiez l'indexation des FK de cette table fille.

Votre table est vide mais les blocs sont toujours présents puisque vous avez fait des delete, donc si les FK ne sont pas indexées Oracle doit full scan la table composée de nombreux bloc vide (c'est le concept de High Water Mark)

Est-ce qu'il est possible de réduire le Water Mark sans créer d’index?
Si oui, comment?

Merci.

[mnitu]

Est-ce qu'il est possible de réduire le Water Mark sans créer d’index?

Aucun lien entre le High Water Mark et l'index. Vous devez comprendre que si vous avez des foreign keys et vous supprimez dans la table parent vous devez créer un index.
Sinon sur votre question on vous a déjà répondu:

...truncate les tables (réinitialise le High Water Mark)...

[cmako]

Sinon sur votre question on vous a déjà répondu:
...truncate les tables (réinitialise le High Water Mark)...

Si on a des foreign keys on ne peut pas faire de TRUNCATE TABLE. On aura une erreur ORA-02266.
De plus je n'aurais pas les droits nécessaires pour faire un TRUNCATE.

[mnitu]

Si on a des foreign keys on ne peut pas faire de TRUNCATE TABLE. On aura une erreur ORA-02266.
De plus je n'aurais pas les droits nécessaires pour faire un TRUNCATE.

J'avais pensé que vous aller lire tout le paragraphe:

...serait de disable les FK, truncate les tables (réinitialise le High Water Mark) puis ré-enable les FK

Si vous ne pouvez pas faire truncate ne le faite pas. Je vous ai répondu à votre question

Est-ce qu'il est possible de réduire le Water Mark sans créer d’index?

et non pas donné des conseils sur votre traitement.

[cmako]

Merci pour vos réponses.

Finallement, j'ai contourné le problème en créant la procédure sur le schéma dont je ne suis pas propriétaire.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE OR REPLACE PROCEDURE SP_RESIZE (tablename VARCHAR2) AS
BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE ' || tablename || ' ENABLE ROW MOVEMENT';
  EXECUTE IMMEDIATE 'ALTER TABLE ' || tablename || ' SHRINK SPACE';
END;