Discussion :

[Logan Mauzaize]

Bonjour à tous,


J'ai un petit soucis concernant une bête synchronisation entre deux tables en utilisant BULK COLLECT / FORALL.
Mes enregistrements n'ont pas de clé primaire et se composent uniquement de "clés" dont l'une des valeurs peut-être null.
Exemple

Code :

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

1
2
3
4
CREATE TABLE FOO(
  A VARCHAR2(250) NOT NULL,
  B VARCHAR2(250),
  C VARCHAR2(250) NOT NULL);

Pour gérer la suppression des données périmées, j'ai un code du style:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DECLARE
  FOO_ARRAY_TYPE IS TABLE OF FOO%ROWTYPE;
  FOO_ARRAY FOO_ARRAY_TYPE;
  FOO_DEL_CURSOR IS
    SELECT old.A, old.B, old.C FROM FOO old
    MINUS
    SELECT new.A, new.B, new.C FROM BAR new;
BEGIN
  OPEN FOO_DEL_CURSOR;
  LOOP
    FETCH FOO_DEL_CURSOR BULK COLLECT INTO FOO_ARRAY LIMIT 50000;
    FORALL i IN FOO_ARRAY.FIRST ... FOO_ARRAY.LAST
      DELETE FOO WHERE ???;
    COMMIT;
    EXIT WHEN FOO_DEL_CURSOR%NOTFOUND;
  END LOOP;
END;

Pour mes autres tables, je mettais le MINUS dans une sous-vue et je récupérai que les clés primaires. Et pour le delete je mettais les clés primaires dans le WHERE.
Ma question est comment faire sans clé primaire ?

[Waldar]

Ma question est comment faire sans clé primaire ?

Vous pouvez utiliser la pseudo-colonne ROWID, et le PL/SQL n'est pas obligatoire ici :

Code :

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

1
2
3
4
5
6
DELETE FROM FOO
 WHERE rowid in (select rowid
                   from FOO
                  where (A, B, C) in (select A, B, C from FOO
                                       minus
                                      select A, B, C from BAR));

[Logan Mauzaize]

1. Le IN ne fonctionne pas (valeur NULLable)
2. PL/SQL obligatoire pour utiliser BULK COLLECT / FORALL LIMIT. Que j'utilise sinon j'explose mon UNDO. Plusieurs centaines millions d'enregistrements impactés.

[mnitu]

Utilisez Nvl pour les "valeurs" nulles.

</Edit>
Et quelle est la volumétrie de la table après le delete ?

[Waldar]

Le IN ne fonctionne pas (valeur NULLable)

En effet, mais vous pouvez toujours utiliser un EXISTS.
Edit : ou un nvl oui.

PL/SQL obligatoire pour utiliser BULK COLLECT / FORALL LIMIT

C'est une bonne raison, mais la logique reste la même : récupérez la liste des rowid qui ne sont plus valide et supprimez dessus.

Sinon, si c'est pour remettre à l'identique une table par rapport à une autre, avez-vous envisagé ceci :

Code :

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

1
2
3
truncate table foo;
 
insert /*+ append */ into foo select a,b,c from bar;

Même si je suis conscient que la faisabilité dépend du reste des traitements, des volumétries des données modifiées et sûrement d'autres paramètres.

[Logan Mauzaize]

Je rajoute comme contrainte que les données de FOO doivent rester consultable Et toutes les données de FOO ne sont pas à supprimer. J'ai en fait plusieurs sources de données chacune étant responsable de ses données.

D'ailleurs le principe c'est de construire la table BAR qui sera une image partielle de FOO et de synchroniser qu'un sous-ensemble de FOO.

Sinon je vois pas en quoi NVL va régler mon problème

Finalement j'ai trouvé ma solution toute bête !

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DECLARE
  FOO_A_TYPE IS TABLE OF FOO.A%TYPE;
  FOO_A FOO_A_TYPE;
  FOO_B_TYPE IS TABLE OF FOO.B%TYPE;
  FOO_B FOO_B_TYPE;
  FOO_C_TYPE IS TABLE OF FOO.C%TYPE;
  FOO_C FOO_C_TYPE;
  FOO_DEL_CURSOR IS
    SELECT old.A, old.B, old.C FROM FOO old
    MINUS
    SELECT new.A, new.B, new.C FROM BAR new;
BEGIN
  OPEN FOO_DEL_CURSOR;
  LOOP
    FETCH FOO_DEL_CURSOR BULK COLLECT INTO FOO_A, FOO_B, FOO_C LIMIT 50000;
    FORALL i IN FOO_A.FIRST ... FOO_A.LAST
      DELETE FOO WHERE A=FOO_A(i) AND C=FOO_C(i) AND ((B IS NULL AND FOO_B(i) IS NULL) OR B=FOO_B(i));
    COMMIT;
    EXIT WHEN FOO_DEL_CURSOR%NOTFOUND;
  END LOOP;

[mnitu]

...
Sinon je vois pas en quoi NVL va régler mon problème
...

Vous pouvez écrire à la place de

Code :

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

1
2
 
AND ((B IS NULL AND FOO_B(i) IS NULL) OR B=FOO_B(i));

Ceci

Code :

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

1
2
 
AND Nvl(B, 'VALEUR IMPOSSIBLE') = Nvl(FOO_B(i), 'VALEUR_IMPOSSIBLE')

[mnitu]

Je rajoute comme contrainte que les données de FOO doivent rester consultable Et toutes les données de FOO ne sont pas à supprimer. J'ai en fait plusieurs sources de données chacune étant responsable de ses données.

D'ailleurs le principe c'est de construire la table BAR qui sera une image partielle de FOO et de synchroniser qu'un sous-ensemble de FOO.
...

Ca a l’air d’une usine à gaz mais bon peut être que je n’ai pas tout compris.