[DELETE] massif sans generer de redolog

Version imprimable

08/07/2010, 13h35
AlternantOracle

[DELETE] massif sans generer de redolog

Bonjour, j'ai le probleme suivant:

Je dois effacer plus de 300.000 enregistrements dans une table et ca va generer trop de redo logs ( pas assez d'espace disponible ). Alors vous me direz pas grave tu te mets en NO LOGGING et ca passe.

Oui mais voila le probleme si je ne me trompe pas, les redologs passent dans le processe d'archivage et ces archivelog sont envoyees sur la standby qui ne va pas pouvoir recuperer les modifications.

Je me trompe ? avez vous une technique de sioux a me donner ? :p

je suis a cours d'idees
08/07/2010, 13h44
fred_04510

Bonjour,
Peut être créer une table as select (de structure identique, de nom différent) qui contiendrait les enregistrements à garder.
Puis drop ancienne table et renommage de la nouvelle.

Frédéric.
08/07/2010, 13h58
AlternantOracle

L'idee n'est pas mauvaise mais la table a 36 milions de lignes donc le create table as select va etre assez ... consequent niveau espace disque.

Par rapport a notre contract nous n'avons pas le droit de modifier la structure car ca sera une belle breche dans laquelle ils vont s'enfiler si ca ne fonctionne plus.

J'ai envoye un ticket au support je vais vous tenir au courant.
08/07/2010, 14h43
PxPar

dump

Si on reprend son idée de créer une autre table et de supprimer celle qui existe actuellement sauf que cette fois-ci tu fais un dump de ta table à archiver où tu veux , disque cassette ou autres et tu supprimes celle que as actuellement. :roll:
08/07/2010, 20h04
pachot

Bonjour,

Citation:

Envoyé par AlternantOracle

Je dois effacer plus de 300.000 enregistrements dans une table

... dans une table de combien d'enrgistrements en tout ?

Parce que l'import du dump va générer lui du redo log pour les enregistrements à garder.

Et la taille des enregistrements va jouer aussi.Parce que le redo d'un delete, c'est la taille du ROWID seulement, alors que le redo d'un insert, c'est toutes els colonnes de l'enregistrement.

Donc si c'est 300.000 enregistrements parmi 600.000 ce n'est pas sûr que tu génères moins de log.

Pour le 'create as select', c'est un peu mieux. il est en mode direct, donc il ne génère pas d'undo (qui lui aussi est source de redo).

Cordialement,
Franck
09/07/2010, 11h38
Vincent Rogier

Citation:

Envoyé par fred_04510

Bonjour,
Peut être créer une table as select (de structure identique, de nom différent) qui contiendrait les enregistrements à garder.
Puis drop ancienne table et renommage de la nouvelle.

Frédéric.

cela n'est possible que si aucune contrainte référençant cette table n'existent...
09/07/2010, 13h09
pachot

Bonjour,

Citation:

Envoyé par Vincent Rogier

cela n'est possible que si aucune contrainte référençant cette table n'existent...

Dans ce cas, il est possible de désactiver ces contraines puis de les réactiver NOVALIDATE RELY vu qu'on est sur de ne pas avoir perdu de données en route.
Cordialement,
Franck.

Citation:

Envoyé par Vincent Rogier

cela n'est possible que si aucune contrainte référençant cette table n'existent...

De toute façon si la table concernée est une table parent référencée par une contrainte d'intégrité référentielle, on ne peut y supprimer des lignes que si ces lignes ne sont pas référencées dans une table enfant.

On peut faire cette opération DELETE avec un CTAS en supprimant la contrainte sur la table enfant avant renommage de la nouvelle table parent et en recréant la contrainte sur la table enfant pour référencer la nouvelle table. Exemple avec Oracle XE:

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
SQL> create table p(px int primary key, py int check (py > 0), pz varchar2(10) n
ot null);
 
Table created.
 
SQL> create table e(ex int primary key);
 
Table created.
 
SQL> alter table e add constraint efk foreign key(ex) references p;
 
Table altered.
 
SQL>
SQL> insert into p values(1,1,'un');
 
1 row created.
 
SQL> insert into p values(2,2,'deux');
 
1 row created.
 
SQL>
SQL> insert into e values(2);
 
1 row created.
 
SQL>
SQL> commit;
 
Commit complete.
 
SQL>
SQL> create table pp as select * from p where px >=2;
 
Table created.
 
SQL> alter table e drop constraint efk;
 
Table altered.
 
SQL> drop table p;
 
Table dropped.
 
SQL> alter table pp rename to p;
 
Table altered.
 
SQL> alter table p add primary key(px);
 
Table altered.
 
SQL> alter table e add constraint efk foreign key(ex) references p;
 
Table altered.
 
SQL>
SQL> set long 500
SQL> set pagesize 99
SQL> select dbms_metadata.get_ddl('TABLE', 'P', user) as ddl from dual;
 
DDL
--------------------------------------------------------------------------------
 
 
  CREATE TABLE "HR"."P"
   (    "PX" NUMBER(*,0),
        "PY" NUMBER(*,0),
        "PZ" VARCHAR2(10) NOT NULL ENABLE,
         PRIMARY KEY ("PX")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147
 
 
SQL>
SQL>

Il ne faut pas oublier de recréer les contraintes différentes de NOT NULL ainsi que les GRANT et les index.

je ne parlais pas du delete mais du drop.

ta table a purger peut tres bien avoir une PK et d'autres tables des FK avec un cascade delete sur cette PK.

Le delete marche bien sur mais le drop, dans la proposition effectuée, est impossible car des FK référencent la table.
C'est l'objet de ma remarque.
Citation:
Envoyé par pifor
De toute façon si la table concernée est une table parent référencée par une contrainte d'intégrité référentielle, on ne peut y supprimer des lignes que si ces lignes ne sont pas référencées dans une table enfant.

On peut faire cette opération DELETE avec un CTAS en supprimant la contrainte sur la table enfant avant renommage de la nouvelle table parent et en recréant la contrainte sur la table enfant pour référencer la nouvelle table. Exemple avec Oracle XE:
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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 SQL> create table p(px int primary key, py int check (py > 0), pz varchar2(10) n ot null); Table created. SQL> create table e(ex int primary key); Table created. SQL> alter table e add constraint efk foreign key(ex) references p; Table altered. SQL> SQL> insert into p values(1,1,'un'); 1 row created. SQL> insert into p values(2,2,'deux'); 1 row created. SQL> SQL> insert into e values(2); 1 row created. SQL> SQL> commit; Commit complete. SQL> SQL> create table pp as select * from p where px >=2; Table created. SQL> alter table e drop constraint efk; Table altered. SQL> drop table p; Table dropped. SQL> alter table pp rename to p; Table altered. SQL> alter table p add primary key(px); Table altered. SQL> alter table e add constraint efk foreign key(ex) references p; Table altered. SQL> SQL> set long 500 SQL> set pagesize 99 SQL> select dbms_metadata.get_ddl('TABLE', 'P', user) as ddl from dual; DDL -------------------------------------------------------------------------------- CREATE TABLE "HR"."P" ( "PX" NUMBER(*,0), "PY" NUMBER(*,0), "PZ" VARCHAR2(10) NOT NULL ENABLE, PRIMARY KEY ("PX") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147 SQL> SQL>
Il ne faut pas oublier de recréer les contraintes différentes de NOT NULL ainsi que les GRANT et les index.

09/07/2010, 13h34
pifor

Citation:

Envoyé par Vincent Rogier

je ne parlais pas du delete mais du drop.

ta table a purger peut tres bien avoir une PK et d'autres tables des FK avec un cascade delete sur cette PK.

Le delete marche bien sur mais le drop, dans la proposition effectuée, est impossible car des FK référencent la table.
C'est l'objet de ma remarque.

OK mais dans mon exemple je supprime bien cette FK et je la recrée sur la nouvelle table. Ceci doit fonctionner si la table enfant n'est pas en cours d'utilisation.
09/07/2010, 13h36
Vincent Rogier

Citation:

Envoyé par pifor

OK mais dans mon exemple je supprime bien cette FK et je la recrée sur la nouvelle table. Ceci doit fonctionner si la table enfant n'est pas en cours d'utilisation.

ton exemple marche très bien (comme tous tes exemples, d'ailleurs :) :ccool:)

Ma remarque concernait la solution simpliste non détaillée de fred_04510 qui en l'état, n'est à mon sens pas exploitable à 100%.
09/07/2010, 13h38
Vincent Rogier

de plus, la suppression des contraintes sur une base ouvertes à tous en prod, ne serait ce que quelques minutes, n'est à mon sens pas envisageable. Cela ne doit se faire si certaines conditions sont réunies
09/07/2010, 13h39
pifor

OK. Merci :)