Discussion :

[Diwann]

Bonjour à tous,

Je cherche à faire une requête de suppression multi-tables avec Oracle 10g.

Je voudrais supprimer tous les éléments retournés par la requête suivante :

Code :

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

1
2
SELECT * FROM TAB_ARA, TAB_VIR
     WHERE TAB_ARA.ARA_NUM_COMPTE_CLI = TAB_VIR.VIR_NUM_COMPTE_EMET;

j'ai donc naturellement lancé :

Code :

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

1
2
DELETE FROM TAB_ARA, TAB_VIR
     WHERE TAB_ARA.ARA_NUM_COMPTE_CLI = TAB_VIR.VIR_NUM_COMPTE_EMET);

mais ca ne marche pas :

DELETE FROM TAB_ARA, TAB_VIR
...............................*
ERROR at line 1:
ORA-00933: SQL command not properly ended

du coup, j'ai essayé de faire une requête imbriquée :

Code :

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

1
2
3
DELETE FROM 
    ( SELECT * FROM TAB_ARA, TAB_VIR
     WHERE TAB_ARA.ARA_NUM_COMPTE_CLI = TAB_VIR.VIR_NUM_COMPTE_EMET);

et ca renvoie :

ORA-01752: cannot delete from view without exactly one key-preserved table

La page suivant explique comment faire :
http://dev.mysql.com/doc/refman/5.0/fr/delete.html
mais c'est pour mysql. J'ai rien trouvé qui marcherait avec Oracle.

Pouvez-vous m'aider ?

[orafrance]

c'est pas possible... le mieux c'est une foreign key avec DELETE CASCADE, quand tu supprimes le père ça supprime les fils

[Diwann]

Merci pour la réponse mais il me semble qu'avec une foreign key, on doit avoir une colonne qui contient strictement les mêmes données dans les 2 tables ?

moi je cherche à supprimer les lignes qui sont identiques, pour ne traiter que les cas problématiques.

j'ai fait :

Code :

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

1
2
3
CREATE TABLE test  AS ( SELECT * FROM TAB_ARA WHERE rownum<100); 
CREATE TABLE test2  AS ( SELECT * FROM TAB_VIR WHERE rownum<100); 
ALTER TABLE test ADD CONSTRAINT unicite FOREIGN KEY (ARA_NUM_COMPTE_CLI) REFERENCES test2(VIR_NUM_COMPTE_EMET);

et il me renvoie "no matching unique or primary key for this column-list".
ce qui me parait normal vu que je sais que les 2 colonnes ne sont pas identiques.

[NGasparotto]

Peitit exemple avec le DELETE CASCADE sur une FK, et ca marche très bien :

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
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
SQL> CREATE TABLE test_dup  AS ( SELECT object_name, object_id, 0 as oid FROM all_objects); 
 
Table created.
 
SQL> CREATE TABLE test2_dup  AS ( SELECT object_name, 0 as object_id, object_id as oid FROM test_dup); 
 
Table created.
 
SQL> ALTER TABLE test_dup ADD CONSTRAINT unicite_pk PRIMARY KEY (object_id);
 
Table altered.
 
SQL> ALTER TABLE test2_dup ADD CONSTRAINT unicite_fk FOREIGN KEY (oid) REFERENCES test_dup(object_id) on delete cascade;
 
Table altered.
 
SQL> 
SQL> select object_id from test_dup where object_id <= 10;
 
 OBJECT_ID
----------
         3
         4
         5
         7
         9
 
SQL> select oid from test2_dup where oid <= 10;
 
       OID
----------
         5
         9
         3
         7
         4
 
SQL> 
SQL> delete test_dup where object_id <= 10;
 
5 rows deleted.
 
SQL> 
SQL> select object_id from test_dup where object_id <= 10;
 
no rows selected
 
SQL> select oid from test2_dup where oid <= 10;
 
no rows selected
 
SQL>

Nicolas.

[Diwann]

Je cherche un moyen de supprimer des éléments qui se trouvent dans les 2 tables et ne garder dans chaque table que les éléments qui ne se trouvent pas dans l'autre.

exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
drop table test;
drop table test2;
create table test (a number);
create table test2 (b number);
insert into test values (1);
insert into test values (2);
insert into test2 values (2);
insert into test2 values (3);
 
ALTER TABLE test ADD CONSTRAINT unicite_pk PRIMARY KEY (a);
ALTER TABLE test2 ADD CONSTRAINT unicite_fk FOREIGN KEY (b) REFERENCES test(a) ON DELETE cascade;

ORA-02298: cannot validate (BO.UNICITE_FK) - parent keys not found

si j'insère les mêmes valeurs dans les 2 tables, la contrainte se crée.

je croit que je vais regarder du coté là : http://oracle.developpez.com/faq/?pa...#errconstraint

mais ca me parait quand meme bien compliqué ...

[Diwann]

Merci pour votre aide.

un moyen pour faire ça est d'ajouter une foreign key sur les 2 tables et de tester les rejets de contraintes:

1 - création de la table des exceptions :

Code :

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

create table exceptions(row_id rowid, owner varchar2(30), table_name varchar2(30), constraint varchar2(30));

2 - contraintes croisées :

Code :

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

1
2
3
4
5
 
ALTER TABLE test ADD CONSTRAINT unicite_pk PRIMARY KEY (a);
ALTER TABLE test2 ADD CONSTRAINT unicite2_pk PRIMARY KEY (b);
ALTER TABLE test ADD CONSTRAINT unicite_fk FOREIGN KEY (a) REFERENCES test2(b) exceptions into exceptions;
ALTER TABLE test2 ADD CONSTRAINT unicite2_fk FOREIGN KEY (b) REFERENCES test(a) exceptions into exceptions;

3 - extraction depuis la table des exceptions :

Code :

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

1
2
3
 
select * from test where rowid in ( select row_id from exceptions where table_name='TEST');
select * from test2 where rowid in ( select row_id from exceptions where table_name='TEST2');

Je teste tout de suite sur la vraie base.

[Diwann]

ORA-02437: cannot validate (BO.VIR_PK) - primary key violated

les colonnes ARA_NUM_COMPTE_CLI et VIR_NUM_COMPTE_EMET ne peuvent pas être déclarées en tant que clés uniques.
ca devient compliqué là.

[NGasparotto]

supprimer des éléments qui se trouvent dans les 2 tables et ne garder dans chaque table que les éléments qui ne se trouvent pas dans l'autre.

Je ne sais pas si tu pourras faire çà en une requête (est-ce vraiment nécessaire ?), mais avec un tout petit PL/SQL, çà peut être beaucoup plus simple (à voir tout de même pour les perf.) :

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
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SQL> CREATE TABLE test (a number);
 
Table created.
 
SQL> CREATE TABLE test2 (b number);
 
Table created.
 
SQL> INSERT INTO test VALUES (1);
 
1 row created.
 
SQL> INSERT INTO test VALUES (2);
 
1 row created.
 
SQL> INSERT INTO test2 VALUES (2);
 
1 row created.
 
SQL> INSERT INTO test2 VALUES (3);
 
1 row created.
 
SQL> 
SQL> begin
  2  for x in (select a from test where exists (select 1 from test2 where a=b)) loop
  3      delete test where a = x.a;
  4      delete test2 where b = x.a;
  5  end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL> 
SQL> select * from test;
 
         A
----------
         1
 
SQL> select * from test2;
 
         B
----------
         3
 
SQL>

Nicolas.

[Diwann]

a ouais !
trop fort.
Merci beaucoup, je teste ca demain.

[Diwann]

Ca marche.
Thanx a lot Nicolas.

Je vais tester les perfs maintenant, mais ca devrait aller avec < 100 000 enregistrements.

[Diwann]

Je reviens sur ce sujet pour signaler qu'après des tests réels, les perfs se sont révélées déplorables (plusieurs heures parfois pour 150 000 lignes); et pour expliquer comment on a pu réduire ça à quelques secondes.

L'idée étant qu'il y a très peu d'enregistrements différents d'une table à l'autre, et qu'il y a des optimisations possible en diminuant le nombre de requete DELETE effectuées.

on a donc créé une colonne supplémentaire pour indiquer les enregistrements qu'il faudra garder.
On utilise alors le script de NGasparotto pour chercher les enregistrements présents seulement dans l'une des 2 tables en faisant un NOT EXISTS à la place du EXISTS, et à la fin on supprime en une requete tous les éléments qui ont cette colonne vide.

code :

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
21
22
23
24
25
drop table test3;
drop table test4;
CREATE TABLE test3  AS ( SELECT * FROM TAB_ARA);
CREATE TABLE test4  AS ( SELECT * FROM TAB_VIR);
ALTER TABLE test3 ADD keep INT;
ALTER TABLE test4 ADD keep INT;
 
begin
    FOR x IN   (SELECT * FROM test4 WHERE NOT EXISTS 
                (SELECT 1 FROM test3 WHERE a=b) )loop                                
        UPDATE test4 SET keep = 1 WHERE a = x.a;
    end loop;
 
    FOR x IN   (SELECT * FROM test3 WHERE NOT EXISTS 
                (SELECT 1 FROM test4 WHERE a=b) ) loop
        UPDATE test3 SET keep = 1 WHERE b = x.b;
    end loop;   
 
    DELETE FROM test3 where keep IS NULL;
    DELETE FROM test4 where keep IS NULL;
    commit;
end;
/
ALTER TABLE test3 DROP column keep;
ALTER TABLE test4 DROP column keep;

ce qui nous a permis de passer de 4 heures à 40 sec.