commit régulier avec un delete en masse [Résolu]

[kalyparker]

Bonjour à tous,

Voilà j'ai un gros pb avec oracle 9i

Je voudrais faire un delete complet d'une table très volumineuse et commiter tous les 5000 enregistrements.

Avez vous une idée de comment je pourrais faire ?

Merci

[chrifo]

Salut,
Sol 1 : un bon vieux truncate
Sol 2 : Une boucle PL

A+

[kalyparker]

Oaip le bon vieux truncate serais effectivement bienvenue,

mais le pb c'est que je dois obligatoirement avoir des logs de petites tailles car j'ai 2 bdd couplé avec dataguard et que lorsque je fait un truncate il perd les pédale et lorsque je fais un

Code :

DELETE * FROM ma_table

dataguard se plante car il ne peux pas traité autant d'info d'un coup.

Je voulais faire une procédure PL, mais je ne sais pas par où commencer ?!

[orafrance]

CURSOR et BULK COLLECT

[kalyparker]

Merci de ta réponse Fred_D

Ma question va surement te paraître idiote mais c'est quoi un bulk collect ?

J'ai essayer de faire ça, mais ce n'est pas très convainquant...

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
declare
  cpt number := 0; 
  max number;
 
begin
	 SELECT count(*) INTO max FROM ma_table;
   WHILE cpt < max
   LOOP
        DELETE  ma_table WHERE ROWNUM < 5000 ;
         cpt := cpt + 5000;
         commit;
    end loop;
    commit;
end;
/

Si tu penses qu'un bulk collect est necessaire pourrais tu m'en dire plus ?

[orafrance]

utilise la recherche pour trouver des exemples ou lis ce document : http://sheikyerbouti.developpez.com/...=Chap1#L1.2.20

[kalyparker]

Je ne vois pas bien en quoi le bulk collect peux m'aider...
J'ai tester celà :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
Declare
   TYPE TYP_TAB_EMP IS TABLE OF ma_table%Rowtype ;
    Tabemp TYP_TAB_EMP ;
  Begin  
   SELECT
        *
      BULK COLLECT
      INTO
         Tabemp
     FROM 
       ma_table;
     FOR i IN Tabemp.first..Tabemp.last Loop
       DELETE FROM ma_table WHERE cle1=Tabemp(i).cle1 AND cle2=Tabemp(i).cle2;
	   commit;
     End loop;
   End ;
   /

Dans ce code je fait un commit tout les enregistrements et Oracle me jette par manque de mémoire...
En plus la clé primaire de ma table est composé ce qui m'oblige à faire la jointure sur 2 champs.

Je suis perdue...

J'ai vu sur internet qu'il existerais quelque chose comme
ma_table.DELETE(val1, val2);
qui permet de supprimer les enregistrements de la ligne val1 à la ligne val2.

En savez vous plus ?

[Yorglaa]

regarde à nouveau la doc que t'a donné Fred_D !

la boucle où tu fais le delete ne doit pas être un FOR... mais FORALL (sans End Loop)

[kalyparker]

Alors voila je crois que je me suis bien battu et que j'ai finalement gagné !
J'ai laissé tomber le bulk collect pour quelque chose de plus simple :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
declare
  v_pas number; 
  v_count number;
begin
LOOP
   v_pas := 5000;
 
   DELETE FROM ma_table WHERE rownum < v_pas;   
   commit; 
   SELECT count(*) INTO v_count FROM ma_table;
 
IF v_count = 0 then
   exit;
end IF;
 
END LOOP;
end;

Ce n'est peut être pas la meilleure solution, mais au moins elle marche !
Merci à vous de votre aide.

[Wurlitzer]

Le bulk collect pourrait t'aider si tu avais besoin de faire un select pour trouver les enregistrements à supprimer. Dans ton cas la condition est directement dans le delete. C'est donc plus simple.

Ta solution est correcte. Je te propose l'optimisation suivante qui évite de faire un count et donc potentiellement un FULL SCAN a chaque itération de boucle. Ce qui peut change beaucoup de chose du point de vue des perfs

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
declare
  v_pas number; 
  p_nb_delete number;
begin
LOOP
   v_pas := 5000;
 
   DELETE FROM ma_table WHERE rownum <= v_pas;   
   p_nb_delete := SQL%ROWCOUNT;   
   commit; 
    
IF p_nb_delete < v_pas then
   exit;
end IF;
 
END LOOP;
end;

[orafrance]

et pourquoi pas un WHILE ROWCOUNT>0 ?

[kalyparker]

Merci pour ces précieux conseils.
Effectivement Wurlitzer, ta solution est beaucoup moins gourmandes que la mienne.
Quand à ton optimisation Fred_D, juste par curiosité qu'est ce que cela m'apporte ? La structure loop exit est-elle plus gourmande que while loop ?

[orafrance]

c'est juste moins compliqué

[Wurlitzer]

Je vois deux raisons pour ne pas utiliser le WHILE ROWCOUNT>0 :

• La première (assez mauvaise ), j'y avais pas pensé.....
  
• La seconde, est que plus loin dans mon code j'additionne les p_nb_delete pour afficher le nombre de lignes totales supprimées

Sinon, je suis d'accord que c'est plus élégant

[orafrance]

pour la seconde SQLROWCOUNT + ((nb_boucle - 1) * v_pas)

[Wurlitzer]

La nuit portant conseil et juste pour le plaisir de chipoter un peu

• Si on doit compter le nombre de boucles on peut compter les lignes directement
• Avec WHILE ROWCOUNT>0 tu es moins optimisé que car tu fais presque toujours un DELETE de trop (puisque que le dernier DELETE est presque toujours un nombre de ligne inferieur a w_pas mais > 0). Il faudrait ecrire WHILE ROWCOUNT = v_pas pour eviter ca

[kalyparker]

Pour l'optimisation, vous êtes efficaces dans votre genre !

Wurlitzer, ta dernière solution me laisse perplexe.
Parce que si je fait un while rowcount=v_pas alors justement je ne passe pas dans la boucle pour le dernier delete et donc lorsque je sort de ma boucle il me reste encore des enregistrements dans ma table, non ?

Je vais rester sur la solution de Fred_D.

[orafrance]

si c'est bon, l'avant dernière boucle fera un rowcount = v_pas, du coup tu passes le WHILE pour faire ce qui sera la dernière boucle

Bravo Wurlitzer

[kalyparker]

Oh la la, honte sur moi

effectivement, vous avez raison, je n'avais pas bien saisi le but de la manoeuvre et pis c'etait le matin alors...
Non, je sais que je n'ai pas d'excuse alors je m'arrete...

A nouveau merci à vous et

++