Discussion :

[CaptainT]

Bonjour,

J'aurai besoin de l'avis d'experts concernant la mise en place de contrôles de doublons. Je m'explique:

- je reçois des fichiers plats via FTP avec des expéditions
- Ces expéditions sont remontées dans une table temporaire par bloc de 15000 via sqlloader selon certaines règles (on intègre que les expéditions des clients existants dans la base)
- ensuite on copie cette table temporaire dans la
table finale.

Sauf qu'aujourd'hui il n'y a pas de contrôle pour empécher d'éventuels doublons et cela devient très problématique.

Il faudrait donc que je rajoute dans le trigger d'insertion dans la table finale un contrôle. Il ya 2 solutions, pour une expédition donnée (identifiée
par un numéro d'expédition + le numéro de client)

- soit je supprime systématiquement les expéditions avec le meme identifiant avant l'insertion (si y'en a pas ça supprimera rien)
- soit je teste si l'expédition existe déja (table avec tres gros volume) si oui je supprime et j'insère ou j'update

Quelle serait selon vous le moins couteux niveau
performance?

Merci d'avance, je dois agir vite, et nous n'avons pas d'expert Oracle

Serveur Unix, Oracle 9i

[McM]

A mon avis, un MERGE marchera bien.
Faudra rajouter un index unique sur ton (numéro d'expédition + le numéro de client)

[CaptainT]

A mon avis, un MERGE marchera bien.
Faudra rajouter un index unique sur ton (numéro d'expédition + le numéro de client)

Au niveau des index, pour cette table j'en ai déja 7 (qui concernent chacun 1 ou plusieurs champs combinés). Est-ce logique? (la base a été mise en place bien avant mon arrivée, et mes compétences d'admin oracle sont assez limitées)

Je viens de regarder la FAQ pour le merge, ça m'a l'air de bien correspondre a ce que je veux faire. Cependant par rapport a un

Code :

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

1
2
3
4
 
insert into EXPED  (...)
select  ...  from TMP_EXPED
;

(c'est ce qui est en place)
qu'est-ce que ça donne niveau performance?

[orafrance]

une autre solution consiste à ajouter une contrainte d'unicité et de gérer l'exception DUP_VAL_ON_INDEX

Par ailleurs, une table externe en lieu et place de SQL*Loader + insert serait probablement plus performant

[CaptainT]

une autre solution consiste à ajouter une contrainte d'unicité et de gérer l'exception DUP_VAL_ON_INDEX

Par ailleurs, une table externe en lieu et place de SQL*Loader + insert serait probablement plus performant

Concernant la contrainte d'unicité tu le fais au niveau de l'index?
ça va pas etre long de créer un index sur une table de plusieurs dizaines de millions de lignes?

Que veux-tu dire par table externe?

[orafrance]

Tu peux créer une contrainte avec l'option NOVALIDATE pour éviter de vérifier la contrainte sur les lignes qui existe déjà : http://oracle.developpez.com/guide/a...disable_clause

pour la table externe : http://oracle.developpez.com/guide/a...age=Chap1#L1.5
C'est tout simplement une table basée sur un fichier

[McM]

Pour gérer l'exception DUP_VAL_ON_INDEX, t'es obligé d'insérer ligne par ligne.

[CaptainT]

Donc l'idéal serait d'ajouter une contrainte d'unicité en NOVALIDATE et de faire un MERGE pour insérer dans la table finale?

[GchcG]

Une autre solution, plus performante a mon sens que de traiter les exceptions liées aux violations de contraintes est de commencer par un update puis un insert :

Code :

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

1
2
3
4
5
Update EXPE set champ1 = var1 where num_cli = x and num_exp = y ;
If sql%rowcount = 0 Then
-- si l'instruction update n'a impacté aucune ligne
Insert into EXPE ... ;
End If ;

[orafrance]

c'est ce que fait le MERGE en principe

[orafrance]

Pour gérer l'exception DUP_VAL_ON_INDEX, t'es obligé d'insérer ligne par ligne.

tu peux passer par un BULK pour améliorer les perfs

[McM]

Tout ceci est regroupé dans le ... MERGE

[CaptainT]

Merci pour vos réponses je vais essayer de mettre ça en place d'ici la fin de semaine!

[McM]

Flute, grillé...

Sinon, Orafrance comment tu fais avec un BULK ?

[orafrance]

Flute, grillé...

Sinon, Orafrance comment tu fais avec un BULK ?

http://sheikyerbouti.developpez.com/...age=Chap5#L5.5

[CaptainT]

Bon y'a un truc que je pige pas...

Code :

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

1
2
3
4
ALTER TABLE EXPED ADD CONSTRAINT C_EXPED_UNIQUE UNIQUE (....) ENABLE NOVALIDATE
                                 *
ERROR at line 1:
ORA-02299: cannot validate (BASE.C_EXPED_UNIQUE) - duplicate keys found

Le novalidate n'est pas censé justement ignorer l'existant?

[orafrance]

oui, en effet.

En fait, quand elle est activée (ENABLE) la contrainte est forcément vérifiée. L'idéal est donc de supprimer les doublons avant d'activre la contrainte.

Pour répondre à ton besoin tu dois utiliser l'option deferrable initially deferred : http://download.oracle.com/docs/cd/B...3a.htm#1002617

[CaptainT]

Bon je reviens à la charge car je bosse ce week-end. Je ne sais pas si j'aurai beaucoup de réponses un dimanche

J'ai décidé d'effectuer une purge des doublons pour libérer de l'espace disque, ma table contient 201 000 000 de lignes. j'en ai supprimé 2millions pour l'instant et je me rend compte que l'occupation de mon tablespace n'a pas évolué dans ma console oracle, il est strictement le meme au Mo près. Une idée du pourquoi du comment?

[CaptainT]

Bon après 8.500.000 lignes en moins l'occupation n'a toujours pas bougé d'un poil... Si quelqu'un a une explication je suis preneur, car là je sèche...

[orafrance]

un DELETE ne fait pas redescendre la HWM donc t'as pas gagné d'espace disque... il faut reconstruire ton tablespace en déplaçant tous les objets dans un autre tablespace pour gagner l'espace disque.