Discussion :

[boutss]

Bonjour,

J'ai un problème de performance concernant la suppression de masse.

Il existe déjà un fil de discussion : http://www.developpez.net/forums/sho...d.php?t=218129

Je suis en 9i mais je vais bientôt passer en 10g.

J'ai environ 3'500'000 enregistrements à supprimer.
Je ne peux pas faire de partition et recréer la table serait beaucoup trop couteux.

Ma requête dépasse allègrement les 2 heures ?? (elle n'est pas terminée)

Existe-il un moyen de delete sans utiliser les roolbacks segments ou une autre astuce comme un truncate par exemple ?

Exemple avec 2'000'000

Code :

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

1
2
3
insert into TMP_BATCH_EXTRACTION (ID) (select ID from TARTICLE where etatobjet=1)
 
delete from TARTICLE A where ID in (select B.ID from TMP_BATCH_EXTRACTION B)

Explain plan du delete

Code :

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

1
2
3
4
5
6
7
8
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
 
DELETE STATEMENT Optimizer Mode=CHOOSE		8 K	 	16446  	 	      	             	 
  DELETE	HERMES_USER.TARTICLE	  	 	 	 	      	             	 
    NESTED LOOPS		8 K	1 M	16446  	 	      	             	 
      SORT UNIQUE		  	 	 	 	      	             	 
        TABLE ACCESS FULL	HERMES_USER.TMP_BATCH_EXTRACTION	8 K	494 K	11  	 	      	             	 
      INDEX UNIQUE SCAN	HERMES_USER.PK_TARTICLE	1  	74  	1

ce que j'ai trouvé : http://www.dbasupport.com/oracle/ora...alData02.shtml

En vous remerciant
Boutss

[philcero]

Normalement pour le DELETE massifs, on utilise un code PL/SQL qui va faire des traitements par tranche de N lignes (1000 généralement).

[boutss]

Mais encore ?

Tu gagnes beaucoup en performance ?

Comme ceci ? :

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
Test Case 3 – Delete using anonymous PL/SQL block, looping through the cursor, and deleting one tow at a time. 
Loop has counter, programmed to trigger commit after each 1000 records.
 
SQL> declare
        rec_read       number(6)    := 0;
        rec_rest       number(6)    := 0; 
        vc_var_out   varchar2(41) := 'Delete Archive Data';
        cursor rec_sql is select SECOND_COLL,rowid from artist_test 
			where SECOND_COLL='JAVA CLASS' order by SECOND_COLL,rowid;
begin
       for rec_sql_run in rec_sql loop
            rec_read := rec_read + 1 ;
            rec_rest := rec_rest + 1 ;
            delete from artist_test A where A.rowid = rec_sql_run.rowid ;
            if rec_rest > 1000 THEN
                 COMMIT;
                 rec_rest := 1;
                 dbms_output.put_line('COMMIT AT..'|| rec_read);
             END IF;
        end loop;
end;
/

[boutss]

J'ai utilisé un execute immédiate avec un client oracle (toad) et j'ai l'impression que c'est plsu rapide comme l'indique le lien que j'ai fourni.

La seule chose, c'est que j'ai créé une procédure stocké pour l'exécuter et là j'ai l'impression qu'on retourne à des temps plus long.

Existe-t-il un moyen d'exécuter en pseudo natif depuis la JDBC ?
Un callable statement est plus rapide qu'un statement dans mon cas ?

Merci de votre aide.
Boutss.

[kervoaz]

Bonjour,

Sur la requête donnée en exemple:

Code :

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

1
2
3
INSERT INTO TMP_BATCH_EXTRACTION (ID) (SELECT ID FROM TARTICLE WHERE etatobjet=1)
 
DELETE FROM TARTICLE A WHERE ID IN (SELECT B.ID FROM TMP_BATCH_EXTRACTION B)

Si le delete efface la totalité de la table c'est TRUNCATE que tu dois utiliser.

Sinon
pour récupérer les rowid, tu peux(dois) utiliser BULK COLLECT pour charger les données dans une ou plusieurs collections(faire attention de mettre une clause LIMIT).
Ensuite pour le delete tu te sers de FORALL qui va traiter toute la collection précédemment récupérée en un seul coup.
Il y a beaucoup d'exemple d'utilisation et le traitement en masse est extrêmement performant.

Cdt

[boutss]

Merci de ta réponse.

Alors non ce n'est pas une suppression complète de la table, car comme tu l'as dit, dans ce cas là, il faut utiliser le TRUNCATE.

Pourrais-tu me donner un exemple plus concret de ce que tu proposes ?

Car regarde sur ce lien :
http://www.dbasupport.com/oracle/ora...alData02.shtml

Ils disent que l'exécute immédiate est préconisé pour les suppressions en masse ?

Merci. Boutss

[orafrance]

J'ai utilisé un execute immédiate avec un client oracle (toad) et j'ai l'impression que c'est plsu rapide comme l'indique le lien que j'ai fourni.

c'est pas le EXECUTE IMMEDIATE qui est plus rapide mais la manière dont sont gérés les rollbacks (commit toutes les 1000 lignes) et/ou les collections (FORALL).

Le SQL dynamique n'est pas plus rapide que le SQL

Au lieu de

Code :

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

1
2
3
INSERT INTO TMP_BATCH_EXTRACTION (ID) (SELECT ID FROM TARTICLE WHERE etatobjet=1)
 
DELETE FROM TARTICLE A WHERE ID IN (SELECT B.ID FROM TMP_BATCH_EXTRACTION B)

Essaye :

Code :

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

1
2
3
4
5
6
7
8
9
 
CREATE TEMPORARY TABLE tarticle_save AS
SELECT * FROM tarticle WHERE etatobjet <> 1;
 
TRUNCATE TABLE tarticle;
 
INSERT INTO tarticle SELECT * FROM tarticle_save;
 
DROP TABLE tarticle_save;

[boutss]

Oui j'ai refait des tests et rien de très probant.

Merci je vais essayer cette solution.

Mais je suis à plus de 5M d'enregistrements à manipuler ce n'est pas gênant ?

[mnitu]

...

Exemple avec 2'000'000

Code :

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

1
2
3
insert into TMP_BATCH_EXTRACTION (ID) (select ID from TARTICLE where etatobjet=1)
 
delete from TARTICLE A where ID in (select B.ID from TMP_BATCH_EXTRACTION B)

...

Cette démarche est erronée. La démarche correcte est :

Code :

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

1
2
 
DELETE FROM TARTICLE A Where etatobjet=1

Toute autre solution (PL/SQL, bulk, commit par lot, etc.) coûte plus cher!

[boutss]

Mais si j'utilise une table temporaire c'est que j'ai un traitement intermédiaire.

[mnitu]

Mais si j'utilise une table temporaire c'est que j'ai un traitement intermédiaire.

En 99% des cases la table temporaire est la pour plomber les performances (ce que ton exemple démontre déjà).

[orafrance]

le but inital était de pouvoir faire des commits intermédiaires si je ne m'abuse

[mnitu]

le but inital était de pouvoir faire des commits intermédiaires si je ne m'abuse

Le but initial est

J'ai un problème de performance concernant la suppression de masse.

Vu que en fait au lieu de supprimer le 3 millions enregistrements le traitement copie les 3 millions et ensuite les supprime ça ne m’étonne pas qu’il a des problèmes de performance.