delete trop long à cause d'une fk ou des stats oracle ?

Version imprimable

21/12/2010, 21h10
ekremyilmaz

delete trop long à cause d'une fk ou des stats oracle ?
bonjour,
j'ai une petite question Oracle ou SQL...

J'ai une table OPERATION

OPE_ID ##### OPE_DATE ##### OPE_PRECEDENTE
1 ######## 29/10/10 ######## NULL
2 ######## 29/10/10 ######## NULL
3 ######## 29/10/10 ######## 2
4 ######## 29/10/10 ######## 1
5 ######## 29/10/10 ######## 4

OPE_ID : clé primaire
OPE_PRECEDENTE : foreign key sur la clé primaire OPE_ID

J'ai une requête qui a 38000 enregistrements à supprimer de cette table OPERATION.
Code:

1 2 Delete from operation where ope_id in (select ope_id from extr_id_arc_pur);
Nous avons testé sur une base iso-prod en désactivant la contrainte (foreign key FK_OPE_PRECEDENTE) de cette table.

Le temps d'exécution est de 3min.

Nous avons réactivé cette contrainte et réimporter les données de la base et le temps d'exécution est > 6 heures.

Cette colonne OPE_PRECEDENTE peut avoir des valeurs null.

Il y a un index sur cette colonne.

J'aurai voulu savoir s'il est possible que cette contrainte puisse faire ralentir les perfs ? A chaque delete, Oracle que fait-il ? il contrôle les foreign key ?

Autre chose, après le re-import de la base, doit-on recalculer les statistiques oracle ?

En espérant avoir été clair :) !!!

Merci d'avance.
22/12/2010, 14h56
Waldar

Oui, à chaque DELETE Oracle va vérifier l'intégrité référentielle, impacter les index et ça prend du temps.

Supprimer les index et désactiver les contraintes avant de faire la suppression est une pratique courante.

Pour les statistiques ça dépend. Si vous supprimez 38.000 lignes d'une table de 50.000 lignes ou de plusieurs millions. Dans le premier cas il faut recalculer, dans le second c'est à priori pas nécessaire.

finalement, il n'y avait pas d'index sur cette colonne (pas en prod) uniquement en dev.
Dans l'environnement isoprod, il n'y avait pas d'index.

On l'a rajouté, on passe de 6h à 3min :) !!!!!!!

Lorsque nous avions supprimé la contrainte, il n'y avait pas de souci mais en la réactivant, il faisait un full scan

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 
select /*+ all_rows */ count(1) from "NAEADM"."OPERATION" where "OPE_ID_PRECEDE                                                            
NTE" = :1                      
 
Plan 1(PHV: 2489153561)
Execution Plan                                                                                                                              
--------------------------------------------------------------------------------                                                            
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |                                                            
--------------------------------------------------------------------------------                                                            
|   0 | SELECT STATEMENT   |           |       |       | 39197 (100)|          |                                                            
|   1 |  SORT AGGREGATE    |           |     1 |     2 |            |          |                                                            
|   2 |   TABLE ACCESS FULL| OPERATION |     1 |     2 | 39197   (2)| 00:07:51 |                                                            
--------------------------------------------------------------------------------