Discussion :

[slefevre01]

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 :

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

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

[rouardg]

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 ?

[sygale]

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

Code :

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

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

[orafrance]

y a-t-il un index sur emp_id ?

[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.

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 :

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

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

[rouardg]

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.

[orafrance]

que donne ceci :

Code :

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

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

Les stats sont à jour ?

[McM]

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).