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

[shadeoner]

Hello,

j'ai exactement le même problème que toi.

J'étais parti sur une proc qui créé une table temporaire transvase avec un select mes lignes vers cette table, renomme l'ancienne table puis renomme la temporaire avec le nom d'origine. Je termine pas un DROP de ma table tampon.

Ce qui me dérange c'est que ma table serait indisponible le temps du SWAP des tables.
Je n'ai pas testé le EXECUTE immédiate.

Ce qui est long sur un DELETE en dur est la création du rollback segment, je me demandais si on forçait le process en autocommit cela n'irait pas mieux ?

[shadeoner]

J'ai lu le lien que tu as fournis il est en effet très sympa, je vais essayé cela.

[boutss]

J'effectue aussi quelques tests et vous tiens informé.

[kervoaz]

Alors la effectivement un lien très intéressant. Ça remet en cause des trucs que je tenais pour sûr.
Je vais approfondir car l'article ne donne pas d'explication sur le pourquoi des différences ?

Cependant hormis l'elapsed time, toutes les valeurs mesurées sont supérieures.

Cdt

[shadeoner]

ça donne quoi de votre côté ?

[kervoaz]

bonsoir,

en faisant une trace on voit que la requete sql executée est la meme entre l'execute immediate et le delete classique. De plus dans la trace(je peux la poster si ca interesse qqun) les 2 requetes ont des executions similaires.
L'execute immediate introduit du travail suppplementaire pour le serveur, confirmé par les mesures du lien.

Bref je ne comprends pas cette difference de temps SAUF si l'elapsed time relevé depend de la charge globale du serveur(autre traitement en cours pendant les mesures)....

J'ai essayé de contacter l'auteur pour plus de renseignements

Cdt

[jmguiche]

Ce papier me semble plus qu'étonnant.
Si quelqu'un arrive a démontrer qu'une requette en execute immédiate est plus rapide que toute simple, j'aurais appris qqchose.
D'apres moi, les 2 traitements (le premier et le dernier) sont identiques en temps : vue le temps de traitement, le surcout de l'execute immediat est négligeable.

Il est assez probable que le test ait été fait "dans l'ordre", avec des rollback au milieu. Et dans ce cas, le premier delete tout simple fait tout les io pour charger en SGA et les autres traitements en profitent.

The best result was using EXECUTE_IMMEDIATE calls inside an anonymous PL/SQL block, during test case 5. This is not surprising, since Oracle suggests this method for mass data deletion.

Ou ça ? Moi, tout les papiers que j'ai lu insistent sur le surcout de l'execute immediate.

[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 ?

[orafrance]

faudra éventuellement revoir la taille du TEMP

[kervoaz]

Bon ben l'adresse de l'auteur n'est plus valide....Apres la redaction de cette article sa boite mail a du exploser

On est tous d'accord pour dire que le bonhomme s'est loupé dans ses mesures??

[pifor]

Cela semble le plus probable. A moins que quelqu'un arrive à poster un test reproductible dans une configuration précise (machine, version du système, config. du système, version d'Oracle et config. de l'instance).

[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.