Insertion depuis un fichier avec mise à jour d'une autre table

Bonjour tout le monde,

J'aurai besoins de votre expérience en insertion de données dans une base Oracle 11gR1.

Voilà, j'ai 1 fichier de 100 000 lignes à insérer en base et durant cette phase d'insertion, je dois récupérer l'ID créé pour faire une mise à jour d'un autre champ d'une autre table.

Le fichier pourrait contenir les informations suivantes :

Code:

1
2
1;b2c;Jefferson;;;;;N Kentwood;75019;Atlanta;FRANCE;75000;75001;0;;0;0
2;b2c;Roosevelt;;;;;Richmond Hill;75019;Montpelier;FRANCE;75000;75001;0;;1;1

Donc je récupère les données en splitant les lignes grâce aux ; et je fais mes insertions / mise à jour.

Donc je fais comme ceci :

Code:

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
create or replace
PROCEDURE ADDHAS AS
  ligne_fic VARCHAR2(32767);
  contenu_fic UTL_FILE.file_type;
  separateur VARCHAR2(1) := ';';
  i PLS_INTEGER := 0 ;
 
  numLine NUMBER;
  IDHOUSEHOLD NUMBER;
  INHOUSEHOLDTYPE VARCHAR2(3);
  INHOUSEHOLDNAME  VARCHAR2(50);
  INHOUSEHOLDSERVICE VARCHAR2(32);
  INHOUSEHOLDAD2 VARCHAR2(50);
  INHOUSEHOLDAD3 VARCHAR2(50);
  INHOUSEHOLDAD4 VARCHAR2(50);
  INHOUSEHOLDAD5 VARCHAR2(50);
  INHOUSEHOLDPOSTALCODE VARCHAR2(10);
  INHOUSEHOLDCITY VARCHAR2(50);
  INHOUSEHOLDCOUNTRY VARCHAR2(50);
  INHOUSEHOLDINSEECODE VARCHAR2(10);
  INHOUSEHOLDIRISCODE VARCHAR2(10);
  INHOUSEHOLDRNVP NUMBER(1);
  INHOUSEHOLDRNVPSTATUS VARCHAR(3);
  INHOUSEHOLDREMOVE NUMBER(1);
  INHOUSEHOLDNPAI NUMBER(1);
  BEGIN
 
    BEGIN
        contenu_fic := UTL_FILE.FOPEN('TMP', 'out.csv', 'r', 32767);
    EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(sqlerrm);
    END;
 
 
    LOOP
      BEGIN
 
        UTL_FILE.GET_LINE (contenu_fic, ligne_fic);
 
        numLine := to_number(substr(ligne_fic, 0, INSTR( ligne_fic, separateur, 1, 1 )-1));
 
      INHOUSEHOLDTYPE := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 1 )+1, (INSTR( ligne_fic, separateur, 1, 2 )- INSTR( ligne_fic, separateur, 1, 1 ))-1);
      INHOUSEHOLDNAME  := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 2 )+1, (INSTR( ligne_fic, separateur, 1, 3 )- INSTR( ligne_fic, separateur, 1, 2 ))-1);
      INHOUSEHOLDSERVICE := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 3 )+1, (INSTR( ligne_fic, separateur, 1, 4 )- INSTR( ligne_fic, separateur, 1, 3 ))-1);
      INHOUSEHOLDAD2 := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 4 )+1, (INSTR( ligne_fic, separateur, 1, 5 )- INSTR( ligne_fic, separateur, 1, 4 ))-1);
      INHOUSEHOLDAD3 := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 5 )+1, (INSTR( ligne_fic, separateur, 1, 6 )- INSTR( ligne_fic, separateur, 1, 5 ))-1);
      INHOUSEHOLDAD4 := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 6 )+1, (INSTR( ligne_fic, separateur, 1, 7 )- INSTR( ligne_fic, separateur, 1, 6 ))-1);
      INHOUSEHOLDAD5 := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 7 )+1, (INSTR( ligne_fic, separateur, 1, 8 )- INSTR( ligne_fic, separateur, 1, 7 ))-1);
      INHOUSEHOLDPOSTALCODE := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 8 )+1, (INSTR( ligne_fic, separateur, 1, 9 )- INSTR( ligne_fic, separateur, 1, 8 ))-1);
      INHOUSEHOLDCITY := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 9 )+1, (INSTR( ligne_fic, separateur, 1, 10 )- INSTR( ligne_fic, separateur, 1, 9 ))-1);
      INHOUSEHOLDCOUNTRY := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 10 )+1, (INSTR( ligne_fic, separateur, 1, 11 )- INSTR( ligne_fic, separateur, 1, 10 ))-1);
      INHOUSEHOLDINSEECODE := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 11 )+1, (INSTR( ligne_fic, separateur, 1, 12 )- INSTR( ligne_fic, separateur, 1, 11 ))-1);
      INHOUSEHOLDIRISCODE := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 12 )+1, (INSTR( ligne_fic, separateur, 1, 13 )- INSTR( ligne_fic, separateur, 1, 12 ))-1);
      INHOUSEHOLDRNVP := to_number(substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 13 )+1, (INSTR( ligne_fic, separateur, 1, 14 )- INSTR( ligne_fic, separateur, 1, 13 ))-1));
      INHOUSEHOLDRNVPSTATUS := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 14 )+1, (INSTR( ligne_fic, separateur, 1, 15 )- INSTR( ligne_fic, separateur, 1, 14 ))-1);
      INHOUSEHOLDREMOVE := to_number(substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 15 )+1, (INSTR( ligne_fic, separateur, 1, 16 )- INSTR( ligne_fic, separateur, 1, 15 ))-1));
      INHOUSEHOLDNPAI := to_number(substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 16 )+1));
 
    INSERT INTO HOUSEHOLD (HOUSEHOLDTYPE,	HOUSEHOLDNAME,	HOUSEHOLDSERVICE,	HOUSEHOLDAD2,	HOUSEHOLDAD3,	HOUSEHOLDAD4,	HOUSEHOLDAD5,	HOUSEHOLDDEPT, HOUSEHOLDPOSTALCODE,	HOUSEHOLDCITY,	HOUSEHOLDCOUNTRY,	HOUSEHOLDINSEECODE,  HOUSEHOLDIRISCODE,	HOUSEHOLDRNVP,	HOUSEHOLDRNVPSTATUS,	HOUSEHOLDREMOVE,	HOUSEHOLDNPAI)
    VALUES (INHOUSEHOLDTYPE,	INHOUSEHOLDNAME,	INHOUSEHOLDSERVICE,	INHOUSEHOLDAD2,	INHOUSEHOLDAD3,	INHOUSEHOLDAD4,	INHOUSEHOLDAD5,	SUBSTR(INHOUSEHOLDPOSTALCODE,0,2), INHOUSEHOLDPOSTALCODE,	INHOUSEHOLDCITY,	INHOUSEHOLDCOUNTRY,	INHOUSEHOLDINSEECODE, INHOUSEHOLDIRISCODE,	INHOUSEHOLDRNVP,	INHOUSEHOLDRNVPSTATUS,	INHOUSEHOLDREMOVE,	INHOUSEHOLDNPAI	)
     RETURNING HOUSEHOLDID INTO IDHOUSEHOLD;
 
 
    UPDATE MATCHING SET HOUSEHOLDID = IDHOUSEHOLD WHERE LINEID = numLine;
 
    i := i + 1;
 
    IF i = 10000 THEN
        commit;
        i := 0;
    end if;
 
 
    EXCEPTION WHEN NO_DATA_FOUND THEN
      UTL_FILE.FCLOSE(contenu_fic);
      EXIT;
  END;
 
END LOOP;
 
COMMIT;
 
END ADDHAS;

J'ai trois questions :

1. Avant je faisais une insertion toutes les lignes ce qui prenait du temps du coup je suis passé à une insertion toutes les 10 000. Ce qui m'a fait gagner environ 30 secondes. Est-ce que je m'y prends bien pour insérer les données toutes les 10000 lignes

2. Je m'aperçois que l'insertion est assez rapide mais arrivé à 50 000 lignes l'insertion ralenti considérablement. En gros j'insère 40 000 lignes en moins de 30 secondes mais après en fin de traitement j'arrive à 100 000 lignes en 3'50''. Ai-je loupé un truc quelque part ? Y a-t-il un moyen de libérer la mémoire ou toute autre ressource toutes les 10 000 lignes insérées ? (un genre de flush quelque chose ??)

3. Un autre moyen pour insérer les données puis récupérer les IDs pour mettre à jour mon autre table ?

A savoir que j'ai un index sur le champs LINEID de la table MATCHING.

Merci de votre aide.

Personne a d'idées ?

Bon ben j'ai enfin trouvé oufffff!

En faite j'avais un trigger sur le champ HOUSEHOLDID qui créait automatiquement l'identifiant de la table.

Je l'ai désactivé et rajouté un champs dans l'insertion :
Code:

1 2 3 INSERT INTO HOUSEHOLD (HOUSEHOLDID, HOUSEHOLDTYPE, HOUSEHOLDNAME, HOUSEHOLDSERVICE, HOUSEHOLDAD2, HOUSEHOLDAD3, HOUSEHOLDAD4, HOUSEHOLDAD5, HOUSEHOLDDEPT, HOUSEHOLDPOSTALCODE, HOUSEHOLDCITY, HOUSEHOLDCOUNTRY, HOUSEHOLDINSEECODE, HOUSEHOLDIRISCODE, HOUSEHOLDRNVP, HOUSEHOLDRNVPSTATUS, HOUSEHOLDREMOVE, HOUSEHOLDNPAI) VALUES (HOUSEHOLD_SEQ.nextval, INHOUSEHOLDTYPE, INHOUSEHOLDNAME, INHOUSEHOLDSERVICE, INHOUSEHOLDAD2, INHOUSEHOLDAD3, INHOUSEHOLDAD4, INHOUSEHOLDAD5, SUBSTR(INHOUSEHOLDPOSTALCODE,0,2), INHOUSEHOLDPOSTALCODE, INHOUSEHOLDCITY, INHOUSEHOLDCOUNTRY, INHOUSEHOLDINSEECODE, INHOUSEHOLDIRISCODE, INHOUSEHOLDRNVP, INHOUSEHOLDRNVPSTATUS, INHOUSEHOLDREMOVE, INHOUSEHOLDNPAI ) RETURNING HOUSEHOLDID INTO IDHOUSEHOLD;
Du coup je passe à 30 secondes ! Ce qui est beaucoup mieux.

J'avais aussi essayé de rajouter la ligne :

Code:

EXECUTE IMMEDIATE 'alter system flush buffer_cache';

après toutes les 10000 lignes et en donnant les privilèges ALTER SYSTEM à mon utilisateur mais ça n'a rien changé.

Enfin le principal c'est que maintenant en désactivant le trigger et en faisant appel directement à ma séquence dans l'insertion, j'ai divisé mon insertion par 8.

Voilà j'espère que ça pourra servir à d'autres personnes. Mais entre nous les trigger c'est mal :) (pas merci à Oracle :aie:)