Comment faire un commit toutes les 10.000 transactions ? [Résolu]

[7gyY9w1ZY6ySRgPeaefZ]

Bonjour.

Je me retrouve confronté à un problème avec un bête delete dans une base oracle 9.2.0.8 :

Code :

DELETE FROM TABLE_MACHIN WHERE DATE_MOD < sysdate - 100 ;

Pour faire court, j'utilise Stream et ce delete lance une création d'Archive Logs pénalisante en place (une bonne dizaine de Gigas) et donc en temps.
On a remarqué (dans le curseur d'un autre programme plus complexe) qu'en faisant un commit toutes les 10.000 transactions, on s'affranchissait de ce problème.

Ma question :
Existe-t-il un moyen simple d'imposer un commit tous les 10.000 trasactions en faisant quelque chose de plus propre qu'une boucle avec curseur et compteur ?

Merci.

[LeoAnderson]

X lignes à supprimer = X entrées UNDO + X entrées REDO = Y To d'archivelog

et ce, quelque soit la fréquence des commits, ou qu'il y ait ou pas un commit d'ailleurs !

Faire des commits périodiques permettra juste de marquer comme réutilisables des entrées d'UNDO et donc nécessitera moins de place en UNDO, mais c'est tout.

[7gyY9w1ZY6ySRgPeaefZ]

Mon problème vient du stream entre mes 2 bds.
La version sous 9i2 provoque pléthore de transferts de données entre les bases - même quand des données n'ont pas à être utilisé sur le serveur cible...
Et lorsque l'on fait plus de 10.000 transactions (environ... source : test maison... ), on arrive à un équilibre entre le transfert des données et les créations de fichiers.
Sinon, c'est l'engorgement et le serveur cible se noie en créant un décalage qui a mis 24 h pour se résorber au dernier test...

Donc, Léo, je ne doute pas du bien fondé de tes dires mais mon problème est ailleurs, avec cet obscur stream.

De plus, ce sont des tables plutôt petites (qq 100.000 lignes tout au plus) donc le problème ne vient pas de la place manquante.
Dans le même genre de problème, un

Code :

CREATE TABLE TOTO AS SELECT * FROM MACHIN

provoque aussi un engorgement avec une table de 50.000 lignes.

[LeoAnderson]

dans ce cas, à votre question

Ma question :
Existe-t-il un moyen simple d'imposer un commit tous les 10.000 trasactions en faisant quelque chose de plus propre qu'une boucle avec curseur et compteur ?

il n'y a pas d'autre réponse que non.

mais y'a quand même un truc qui m'échappe...

Code :

DELETE FROM TABLE_MACHIN WHERE DATE_MOD < sysdate - 100 ;

ça ne fait qu'une seule transaction !

par ailleurs, y-a-t-il des triggers qui pourraient provoquer des opérations supplémentaires ?

[7gyY9w1ZY6ySRgPeaefZ]

Envoyé par LeoAnderson

mais y'a quand même un truc qui m'échappe...

Code :

DELETE FROM TABLE_MACHIN WHERE DATE_MOD < sysdate - 100 ;

ça ne fait qu'une seule transaction !

Euh effectivement, j'ai un problème de vocabulaire ! Je voudrais limité une transaction à 10.000 "modifications". Je ne sais pas si c'est beaucoup plus clair...

Envoyé par LeoAnderson

par ailleurs, y-a-t-il des triggers qui pourraient provoquer des opérations supplémentaires ?

C'est juste une table d'audit qui n'est relié à rien et où l'on ne fait que des inserts.
En fait, il y a juste un trigger "Before Each Row" pour incrémenter la séquence de l'id de l'enregistrement.

Je lance cette ligne de code par du SQL dynamique (je fais ça sur plusieurs tables et je fais un commit après chaque delete) mais je ne crois pas que cela ait une influence sur mon problème puisque dans une autre procédure je lance du sql dynamique (pour supprimmer un seul enregistrement) mais avec un commit tous les 10.000 modifs. Et ça roule bien, celui-là.

[7gyY9w1ZY6ySRgPeaefZ]

Bon ben il se trouve que mon client m'a raconté des bétises...

C'est sûr, s'il utilise seulement la moitié des programmes qu'il me demande de modifier...

[DAB.cz]

Si je comprends bien:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
CREATE TABLE tst (a number, d date);
 
INSERT INTO tst (a, d)
  SELECT level, sysdate - 100 + level/10000
    FROM dual
    connect BY
    level <= 100000;
commit;
 
variable i number
begin
  :i := 0;
  loop
    DELETE
      FROM tst
      WHERE d <= to_date ('01.11.2007', 'dd.mm.yyyy') AND rownum <= 100;
    exit when sql%rowcount < 100;
    commit;
    :i := :i + 1;
  end loop;
end;
/
 
print i

Dans votre cas:

Code :

1
2
3
4
5
6
7
begin
  loop
    DELETE FROM TABLE_MACHIN WHERE DATE_MOD < sysdate - 100 AND rownum <= 10000;
    exit when sql%rowcount < 10000;
    commit;
  end loop;
end;

DAB

[7gyY9w1ZY6ySRgPeaefZ]

Merci, j'avais fait un truc analogue, mais de toute façon, ce n'est plus utile maintenant.
Note : j'utilise du sql dynamique parce que je fais le même traitement sur une poignée de tables.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE OR REPLACE
PROCEDURE AAAA
IS
	DATE_LIMITE	DATE;
	NOM_TABLE	VARCHAR2(30);
	CODE_DELETE	VARCHAR2(400);
        COUNT_SELECT   VARCHAR2(400);
        NUMB_LOOP       NUMBER := 0 ;
        MAX_LIGNES_SUPPRIM NUMBER;
 
BEGIN
	DATE_LIMITE := sysdate - 100 ;
        NOM_TABLE := 'MACHIN';
        MAX_LIGNES_SUPPRIM := 10000;
 
        COUNT_SELECT := 'SELECT COUNT(*) FROM '|| NOM_TABLE ||' WHERE LASTMODDATE < ''' || DATE_LIMITE || '''' ;
        EXECUTE IMMEDIATE COUNT_SELECT INTO NUMB_LOOP ;
        IF NUMB_LOOP > 0 then 
          NUMB_LOOP := trunc ( NUMB_LOOP / MAX_LIGNES_SUPPRIM ) + 1 ;
        END IF;
 
        FOR k IN 1 .. NUMB_LOOP loop
          CODE_DELETE := 'DELETE ' || NOM_TABLE || ' WHERE LASTMODDATE < ''' || DATE_LIMITE || ''' AND ROWNUM <= ' || MAX_LIGNES_SUPPRIM  ;
          EXECUTE IMMEDIATE CODE_DELETE ;
          commit;
        end loop;
 
END AAAA ;