Optimisation DELETE

**poups** · 16/05/2006, 18h23

Oracle Server 9.2
Je souhaite effectuer un DELETE important sur une BDD en production au sein d'une table contenant près de 20 millions d'enregistrements.
Le problème est que cette opération prend un temps extrêmement important et conduit à l'écroulement des performances de la BDD pour les utilisateurs.
Nous avons essayer d'effectuer l'opération en supprimant temporairement les contraintes sur la table concernée mais cela n'a rien changé.
La base est en mode NOARCHIVELOG.
Quelqu'un aurait-il une idée pour améliorer le temps d'exécution de la requête?
Merci

**orafrance** · 16/05/2006, 18h36

combien de lignes supprimes-tu dans les 20 Millions ?

Un méthode interessante est:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE tab_new
AS SELECT * FROM tab_a_supprimer
WHERE <conditions de sauvegarde>;
 
RENAME TABLE tab_a_supprimer TO tab_sav;
 
RENAME TABLE tab_new TO  tab_a_supprimer;
 
recréation des contraintes, triggers, indexes et grant
 
DROP TABLE tab_sav; -- quand tout est OK

Si tu ne peux pas te passer de la table même temporairement où que c'est trop contraignant, alors tu peux également supprimer les indexes et triggers avant de supprimer les lignes.

**dyvim** · 16/05/2006, 20h52

Si tu ne peux pas appliquer la solution de Fred_D (tout le monde ne peux pas créer deux tables de 20 millions de lignes) et que tu veux que l'on t'aide, tu devrais:
- nous donner des informations sur la table que tu veux supprimer (colonnes, clés, index, triggers, partitions,...)
- nous donner des infos sur ton DELETE (requete précise, plan d'exécution, temps d'éxécution,...)

A bon entendeur

**Mathusalem** · 17/05/2006, 10h05

est ce que la méthode de fred_d est vraiment intéressante, si on doit supprimer moins de 50% de la table ?

j'ai en effet le même soucis avec une table, ou je dois retirer de temps à autre 3 millions de tuples sur un peu plus de 9.

**orafrance** · 17/05/2006, 10h31

là, il n'y a pas d'autres solutions que tester la méthode... je ne vois pas comment savoir si c'est mieux ou pire qu'un DELETE sans le vérifier

Parce que pour optimiser un DELETE, je ne vois qu'un autre moyen, c'est de supprimer les indexes avant et les recréer ensuite.

Donc :
1°) méthode :
- insert volume - 3 millions de ligne et create index

2°) méthode
- delete 3 millions de ligne et create index

sachant que la 1° méthode ne consomme pas d'UNDO... donc plus de volume mais pas d'UNDO contre moindre volume avec UNDO... faut voir.

Peut-on me confirmer par ailleurs que l'INSERT n'utilise pas le UNDO... parce que j'en suis pas certain

**Mathusalem** · 17/05/2006, 13h57

Envoyé par Fred_D

[...] Parce que pour optimiser un DELETE, je ne vois qu'un autre moyen, c'est de supprimer les indexes avant et les recréer ensuite.
[...]

En quoi le fait de retirer les indexes peut accélérer un DELETE ?

De plus, je peux me tromper, mais il me semble que Oracle crée automatiquement un index sur les clés primaires, non ?

Ce qui voudrait dire qu'il faudrait aussi faire sauter la clé ?

**macben** · 17/05/2006, 14h26

Envoyé par Mathusalem

En quoi le fait de retirer les indexes peut accélérer un DELETE ?

Moi c'est plus pour ma culture perso, mais en quoi le fait de retirer LES triggers peut accélérer un Delete ? De quel triggers parles-tu ? Les triggers genre ON INSERT, ON UPDATE, ON DELETE, ou d'autres (donc je ne connaitrais pas l'existence) ?

**orafrance** · 17/05/2006, 14h56

les triggers ON DELETE bien entendu

Il faut supprimer les indexes pour éviter de perdre du temps à les mettre à jour pendant le traitement de suppression. Il peut être plus avantageux de le recréer

**dyvim** · 17/05/2006, 14h58

Envoyé par Mathusalem

En quoi le fait de retirer les indexes peut accélérer un DELETE ?

De plus, je peux me tromper, mais il me semble que Oracle crée automatiquement un index sur les clés primaires, non ?

Ce qui voudrait dire qu'il faudrait aussi faire sauter la clé ?

Un trigger on DELETE sera joué chaque fois que tu supprimera des lignes donc si tu désactive le TRIGGER tu accélère le process puisque cela fait du code en moins à éxécuter.
De la même façon si tu supprimes les clés et les contraintes tu gagnes du temps.
Pour les index tu gagnes le temps nécessaire pour les mettre à jour.

Concernant les index de clef primaire, tu peux les supprimer sans supprimer la clé.

**macben** · 17/05/2006, 15h00

Envoyé par Fred_D

les triggers ON DELETE bien entendu

Ah ok... moi je trouve ça dommage de supprimer ON DELETE au moment de faire un ... DELETE

(Ok j'ai compris que c'est dans le cas où le ON DELETE ne doit s'appliquer que pour un DELETE "non-purge" mais un DELETE applicatif).

**dyvim** · 17/05/2006, 15h15

Attention, si tu désactive les contraintes, triggers,... tu t'expose à des problèmes d'intégrité.

Assure toi que tu seras le seul à utiliser la table à ce moment là...

Peut être que tu peux le faire en une seule transaction spécifiée dans un bloc PL, mais je me demandes ce qu'il advient des ordres en EXECUTE IMMEDIATE.

**poups** · 17/05/2006, 19h08

Tout d'abord, merci à tous pour vos réponses.

Pour répondre aux diverses questions:
- l'objectif dans un premier temps est de purger près de 80% de la table... puis de la purger régulièrement au fur et à mesure de son incrémentation.

- ta solution fred peut être envisageable mais elle est effectivement contraignante : la base est rattachée à un progiciel pour lequel nous avons un support, mais malheureusement trop incompétent pour nous fournir une solution appropriée à notre problème. Et toucher à la structure de la base est par conséquent un peu délicat. m'enfin si on a pas le choix...

- la structure de la table est la suivante :

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
 
CREATE TABLE "ODB"."" ("AC" VARCHAR2(10 byte) NOT NULL, 
    "FLIGHT_LOG" VARCHAR2(12 byte) NOT NULL, "FLIGHT_LEG" 
    NUMBER(4) NOT NULL, "FLIGHT_DATE" DATE, "ORIGIN" VARCHAR2(4 
    byte), "DESTINATION" VARCHAR2(4 byte), "FLIGHT" VARCHAR2(10 
    byte), "STATUS" VARCHAR2(10 byte), "OFF_HOUR" NUMBER(2), 
    "OFF_MINUTE" NUMBER(2), "TO_HOUR" NUMBER(2), "TO_MINUTE" 
    NUMBER(2), "LDG_HOUR" NUMBER(2), "LDG_MINUTE" NUMBER(2), 
    "ON_HOUR" NUMBER(2), "ON_MINUTE" NUMBER(2), "FLIGHT_HOURS" 
    NUMBER(6), "FLIGHT_MINUTES" NUMBER(2), "CYCLES" NUMBER(6), 
    "BLOCK_HOURS" NUMBER(6), "BLOCK_MINUTES" NUMBER(2), 
    "TOTAL_AC_FLIGHT_HOURS" NUMBER(6), "TOTAL_AC_FLIGHT_MINUTES" 
    NUMBER(2), "TOTAL_AC_CYCLES" NUMBER(6), 
    "TOTAL_AC_BRAKE_CYCLES" NUMBER(6), "TOTAL_AC_BLOCK_HOURS" 
    NUMBER(6), "TOTAL_AC_BLOCK_MINUTES" NUMBER(2), 
    "FLIGHT_CATEGORY" VARCHAR2(8 byte), "FUEL_BURNED" NUMBER(5), 
    "NOTES" NUMBER(8), "CREATED_BY" VARCHAR2(10 byte), 
    "CREATED_DATE" DATE NOT NULL, "MODIFIED_BY" VARCHAR2(10 byte),
    "MODIFIED_DATE" DATE NOT NULL, "VOID" VARCHAR2(10 byte), 
    "PILOT_FLIGHT_HOURS" NUMBER(6), "PILOT_FLIGHT_MINUTES" 
    NUMBER(2), "UPDATE_MODE" VARCHAR2(10 byte), "BLOB_NO" 
    NUMBER(8), "ETOPS_FLIGHT" VARCHAR2(10 byte), 
    "AC_MAINTENANCE_RESET" VARCHAR2(10 byte), "PILOT_CYCLES" 
    NUMBER(6), "CAT_RATING" VARCHAR2(10 byte), "MAINTENANCE_LOG" 
    VARCHAR2(10 byte), "FLIGHT_HOURS_AT_CLOSING" NUMBER(6), 
    "FLIGHT_MINUTES_AT_CLOSING" NUMBER(2), "CYCLES_AT_CLOSING" 
    NUMBER(6), "CAT_TYPE" VARCHAR2(2 byte), "CAT_RESULT" 
    VARCHAR2(2 byte), 
    CONSTRAINT "FK_AC_ACTUAL_FLIGHT_AUDIT_1" FOREIGN KEY("AC") 
    REFERENCES "ODB"."AC_MASTER"("AC") 
    ON DELETE CASCADE, 
    CONSTRAINT "P_AC_ACTUAL_FLIGHTS_AUDIT_1" PRIMARY KEY("AC", 
    "FLIGHT_LOG", "FLIGHT_LEG", "CREATED_DATE", "MODIFIED_DATE") 
    USING INDEX  
    TABLESPACE "USR" 
    STORAGE ( INITIAL 1215712K NEXT 0K MINEXTENTS 1 MAXEXTENTS 
    2147483645 PCTINCREASE 0) PCTFREE 10 INITRANS 2 MAXTRANS 255)
 
    TABLESPACE "USR" PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
    STORAGE ( INITIAL 2097152K NEXT 0K MINEXTENTS 1 MAXEXTENTS 
    2147483645 PCTINCREASE 0) 
    LOGGING

et d'ailleurs je viens de me rendre compte qu'il y a un trigger sur DELETE... que signifie CASCADE?

- le DELETE est très simple : le but est de purger les enregistrements antérieurs à une date donnée (champ FLIGHT_DATE). cela donne quelque chose du type :

Code :

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

1
2
3
 
DELETE FROM ODB.AC_ACTUAL_FLIGHTS_AUDIT
WHERE FLIGHT_DATE<='01-FEB-03'

J'avais essayer de "décomposer" la suppression, en lançant une requête quotidienne la nuit du type :

Code :

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

1
2
3
4
5
6
7
 
DELETE FROM(
SELECT ROWID FROM
(SELECT *
FROM ODB.AC_ACTUAL_FLIGHTS_AUDIT
ORDER BY FLIGHT_DATE)
WHERE ROWNUM<1000);

qui devrait me permettre de purger petit à petit la table sans m'en préoccuper, mais le problème est que ROWNUM ne semble pas fonctionner avec DELETE : les enregistrement supprimés ne correspondent pas aux plus petits ROWNUM, cad aux dates les plus petites. Comprend pas..

**Yorglaa** · 17/05/2006, 21h00

et que donne juste un Select avec la même clause que le Delete ?

Code :

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

1
2
Select * from Actual_Flight_Audit
WHERE FLIGHT_DATE<='01-FEB-03'

au niveau du plan d'execution ? est-ce que ça passe par un index ?

mon but est de savoir si la perte de temps provient de la selection des rows à effacer ou du delete lui-même...

**dyvim** · 17/05/2006, 21h55

Est ce qu'il ne s'agirait pas de purger un mois entier par exemple?

Parce que dans ce cas là une table partitionnée avec des DELETE qui mentionnent les partitions (voire même des suppressions de partitions entières) te permettrait de gagner du temps.
Il faut partitionner par valeur (BY RANGE) en définissant des valeurs délimitants les partitions.

Sinon une bonne question est : as tu un index sur ta colonne FLIGHT_DATE?

**Research Am** · 17/05/2006, 22h36

Pour épurer des données dans le temps comme dans un entrepôts de données. Je partionne mes tables de faits, donc j'ajoute une nouvelle partition a chaque mois et je supprime toujours le plus vieux mois ce qui fait que j'ai toujours 13 mois d'historique. De plus la suppression d'une partition est très rapide (environ 3 secondes pour 8 millions d'enregistrements), mais ne permet pas au Triggrer de ON DELETE d'effectuer leur travail.

Research

**dyvim** · 17/05/2006, 22h52

Envoyé par poups

et d'ailleurs je viens de me rendre compte qu'il y a un trigger sur DELETE... que signifie CASCADE?

Il ne s'agit pas d'un TRIGGER mais d'une contrainte d'intégrité(à moins que tu ais un TRIGGER mais tu ne l'as pas montré).
Visiblement tu références une autre table via cette clef étrangère:

Code :

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

CONSTRAINT "FK_AC_ACTUAL_FLIGHT_AUDIT_1" FOREIGN KEY("AC") REFERENCES "ODB"."AC_MASTER"("AC") ON DELETE CASCADE,

le terme CASCADE signifie que lorsque tu supprime des lignes sur la table mère, les lignes de la table fille sont supprimées aussi.

**niiccoo** · 18/05/2006, 11h58

Envoyé par Research Am

Pour épurer des données dans le temps comme dans un entrepôts de données. Je partionne mes tables de faits, donc j'ajoute une nouvelle partition a chaque mois et je supprime toujours le plus vieux mois ce qui fait que j'ai toujours 13 mois d'historique. De plus la suppression d'une partition est très rapide (environ 3 secondes pour 8 millions d'enregistrements), mais ne permet pas au Triggrer de ON DELETE d'effectuer leur travail.

Research

Salut Research Am, pourrait tu m'en dire s'il te plait ? je suis dans un cas de suppression d'un grand nombre d'enregistrement dans uen base datamart, et j'ai environ 26 millions d'enregistrements à purger,
donc si je savais comment faire une partition et comment supprimer les enregistrements associés à cette partition, ça serait génial !!

Merci d'avance :=)

**poups** · 18/05/2006, 23h52

j'ai effectué l'essai sur les contraintes en test : j'ai desactiver les contraintes sur les clés primaire et étrangère avec Oracle Manager, ça a également fait disparaitre l'index. Et le résultat est excellent, le temps d'exécution du DELETE est quasiment divisé par 10! Je ferai un essai partiel en prod demain pour voir l'impact sur l'utilisation de la base.
L'utilisation du NOLOGGING semble également permettre une amélioration sensible.
Merci à tous pour votre aide précieuse