delete très long

Re-bonjour,
(en 8.1.6 sous solaris)
j'ai une table à purger mais pas en totalité.

par exemple pour tester un delete de 10lignes.

Code:

---

delete from customer where emp_id='ELECTRA' and rownum<11;

---

y'a un index sur emp_id.

les delete prennent beaucoup de temps, 22sec pour deleter 10lignes.
cette table comporte 4 FK mais pas paramètrées en DELETE CASCADE, donc je ne pense pas que ca vienne de là.

Elle comporte également 4 index. Est-ce que la suppression des index(sauf la PK) durant les delete améliorera bcq les temps, il y a 700000 lignes.

Il y a 2 triggers, un en insert, l'autre en update, donc ce ne devrait pas être ca non plus ?

D'ou peuvent provenir les pertes de temps ?
locks sur la table, contention sur les redologs.

avez-vous déjà rencontrés ce problème ?

Merci

Sébastien

Bonjour,

Il ne faut surtout pas supprimer tes index posés sur les FK.

En effet, lors du delete dans la table mère, Oracle recherche si il n'y a pas d'occurrences enfants dans ces 4 tables, au moyen des 4 index.

Si tu supprimes les index, alors Oracle devra balayer les tables enfants pour voir si ces occurrences existent ou pas, ce qui est beaucoup plus long.

Peux-tu tracer le plan d'exécution de ton delete STP ?
En fait, je suppose que, bien qu'il y ait un index sur EMP_ID, il ne doit pas être utilisé, car pas assez sélectif.

Sur tes 700000 lignes, combien as-tu de valeurs différentes de EMP_ID ?

Si tu es vraiement sur des tes intégritées tu peux les désactiver.

Code:

---

1 2 3

ALTER TABLE table_name disable CONSTRAINT constraint_name;

---

Mais là je vois pas trop le but d'avoir des contraints

Certe mais si c'est une purge, on peux faire ca en s'assurant de supprimer tous les fils ensuite et des réactiver les contraintes désactivées

y a-t-il un index sur emp_id ?

pour répondre à rouardg,
tu m'as permis de découvrir qu'il n'y avait pas d'index sur toutes les FK.
Ca peut donc provenir de là ?

il me semblait qu'en ne spécifiant pas DELETE on CASCADE sur les contraintes, oracle n'allait pas accèder aux tables filles.

Voici le plan d'exécution, il utilise bien l'index à priori, même s'il n'est pas super sélectif. 24 valeurs différentes sur 100.000 rows au total.
est-ce qu'en forcant un fullscan ca sera plus rapide. ?

Code:

---

1 2 3 4 5 6 7 8

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop   DELETE STATEMENT Optimizer Mode=CHOOSE 4 K 416 DELETE CUSTOMER COUNT STOPKEY TABLE ACCESS BY INDEX ROWID CUSTOMER 4 K 151 K 416 INDEX RANGE SCAN IDX_CUSTOMER_EMP_ID 4 K 13

---

merci

Garde ton index. L'optimiseur Oracle est suffisament intelligent pour choisir entre un Range Scan sur cet index, et un Full Scan sur la table.

Sinon, concernant tes FK, à partir du moment où tu fais un delete sur la table mère, que les FK soient en DELETE CASCADE, en DELETE NULL ou sans clause particulière, il faut mettre des index sur les FK.

Là en fait, comme tu delete 10 occurrences dans la table mère, tu te payes 40 full scan de tables enfants au total.

Mon conseil : poses 4 index sur tes 4 FK, regénère tes stats, et effectue le delete. Cela devrait aller beaucoup plus vite.

Pour info complémentaire, pour ta création d'index, tu peux faire des index composite si tu en as besoin, mais il faut que les colonnes de ta FK soient les colonnes leader de l'index.

que donne ceci :

Code:

---

delete /*+ FIRST_ROWS */ from customer where emp_id='ELECTRA' and rownum<11;

---

Les stats sont à jour ?

Citation:

---

Envoyé par slefevre01

pour répondre à rouardg,
tu m'as permis de découvrir qu'il n'y avait pas d'index sur toutes les FK.
Ca peut donc provenir de là ?

il me semblait qu'en ne spécifiant pas DELETE on CASCADE sur les contraintes, oracle n'allait pas accèder aux tables filles.
merci

---

Oui, ça peut provenir de là, surtout si ta table fille est grosse.
Oracle vérifie tout le temps l'intégrité des données, donc il accède constamment aux FK.

J'ai le cas d'une table des paramètres qui sont utilisés dans toutes les tables de la base. Un delete ou update d'une ligne de la PK met 5 minutes.
En cas de suppression de plusieurs codes (je sais qu'ils ne sont utilisés nul part), je désactive les contraintes (2s), je passe mon delete (2s) et je réactive les contraintes (10 minutes).