Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Delete de masse (suite)


Sujet :

SQL Oracle

  1. #1
    Membre habitué
    Delete de masse (suite)
    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

  2. #2
    Membre éclairé
    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).
    Philippe CEROU,

    Architecte Systèmes & Bases de données.

  3. #3
    Membre habitué
    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 3Delete 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;
    /

  4. #4
    Membre habitué
    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.

  5. #5
    Membre habitué
    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

  6. #6
    Membre habitué
    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

  7. #7
    Membre régulier
    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 ?
    www.ou-dejeuner.com A chacun son déjeuner ! |[URL="http://www.blackprism.org]Blackprism.org[/URL]

  8. #8
    Membre régulier
    J'ai lu le lien que tu as fournis il est en effet très sympa, je vais essayé cela.
    www.ou-dejeuner.com A chacun son déjeuner ! |[URL="http://www.blackprism.org]Blackprism.org[/URL]

  9. #9
    Membre habitué
    J'effectue aussi quelques tests et vous tiens informé.

  10. #10
    Membre habitué
    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

  11. #11
    Membre régulier
    ça donne quoi de votre côté ?
    www.ou-dejeuner.com A chacun son déjeuner ! |[URL="http://www.blackprism.org]Blackprism.org[/URL]

  12. #12
    Membre habitué
    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

  13. #13
    Membre averti
    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.

  14. #14
    Expert éminent sénior
    Citation Envoyé par boutss Voir le message
    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;

  15. #15
    Membre habitué
    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 ?

  16. #16
    Expert éminent sénior
    faudra éventuellement revoir la taille du TEMP

  17. #17
    Membre habitué
    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??

  18. #18
    Membre expert
    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).

  19. #19
    Expert éminent sénior
    Citation Envoyé par boutss Voir le message
    ...

    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!

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