optimisation procedure stocké

**karimoos** · 12/05/2010, 18h10

bonjours;
j'ai le code suivant dont je souhaite optimiser le temps d'execution de mon procedure avec un peu prés 2 mil de lignes ,mais oracle me retourne

impossible d'obtenir un ensemble de lignes stables dans les tables
svp j'ai un projet que je dois finir le plus tôt possible!!

Code :

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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
CREATE OR REPLACE PROCEDURE get_nom IS
 
 
 v_sigle             NUMBER (1):=1;
 v_datecreation      NUMBER (1):=1;
 v_raisonsociale     NUMBER (1):=1;
 v_idactivite        NUMBER (1):=1;
 
  TYPE aat_id IS TABLE OF tier.IDTIER%type
          INDEX BY PLS_INTEGER;
  TYPE aat_sigle IS TABLE OF tier.sigle%type
          INDEX BY PLS_INTEGER;
  TYPE aat_creation IS TABLE OF tier.datecreation%type
          INDEX BY PLS_INTEGER;
  TYPE aat_raison IS TABLE OF tier.raisonsociale%type
          INDEX BY PLS_INTEGER;
  TYPE aat_ap IS TABLE OF infosactiviteprofessionnelle.idactivite%type
          INDEX BY PLS_INTEGER;
 
	 aa_ids       aat_id;
     aa_creation  aat_creation;
	 aa_activite  aat_ap;
	 aa_raison    aat_raison;
	 aa_sigle     aat_sigle;
 
      -- i PLS_INTEGER := 0;
       u PLS_INTEGER := 0;
 
--  v_number_of_lines   number(30); 
--   
 CURSOR sigledcrs_cur
   IS
      SELECT t.idtier, t.sigle, t.datecreation, t.raisonsociale,
             infop.idactivite
        FROM tier t, infosactiviteprofessionnelle infop
       WHERE (t.sigle IS NULL)
          OR (t.datecreation IS NULL)
          OR (t.raisonsociale IS NULL)
          OR     (infop.idactivite IS NULL)
             AND (    t.typepersonne = 'PP'
                  AND t.idagenteco IN (32)
                  AND t.idactiviteprof = infop.idactiviteprof
                 );
 
BEGIN
 
 
 open sigledcrs_cur;
 
     LOOP
 
	FETCH sigledcrs_cur BULK COLLECT INTO aa_ids,aa_sigle,aa_creation,aa_raison,aa_activite limit 10000;
 
 
      IF  aa_sigle IS NULL
      THEN
         v_sigle  := 1;
      ELSE
         v_sigle  := 0;
      END IF;
 
      IF  aa_creation IS NULL
      THEN
         v_datecreation := 1;
      ELSE
         v_datecreation := 0;
      END IF;
 
      IF aa_raison IS NULL
      THEN
         v_raisonsociale := 1;
      ELSE
         v_raisonsociale := 0;
      END IF;
 
	   IF aa_activite IS NULL
      THEN
           v_idactivite := 1;
      ELSE
           v_idactivite:=  0;
      END IF;
 
	 FORALL i IN 1 .. aa_ids.COUNT
 
     MERGE INTO nfiabilisation nf
     USING(select aa_ids(i) as idtier  from tier) ti
     on(nf.idtier=ti.idtier)
 
	 WHEN MATCHED THEN 	
	 UPDATE  
	 SET sigle=v_sigle,
	 activiteprincipale= v_idactivite,
	 raisonsociale=v_raisonsociale,
	 datecreation=v_datecreation	 
 
 
	-- u := u + SQL%ROWCOUNT;
   --  COMMIT;
 
	--IF SQL%ROWCOUNT = 0 THEN
 
	 WHEN NOT MATCHED THEN	 
      INSERT (IDTIERAFIAB,idtier,sigle,datecreation,raisonsociale,activiteprincipale,TYPEPERSONNE)
      VALUES (nfiabilisation_seq.NEXTVAL,ti.idtier,v_sigle,v_datecreation,v_raisonsociale,v_idactivite,'PP'
                  );
 
   -- i := i + 1;COMMIT;
 	--END IF;
	u := u + SQL%ROWCOUNT;
 
      EXIT WHEN sigledcrs_cur%NOTFOUND;
   END LOOP;
 
 
  close sigledcrs_cur;
    DBMS_OUTPUT.PUT_LINE( u || ' rows merged.' );
   -- DBMS_OUTPUT.PUT_LINE( i || ' rows inserted.' );
 
 
   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN	
      raise_application_error (-20992, 'aucune donnée trouvée');
END get_nom;
/

**McM** · 13/05/2010, 12h14

Plusieurs points sur ton code :

1/ Les parenthèses de ton curseur.
les OR et les AND, mieux vaut pour la lecture/débugage séparer les groupes

Code :

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

1
2
3
4
5
6
7
8
(t.sigle IS NULL)
          OR (t.datecreation IS NULL)
          OR (t.raisonsociale IS NULL)
          OR     (infop.idactivite IS NULL)
             AND (    t.typepersonne = 'PP'
                  AND t.idagenteco IN (32)
                  AND t.idactiviteprof = infop.idactiviteprof
                 );

est équivalent à

Code :

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

1
2
3
4
5
6
7
8
  t.sigle IS NULL
OR t.datecreation IS NULL
OR t.raisonsociale IS NULL
OR (    infop.idactivite IS NULL
    AND t.typepersonne = 'PP'
    AND t.idagenteco IN (32)
    AND t.idactiviteprof = infop.idactiviteprof
    )

2/ Le MERGE, Gros problème

Code :

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

USING(SELECT aa_ids(i) AS idtier  FROM tier) ti  ON(nf.idtier=ti.idtier)

En gros tu sélectionnes toute la table TIER en ramenant une variable. Donc ça donne X lignes avec aa_ids(i)
Pour être correct (mais pas optimisé) tu aurais du mettre

Code :

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

USING(SELECT aa_ids(i) AS idtier  FROM tier WHERE idtier = aa_ids(i)) ti  ON(nf.idtier=ti.idtier)

Mais bon, Il faut juste mettre DUAL comme table.

Code :

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

USING(SELECT aa_ids(i) AS idtier  FROM DUAL) ti  ON(nf.idtier=ti.idtier)

3/ La prochaine fois, il faut mettre le code erreur oracle ORA-30926
une recherche sur google m'a ramené sur developpez :http://www.developpez.net/forums/d88...e/#post5015378

**karimoos** · 14/05/2010, 13h14

Merci de tes remarques!!
En fin j'ai modifié pas mal de code mais le problème reste encore :temps d'exécution de cette procédure dépasse 1h30min.
avec un peu prés 2 mil de lignes donnés.

Code :

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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
CREATE OR REPLACE PROCEDURE get_nom IS
 
 
 v_sigle             NUMBER (1):=1;
 v_datecreation      NUMBER (1):=1;
 v_raisonsociale     NUMBER (1):=1;
 v_idactivite        NUMBER (1):=1;
 
  TYPE aat_id IS TABLE OF tier.IDTIER%type
          INDEX BY PLS_INTEGER;
  TYPE aat_sigle IS TABLE OF tier.sigle%type
          INDEX BY PLS_INTEGER;
  TYPE aat_creation IS TABLE OF tier.datecreation%type
          INDEX BY PLS_INTEGER;
  TYPE aat_raison IS TABLE OF tier.raisonsociale%type
          INDEX BY PLS_INTEGER;
  TYPE aat_ap IS TABLE OF infosactiviteprofessionnelle.idactivite%type
          INDEX BY PLS_INTEGER;
 
	 aa_ids       aat_id;
     aa_creation  aat_creation;
	 aa_activite  aat_ap;
	 aa_raison    aat_raison;
	 aa_sigle     aat_sigle;
 
      -- i PLS_INTEGER := 0;
     u PLS_INTEGER := 0;
 
--  v_number_of_lines   number(30); 
--   
 CURSOR sigledcrs_cur
   IS
      SELECT t.idtier, t.sigle, t.datecreation, t.raisonsociale,
             infop.idactivite
        FROM tier t, infosactiviteprofessionnelle infop
       WHERE (t.sigle IS NULL)
          OR (t.datecreation IS NULL)
          OR (t.raisonsociale IS NULL)
          OR (infop.idactivite IS NULL AND t.typepersonne = 'PP'
                  AND t.idagenteco IN (32)
                  AND t.idactiviteprof = infop.idactiviteprof
                 );
 
BEGIN
 
 
 open sigledcrs_cur;
 
   LOOP
 
	FETCH sigledcrs_cur BULK COLLECT INTO aa_ids,aa_sigle,aa_creation,aa_raison,aa_activite limit 1000;
 
 
	--for sigledcrs_rec  in     sigledcrs_cur loop
 
	 FOR i IN 1 .. aa_ids.COUNT loop
 
 
 
	 if aa_sigle.exists(i) then v_sigle :=1; else v_sigle :=0; end if;
	 if aa_creation.exists(i) then  v_datecreation :=1; else  v_datecreation :=0; end if;
	 if aa_raison.exists(i) then v_raisonsociale :=1; else v_raisonsociale :=0; end if;
	 if aa_activite.exists(i) then  v_idactivite :=1 ;else  v_idactivite :=0; end if;
 
    MERGE INTO NFIABILISATION nf
     USING(select aa_ids(i) as idtier from tier where idtier=aa_ids(i)) ti
     ON(nf.idtier=ti.idtier)
 
	 WHEN MATCHED THEN 	
	 UPDATE 
	 SET sigle=v_sigle,
	 activiteprincipale= v_idactivite,
	 raisonsociale=v_raisonsociale,
	 datecreation=v_datecreation	 
 
	-- u := u + SQL%ROWCOUNT;
   --  COMMIT;          
 
	--IF SQL%ROWCOUNT = 0 THEN
	 WHEN NOT MATCHED THEN	 
     INSERT (IDTIERAFIAB,idtier,sigle,datecreation,raisonsociale,activiteprincipale,TYPEPERSONNE)
     VALUES (nfiabilisation_seq.NEXTVAL,ti.idtier,v_sigle,v_datecreation,v_raisonsociale,v_idactivite,'PP'
                   );
     -- COMMIT;
   -- i := i + 1;COMMIT;
 	--END IF;
	u := u + SQL%ROWCOUNT;
	end loop;
      EXIT WHEN sigledcrs_cur%NOTFOUND;
   END LOOP;
 
 
 
    DBMS_OUTPUT.PUT_LINE( u || ' rows merged.' );
   -- DBMS_OUTPUT.PUT_LINE( i || ' rows inserted.' );
 
 
   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN	
      raise_application_error (-20992, 'aucune donnée trouvée');
END get_nom;
/

**McM** · 14/05/2010, 13h32

Dnas le merge, met la table DUAL au lieu d'un accès à TIER

Code :

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

USING(SELECT aa_ids(i) AS idtier  FROM DUAL) ti  ON(nf.idtier=ti.idtier)

Sinon, essaye de le faire en 2 requêtes normales INSERT et UPDATE sans passer par un curseur. Ce sera surement plus optimisé.

En regardant un peu pour coder l'update, ton curseur de départ est faux.
Tu n'as pas de jointure entre tier et infop !
Corrige déjà le curseur avant de tester les merger.

**karimoos** · 14/05/2010, 13h58

Voici la jointure entre les 2 table mais le mem probleme!!malheuresement

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
CURSOR sigledcrs_cur
   IS
      SELECT t.idtier, t.sigle, t.datecreation, t.raisonsociale,
             infop.idactivite
        FROM tier t inner join infosactiviteprofessionnelle infop on t.idactiviteprof = infop.idactiviteprof
       WHERE (t.sigle IS NULL)
          OR (t.datecreation IS NULL)
          OR (t.raisonsociale IS NULL)
          OR (infop.idactivite IS NULL AND t.typepersonne = 'PP'
                  AND t.idagenteco IN (32)
 
                 );

optimisation procedure stocké

PL/SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager