Discussion :

[madmax-mad]

bonjour, pouvez-vous m'aider à améliorer le temps de traitement d'une procedure qui traite l'historique(comparaison de deux tables de plus 1 million d'enregistrement ).

Code sql :

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

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
SQL> desc t_paie --
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 IDENT                                              VARCHAR2(12)
 CODE                                               VARCHAR2(5)
 MONTANT                                            NUMBER(10,2)
 
SQL> desc hist_paie
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 IDENT                                              VARCHAR2(12)
 CODE                                               VARCHAR2(5)
 MONTANT                                            NUMBER(10,2)
 DU                                                 DATE
 AU                                                 DATE
 
SQL> desc t_temp
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 IDENT                                              VARCHAR2(12)
 
SQL> CREATE OR REPLACE PROCEDURE histo_paie(mois_new in date,mois_old in date) IS
  2  BEGIN
  3     DECLARE
  4        CURSOR paie_new IS
  5           SELECT ident, montant
  6           FROM t_paie
  7           WHERE code = 'N';
  8        CURSOR paie_old IS
  9           SELECT ident, montant
 10           FROM hist_paie
 11           WHERE code = 'N' AND au = mois_old;
 12  
 13     BEGIN
 14  
 15        FOR rec_new IN paie_new  LOOP
 16           FOR rec_old IN paie_old  LOOP
 17              IF rec_old.ident = rec_new.ident THEN
 18                 IF rec_old.montant <> rec_new.montant THEN
 19                    INSERT INTO t_temp
 20                    SELECT DISTINCT (ident) FROM t_paie WHERE ident = rec_new.ident;
 21                 ELSE
 22                    UPDATE hist_paie
 23                    SET au = mois_new
 24                    WHERE au = mois_old and ident =rec_old.ident;
 25                 END IF;
 26              END IF;
 27           END LOOP;
 28        END LOOP;
 29        commit;
 30        INSERT INTO hist_paie
 31        SELECT ident, code, montant, mois_new du,mois_new au
 32        FROM t_paie
 33        WHERE ident IN (SELECT ident FROM t_temp);
 34        COMMIT;
 35     END;
 36  END;
 37  /
 
Procédure créée.
 
 
 
SQL> begin
  2  dbms_output.enable (100000);
  3  dbms_output.put_line('DEBUT TRAITEMENT : '||to_char(sysdate,'HH24:MI:SS'));
  4  histo_paie('01/02/2010','01/01/2010');
  5  dbms_output.put_line('FIN TRAITEMENT : '||to_char(sysdate,'HH24:MI:SS'));
  6* end;
SQL> /
DEBUT TRAITEMENT : 13:38:09
FIN TRAITEMENT : 16:32:57
 
Procédure PL/SQL terminée avec succès.

[mnitu]

Code :

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

1
2
3
 
Drop procedure histo_paie
/

et utilisez Merge à sa place.

[Waldar]

Expliquez ce que vous voulez faire en plus de fournir le code !

[madmax-mad]

Expliquez ce que vous voulez faire en plus de fournir le code !

j'utilise deux curseurs pour la comparaison des deux tables contenant:
- les détails de la paie du mois en cours.
- l'historique des détails de la paie.
et comme je n'ai pas beaucoup d'espace pour inserer simplement
la nouvelle paie dans l'historique, je doit les comparer pour n'insérer que
les modification de paie, sinon une mise à jour sur la date de l'ancienne suffit.
J'ai trouvé une technique de traitement en bloc dans le cours PL/SQL "BULK",
qui est plus rapide, mais je ne sait meme pas comment la demarrer.
donc j'implore vos grandes connaissances pour m'aider.

[Waldar]

Je pense que mnitu vous a donné la meilleure piste.
Pas besoin de bloc PL/SQL, un simple ordre MERGE devrait suffire.

Essayez de l'écrire en suivant le lien fournit.

[madmax-mad]

Code :

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

1
2
3
 
Drop procedure histo_paie
/

et utilisez Merge à sa place.

Merci,
je vais l'examiner de plus prés et je vous répondrais.

[madmax-mad]

Je vous signale que j'évolue en ORACLE 9i

[mnitu]

Je vous signale que j'évolue en ORACLE 9i

Merge Oracle 9i

[madmax-mad]

Merge Oracle 9i

trés intentionné de votre part.
MERCI.

[mnitu]

En fait, il y a quelques petites et subtiles différences entre les deux versions donc il est plus sage d’utiliser la documentation approprié.

[madmax-mad]

En testant MERGE sur une petite partie des deux tables 'T_PAIE' et 'HIST_PAIE'
(le nombre d'enregistrement ne dépassant pas le 50000 lignes) , l'instruction a très bien marché avec un temps d'exécution de 15 secondes environ.
mais avec les tables entières (plus d' un million de ligne chaque) ,je rencontre une erreur ORA-30926 qui stipule qu'un ensemble de lignes stable ne peu pas être obtenu
Et voila mon instruction:

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SQL> MERGE INTO hist_paie H
   2   USING (select ident, code, montant from t_paie) P
   3   ON (H.ident =P.ident and H.code=P.code and H.montant= P.montant)
   4   WHEN MATCHED THEN UPDATE SET H.au='01/02/2010'
   5   WHEN NOT MATCHED THEN INSERT (ident,code,montant,du,au)
   6   values (P.ident,P.code,P.montant,'01/02/2010','01/02/2010');
ON (H.ident =P.ident and H.code=P.code and H.montant= P.montant)
                                                             *
ERREUR à la ligne 3 :
ORA-30926: impossible d obtenir un ensemble de lignes stables dans les tables
source

j'avoue que je suis largué

[Waldar]

C'est que votre jointure entre vos deux tables n'est pas assez restrictive, et pour une ligne de hist_paie vous avez plusieurs lignes de t_paie.

Impossible donc de faire un update dans ces conditions.

[madmax-mad]

Vous aviez raison;
il m'a falut preciser les dates pour la comparaison.
encore