Delete de masse

**boutss** · 03/10/2006, 11h08

Bonjour à tous,

Je suis dans un code JAVA et j'effectue des suppressions qui peuvent être dans certains cas, massives, c'est-à-dire environ 500 000 enregistrements.

De plus pour connaître les éléments à supprimer je fais une liaison de toutes les clés primaire dans une table temporaire.

Ex :

Code :

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

1
2
3
4
5
6
7
 
DELETE FROM 
(SELECT A.SOURCE FROM ARTICLE A, TMP_BATCH_EXTRACTION B 
WHERE A.SOURCE=B.ID)
ou
DELETE FROM ARTICLE 
WHERE ID IN (SELECT ID FROM TMP_BATCH_EXTRACTION)

Le problème arrive lorsqu'il y a plus de 200 000 enregistrements çà devient extrèmement long (~10mn).
J'ai essayé de découper les requêtes de suppression en faisant ROWNUM < 100 000 mais cela n'a pas l'air de changer grand chose (j'ai cru).

Alors avez-vous une idée pour pouvoir supprimer des enregistrements en masse ?

En vous remerciant d'avance.

Cordialement
Boutss

**pifor** · 03/10/2006, 11h44

Une solution possible dans http://www.developpez.net/forums/sho...t=delete+masse

**boutss** · 03/10/2006, 14h25

Je l'avais déjà lu, merci.
Mais je ne souhaiterai pas modifier la structure des tables. Du moins, le moins possible, car j'arrive sur le projet et je ne connais pas toutes les contraintes.

Je me demandais s'il y avait un moyen de réduire les coûts surtout qu'au départ çà semble ne pas être gourmand un delete ?? à cause des rollbacks segments ?

**remi4444** · 03/10/2006, 16h14

Oui tu as bien saisi le problème, c'est malheureux mais un delete est plus long qu'un insert, c'est d'ailleurs le gros défaut du système des rollback-segments. Il faut comprendre que Oracle copie les anciens blocs dans ses segments de rollback avant de les modifier les blocs principaux. Lors d'un insert, il n'y a rien dans les anciens blocs, donc rien à sauvegarder (je résume un peu...) alors que dans un delete, il y a des beaucoup de données à copier. Un delete est donc beaucoup plus couteux...

**boutss** · 03/10/2006, 16h24

Merci Remi de confirmer !!

Y'a-t'il un moyen de désactiver l'utilisation de ces rollbacks segments ? qui empècherai forcément le rollback.

Et est-ce que cela servirait à quelque chose ?

Merci

Ps: à savoir que je commande depuis du code JAVA donc désactivation depuis les API java si possible en Oracle.

**LeoAnderson** · 03/10/2006, 17h07

vous ne voulez pas modifier la base mais vous voulez modifier le fonctionnement d'Oracle ???

non, vraiment, je crois que vous ne cherchez pas dans la bonne direction...

**boutss** · 03/10/2006, 17h13

J'ai dit souhaité ... çà veut dire que si c'est la seule solution viable et pas considéré comme une magouille, je la prends...

Donc si je dérive, il faut me remettre dans la bonne direction justement.

Là où j'ai peur dans la modification de la table, c'est que quelqu'un soit au même moment en train de travailler dessus ou que je n'ai pas les droits, voire qu'on m'interdise de toucher à la structure de la base via des instructions JAVA.

Alors, la solution préconisée est-elle la seule viable ?

**remi4444** · 03/10/2006, 18h19

Tu ne peux pas avoir le beurre et l'argent du beurre...

La copie dans les rollbacks permet justement d'avoir une continuité de service parcque pendant que le système purge les blocs de la table, les autres utilisateurs continuent à acceder aux données en étant dérouté dans ces rollbacks, ce qui maintient de leur point de vue des données parfaitement consistentes.

Si tu veux cour-cricuiter les rollbacks, alors tu tolère que pendant un période, les données ne soient pas "propres".

Généralement, la solution dans ces cas là est de faire des tables partitionnées, tu défini un critère de partitionnement (simple...) et tu peut faire un truncate d'une partition à n'importe quel moment. Mais evidement, ça fait pas dans la finesse, c'est gros paquet par gros paquet....

**gregory.broissard** · 03/10/2006, 20h13

juste pour information, quel impératif fonctionnel fait qu'on a besoin de faire des delete de 500000 lignes en une fois !

Ca ressemblerait plus à un traitement batch non?

**petit arbre** · 03/10/2006, 23h06

salut,
t'as qu'à faire ça par petits paquets de 10000 ... avec une boucle

c'est ce que j'ai fait sur des inserts massifs (> 1000 000)

enfin de toute façon, à ce niveau de volume, ça prend du temps !!!

**pifor** · 04/10/2006, 09h00

t'as qu'à faire ça par petits paquets de 10000 ... avec une boucle

Si on fait des INSERT en masse d'accord mais si on fait des DELETE ou des UPDATE en masse et s'il y a des transactions concurrentes qui accèdent aux mêmes données en lecture, il y a un risque d'avoir ORA-1555 si on fait fait des COMMITs par paquets.

**boutss** · 04/10/2006, 09h43

Merci à tous déjà pour vos réponse.

@gregory.broissard : Oui c'est bien un traitement batch !

En fait je suis en train de mettre un mécanisme d'extraction / injection en place. Il sera donc utilisé pour la Purge / Archivage / Transfert de paramétrage.

**orafrance** · 04/10/2006, 09h48

si tu peux trouver un critère simple de DELETE tu peux partitionner ta table. Pour purger il suffira de droper la partition

**Hugues_78** · 10/10/2006, 15h15

C'est quoi le nombre de lignes total de la table ? sa finalité ? (impact du nologging)

Idée 1 ) Dans de nombreux cas, il est plus rapide de faire :

Code :

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

1
2
3
4
5
6
 
create table toto nologging
as select * from ma_table where type_ligne='A SUPPRIMER';
drop table ma_table;
rename toto to ma_table;
create index...

Bien sur que dans un batch, sans transaction concurrente. Il n'y a pas de régle sur le ratio, mais avec un nombre d'index raisonable, on peut dire 30% de suppression.

Le create table as select fait des ajouts en direct path (au dessus de la high water mark) donc très rapide. Si en plus on ne génére pas de redolog...

Si tu supprimes sur une table compressée, c'est quasi obligatoire. Quand tu modifie un bloc (update ou delete) sur une table compressée, Oracle décompresse le bloc en question... Tu as donc une volumétrie qui augmente.

idée 2 ) tu supprime tes index puis tu delete et enfin tu les recrée
Si tu as des bitmap index, c'est quasi obligatoire

**boutss** · 10/10/2006, 15h24

En fait le nombre de ligne n'est pas défini car on peut paramétrer la table que l'on souhaite manipuler.

Tu voulais marquer plutôt ceci ?

Code :

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

type_ligne='A CONSERVER';

**Hugues_78** · 10/10/2006, 15h35

Oui, c'était bien sur "à conserver"...

Mais avant tout, essaye le truc de supprimer les index (ou les marquer inactif) avant toute chose.

Dans tous les cas, même avec un index tout simple, il faut en général reconstruire les index après la suppression de 200K lignes car ces derniers sont deséquilibré. (sauf si la table est vraiement énorme... j'ai un client avec une table de 2To... On s'amuse pas trop sur les index dans ces cas la).

Autre point à prendre en compte : si tu as supprimé un gros pourcentage de lignes, le stockage de ta table est plein de "trou", ce qui fait qu'un select avant et apres le delete prendra a peu de chose prés le même temps.
Tu as la commande alter table ma_table move (ou move compress) qui te permet de remettre les choses d'aplomb.

**orafrance** · 10/10/2006, 15h41

c'est pas les trous mais la HWM qui pose problème dans le cas que tu décris

**Hugues_78** · 10/10/2006, 15h49

La HWM oui. Problématique surtout sur les full table scan a mon avis.

mais tant qu'on a pas fait de nouveaux inserts, je pense qu'on se retrouve dans une situation similaire à un mauvais reglage pctfree/pctused non ?
Si j'ai 4 lignes par blocs, que je supprime 50% de lignes, je me retrouve avec une HWM qui n'a pas bougé, et j'ai maintenant 2 lignes par blocs

Ca m'a toujours un peu g... la parti structure interne du datablock donc je dis peut etre des betises, mais je l'ai compris comme tel...