Update massif d'une table (massive :D)

**Karibou** · 29/11/2007, 09h35

Bonjour,

J'ai pour objectif de faire un trim (supprimer les espace blancs) de tous les champs varchar d'une table. J'ai d'abord testé avec un update tout bete, qui etait relativement performant (niveau temps) cependant des que je l'ais exectué sur un table avec beaucoup de lignes j'ai un depassement du tablespace undo.
Donc il faut que je trouve un moyen de faire un autocommit.

J'ai donc fait une boucle qui commit a un interval donné, mais maintenant ca mets presque 2min pour 10100 lignes (contre quelques milisecondes pour le code seul)

Voici le code:

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
 
declare
v_pas number := 0;
commit_point number := 1000;
begin
LOOP
    UPDATE DIM_CLUSTER_SCD SET
	CLS_NAME				= rtrim(ltrim(CLS_NAME)),
	CLS_SEGMENT_CODE		= rtrim(ltrim(CLS_SEGMENT_CODE)),
	CLS_SEGMENT_NAME		= rtrim(ltrim(CLS_SEGMENT_NAME)),
	CLS_SUB_SEGMENT_CODE	= rtrim(ltrim(CLS_SUB_SEGMENT_CODE)),
	CLS_SUB_SEGMENT_NAME	= rtrim(ltrim(CLS_SUB_SEGMENT_NAME)),
	CLS_LEVEL				= rtrim(ltrim(CLS_LEVEL))
 WHERE rownum = v_pas;
 v_pas := v_pas + 1;
commit;
IF v_pas > commit_point then
commit;
commit_point := commit_point + 1000;
DBMS_OUTPUT.PUT_LINE ('commit'||commit_point );
end if;
 
/* Sortie de la boucle quand ca depasse le nombre de record de la table */
IF v_pas = 10101 then
exit;
end IF;
END LOOP;
end;

Connaissez-vous un moyen d'accelerer ce morceau de code? (ou du moins la raison pour laquelle c'est plus lent?

J'ai aussi pensé a d'autre techniques, genre copier les données dans une autre table en faisant le trim, puis dropper l'ancienne table et renommer la nouvelle, mais j'ai pas les bons droits pour effectuer ca..
Idem pour augmenter la taille du tablespace undo...

Merci beaucoup pour votre aide!!

**plaineR** · 29/11/2007, 09h46

Bonjour,

Ton code est lent car tu traites n fois les lignes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL>select count(*) from user_objects;
  COUNT(*)
----------
      1916
 
SQL>select count(*) from user_objects where rownum = 2;
  COUNT(*)
----------
         0
 
SQL>select count(*) from user_objects where rownum <= 2;
  COUNT(*)
----------
         2

rownum est le numéro d'ordre dans ta requête (et non dans ta table)

Ce qu'il faut donc que tu fasses :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
LOOP
    UPDATE DIM_CLUSTER_SCD SET
	CLS_NAME				= rtrim(ltrim(CLS_NAME)),
	CLS_SEGMENT_CODE		= rtrim(ltrim(CLS_SEGMENT_CODE)),
	CLS_SEGMENT_NAME		= rtrim(ltrim(CLS_SEGMENT_NAME)),
	CLS_SUB_SEGMENT_CODE	= rtrim(ltrim(CLS_SUB_SEGMENT_CODE)),
	CLS_SUB_SEGMENT_NAME	= rtrim(ltrim(CLS_SUB_SEGMENT_NAME)),
	CLS_LEVEL				= rtrim(ltrim(CLS_LEVEL))
 WHERE rownum <= v_commit;
commit;
 
exit when sql%notfound;
END LOOP;

**orafrance** · 29/11/2007, 10h07

il manque surtout un between et une condition qui permet de ne pas updater toujours les mêmes lignes.

Il faut faire des updates par paquet genre WHERE id BETWEEN borne_min AND borne_max avec id la PK de la table

Et puis, ça vaut peut-être pas le coup de faire des commits intermediaires, il y a combien de lignes dans cette table ?

**Karibou** · 29/11/2007, 10h44

En fait j'ai une quinzaine de tables a updater.
Donc j'aimerais faire en sorte que le code soit le plus generique possible (ie que j'ai juste a coller le code d'update dans la boucle)

C'est super ce que tu m'as donné plaineR cependant j'ai l'impression que ca n'effectue que l'operation pour les v_commit premieres lignes.

La plus grosse table que j'ai a updater fait 4 323 536 lignes (sur ma base test, surement plus en production)

orafrance: c'est une bonne idée de faire un between avec la pk de la table, cependant ya pas vraiment de pk proprement dite (les tables ont mal ete conçues, ils on pas crée une pk generique sur toutes les tables, mais on preferé utiliser un champ qui est unique dans chaque table, ie: un numero de compte dans l'une, numero de telephone dans une autre, etc..) donc le type de l'id change avec les tables...

C'est pour ca que je cherche une methode generique...

Encore merci pour votre aide...

**orafrance** · 29/11/2007, 10h51

Envoyé par Karibou

C'est super ce que tu m'as donné plaineR cependant j'ai l'impression que ca n'effectue que l'operation pour les v_commit premieres lignes.

inspire toi de ce qu'il a fait et lis ceci pour compléter : http://oracle.developpez.com/faq/?page=3-1#rankrownum

Tu peux peut-être aussi utiliser SELECT FOR UPDATE pour faire tes paquets

**plaineR** · 29/11/2007, 11h09

Envoyé par Karibou

C'est super ce que tu m'as donné plaineR cependant j'ai l'impression que ca n'effectue que l'operation pour les v_commit premieres lignes.

Il manque en effet une condition dans la clause where :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
    UPDATE DIM_CLUSTER_SCD SET
	CLS_NAME				= rtrim(ltrim(CLS_NAME)),
	CLS_SEGMENT_CODE		= rtrim(ltrim(CLS_SEGMENT_CODE)),
	CLS_SEGMENT_NAME		= rtrim(ltrim(CLS_SEGMENT_NAME)),
	CLS_SUB_SEGMENT_CODE	= rtrim(ltrim(CLS_SUB_SEGMENT_CODE)),
	CLS_SUB_SEGMENT_NAME	= rtrim(ltrim(CLS_SUB_SEGMENT_NAME)),
	CLS_LEVEL				= rtrim(ltrim(CLS_LEVEL))
 WHERE rownum <= v_commit
   and (CLS_NAME != rtrim(ltrim(CLS_NAME)),
          or CLS_SEGMENT_CODE != rtrim(ltrim(CLS_SEGMENT_CODE)),
          or CLS_SEGMENT_NAME != rtrim(ltrim(CLS_SEGMENT_NAME)),
          or CLS_SUB_SEGMENT_CODE != rtrim(ltrim(CLS_SUB_SEGMENT_CODE)),
          or CLS_SUB_SEGMENT_NAME != rtrim(ltrim(CLS_SUB_SEGMENT_NAME)),
          or CLS_LEVEL != rtrim(ltrim(CLS_LEVEL)));

**orafrance** · 29/11/2007, 11h16

mais alors là gare aux perfs avec autant de FTS que de boucle

4 millions de ligne ça doit pouvoir se faire sans commit intermédiaire quand même

**Karibou** · 29/11/2007, 11h34

4 millions de ligne ça doit pouvoir se faire sans commit intermédiaire quand même

Bah en fait j'ai aucun droits sur la db. A mon avis c'est que le tablespace UNDO est trop petit, c'est l'erreur que j'ai: unable to extend tablespace UNDO by ### KB (je ne sais plus ce que c'est le chiffre)

J'ai testé ca:

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
declare
v_commit number := 1000;
begin
LOOP
    UPDATE DIM_CLUSTER_SCD SET
	CLS_NAME				= rtrim(ltrim(CLS_NAME)),
	CLS_SEGMENT_CODE		= rtrim(ltrim(CLS_SEGMENT_CODE)),
	CLS_SEGMENT_NAME		= rtrim(ltrim(CLS_SEGMENT_NAME)),
	CLS_SUB_SEGMENT_CODE	= rtrim(ltrim(CLS_SUB_SEGMENT_CODE)),
	CLS_SUB_SEGMENT_NAME	= rtrim(ltrim(CLS_SUB_SEGMENT_NAME)),
	CLS_LEVEL				= rtrim(ltrim(CLS_LEVEL))
 WHERE rownum <= v_commit
   AND (CLS_NAME != rtrim(ltrim(CLS_NAME))
          OR CLS_SEGMENT_CODE != rtrim(ltrim(CLS_SEGMENT_CODE))
          OR CLS_SEGMENT_NAME != rtrim(ltrim(CLS_SEGMENT_NAME))
          OR CLS_SUB_SEGMENT_CODE != rtrim(ltrim(CLS_SUB_SEGMENT_CODE))
          OR CLS_SUB_SEGMENT_NAME != rtrim(ltrim(CLS_SUB_SEGMENT_NAME))
          OR CLS_LEVEL != rtrim(ltrim(CLS_LEVEL)));
commit;
v_commit := v_commit + 1000;
if sql%notfound then 
exit;
end if;
END LOOP;
end;

Et ca fait toujours les pas toutes les lignes... :S
Alors ptet qu'il y a une erreur toute bete dans mon code que je ne vois pas (vu que ca fait des heures que j'ai le nez plongé dedans...

Sinon la fonction row_number() me semblait interessante pour assigner un identifiant unique a une ligne (vu que j'en ais pas vraiment) cependant je vois pas trop comment la mettre en marche...

**plaineR** · 29/11/2007, 12h07

Voici un exemple qui fonctionne, tu n'as qu'à t'en inspirer :

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
SQL>create table t1 as select object_name, 0 col1 from user_objects;
Table created.
 
SQL>begin
  2  loop
  3    update t1 set col1 = 1
  4    where rownum <= 10
  5      and col1 = 0;
  6    commit;  
  7  exit when sql%notfound;
  8  end loop;
  9  end;
 10  /
PL/SQL procedure successfully completed.
 
SQL>select distinct col1 from t1;
      COL1
----------
         1

**McM** · 29/11/2007, 12h57

Si c'est un One-shot, autant faire un update par champ afin de limiter l'undo.

Sur 4 Millions de lignes, combien ont des espaces en début ou fin de champ ?

Sur la requete de plaineR, la condition where peut être fausse s'il n'y a que des espaces dans un champ.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
UPDATE DIM_CLUSTER_SCD SET
	cls_name		= TRIM(cls_name),
	cls_segment_code	= TRIM(cls_segment_code),
	cls_segment_name	= TRIM(cls_segment_name),
	cls_sub_segment_code	= TRIM(cls_sub_segment_code),
	cls_sub_segment_name	= TRIM(cls_sub_segment_name),
	cls_level		= TRIM(cls_level)
WHERE NVL(LENGTH(cls_name),0) <> NVL(LENGTH(TRIM(cls_name),0)
OR NVL(LENGTH(cls_segment_code),0) <> NVL(LENGTH(TRIM(cls_segment_code),0)
OR NVL(LENGTH(cls_segment_name),0) <> NVL(LENGTH(TRIM(cls_segment_name),0)
OR NVL(LENGTH(cls_sub_segment_code),0) <> NVL(LENGTH(TRIM(cls_sub_segment_code),0)
OR NVL(LENGTH(cls_sub_segment_name),0) <> NVL(LENGTH(TRIM(cls_sub_segment_name),0)
OR NVL(LENGTH(cls_level),0) <> NVL(LENGTH(TRIM(cls_level),0)

**Karibou** · 29/11/2007, 13h18

McM: oui c'est un oneshot.
J'ai deja corrigé les mappings Informatica pour ca. (Pourquoi ca n'a pas ete fait des le depart, ca je sais pas j'etais pas sur le projet

)

Donc effectivement je pourrais le faire champ par champ. Pour limiter l'undo. (ca voudrait cependant dire que que je vais devoir le faire 15 fois.
Ou alors je peux faire:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
LOOP
update champ1
commit;
END LOOP
 
LOOP 
update champ2
commit;
END LOOP
 
etc..

C'est une idee, je vais partir dans ce sens la...

Sur 4 Millions de lignes, combien ont des espaces en début ou fin de champ ?

Question interessante, je pars du principe que tous les champs on des espaces (ca vient d'un systeme qui fait du padding pour "remplir" tous les champ)

Ce qui me gene le plus dans la requete de PlaineR c'est l'incrementation.
Le m'explique:

Disons que v_commit = 1000
et que la clause where c'est rownum<v_commit
Au premier tour il va faire la mise a jour des v_commit premiers
Ensuite je vais ajouter un "pas" a v_commit (donc v_commit := v_commit + 1000)

La clause where sera donc rownum < 2000:
- ils va donc parcourir les 2000 premiers (jarter les 1000 premiers et updater de 1001 a 1999) et ainsi de suite, ce qui fait que la requete devient de plus en plus importante, je sais pas trop ce que ca vaut niveau perfs (la table serait bloquée, pendant ce temps la...

)

Idealement il me faudrait un identifiant qui s'incremente.
J'ai pensé a rowid, mais j'ai aucune valeur dedans...

**orafrance** · 29/11/2007, 13h38

quelquechose dans ce goût là :

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
DECLARE
cursor c IS
    SELECT *
    from DIM_CLUSTER_SCD
 FOR UPDATE of CLS_NAME,CLS_SEGMENT_CODE,CLS_SEGMENT_NAME,CLS_SUB_SEGMENT_CODE,CLS_SUB_SEGMENT_NAME,CLS_LEVEL  NOWAIT ;
 
TYPE name_array         is TABLE of DIM_CLUSTER_SCD.CLS_NAME%type;
TYPE segment_code_array is TABLE of DIM_CLUSTER_SCD.CLS_SEGMENT_CODE%type;
TYPE segment_name_array is TABLE of DIM_CLUSTER_SCD.CLS_SEGMENT_NAME%type;
TYPE sub_seg_code_array is TABLE of DIM_CLUSTER_SCD.CLS_SUB_SEGMENT_CODE%type;
TYPE sub_seg_name_array is TABLE of DIM_CLUSTER_SCD.CLS_SUB_SEGMENT_NAME%type;
TYPE level_array        is TABLE of DIM_CLUSTER_SCD.CLS_LEVEL%type;
 
lv_name_arr          name_array;
lv_segment_code_arr  segment_code_array;
lv_segment_name_arr  segment_name_array;
lv_sub_seg_code_arr  sub_seg_code_array;
lv_sub_seg_name_arr  sub_seg_name_array;
lv_level_arr         level_array;
BEGIN
   OPEN c;
   LOOP
   FETCH c BULK COLLECT INTO lv_name_arr,
                             lv_segment_code_arr,
                             lv_segment_name_arr,
                             lv_sub_seg_code_arr,
                             lv_sub_seg_name_arr,
                             lv_level_arr
    LIMIT 100;
    EXIT WHEN c%notfound;
 
    FOR i in 1 .. lv_name_arr.count
    LOOP
      IF lv_name_arr(i) != rtrim(ltrim(lv_name_arr(i)))
          OR lv_segment_code_arr(i) != rtrim(ltrim(lv_segment_code_arr(i)))
          OR lv_segment_name_arr(i) != rtrim(ltrim(lv_segment_name_arr(i)))
          OR lv_sub_seg_code_arr(i) != rtrim(ltrim(lv_sub_seg_code_arr(i)))
          OR lv_sub_seg_name_arr(i) != rtrim(ltrim(lv_sub_seg_name_arr(i)))
          OR lv_level_arr(i) != rtrim(ltrim(lv_level_arr(i))) 
        THEN
 
         UPDATE DIM_CLUSTER_SCD SET
              CLS_NAME             = rtrim(ltrim(lv_name_arr(i))),
              CLS_SEGMENT_CODE    = rtrim(ltrim(lv_segment_code_arr(i))),
              CLS_SEGMENT_NAME    = rtrim(ltrim(lv_segment_name_arr(i))),
              CLS_SUB_SEGMENT_CODE = rtrim(ltrim(lv_sub_seg_code_arr(i))),
              CLS_SUB_SEGMENT_NAME = rtrim(ltrim(lv_sub_seg_name_arr(i))),
              CLS_LEVEL            = rtrim(ltrim(lv_level_arr(i)))
        WHERE CURRENT OF c;
 
       END IF;
     END LOOP;
     COMMIT;
   END LOOP;
   CLOSE c;
 END;
/

avec du SQL dynamique mais gare à la migraine

Note que je n'ai pas testé

**McM** · 29/11/2007, 13h52

Mais aussi quelle idée d'utiliser LTRIM(RTRIM( au lieu de TRIM(, c'est sur ça donne mal à la tête.

Karibou: PlaineR n'a jamais fait d'incrémentation de v_commit !!

Tu confonds le ROWNUM et disont le ROWID
Le rownum c'est le n° de la ligne fetchée par la requete.
Donc si tu corriges les champs au premier update, la seconde fois, les lignes ramenées seront différentes, et tu continuera à n'en prendre qu'un paquet de 10000.

**Karibou** · 29/11/2007, 14h38

J'ais utilisé LTRIM(RTRIM()) tout simplement car j'ai copié le code des modifs que j'ai fait dans informatica (qui lui ne supporte pas TRIM())

Ca fonctionne tres bien!!!!

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
declare
v_commit number := 1000;
begin
LOOP
UPDATE DIM_CLUSTER_SCD SET
	cls_name		= TRIM(cls_name),
	cls_segment_code	= TRIM(cls_segment_code),
	cls_segment_name	= TRIM(cls_segment_name),
	cls_sub_segment_code	= TRIM(cls_sub_segment_code),
	cls_sub_segment_name	= TRIM(cls_sub_segment_name),
	cls_level		= TRIM(cls_level)
WHERE NVL(LENGTH(cls_name),0) <> NVL(LENGTH(TRIM(cls_name)),0)
OR NVL(LENGTH(cls_segment_code),0) <> NVL(LENGTH(TRIM(cls_segment_code)),0)
OR NVL(LENGTH(cls_segment_name),0) <> NVL(LENGTH(TRIM(cls_segment_name)),0)
OR NVL(LENGTH(cls_sub_segment_code),0) <> NVL(LENGTH(TRIM(cls_sub_segment_code)),0)
OR NVL(LENGTH(cls_sub_segment_name),0) <> NVL(LENGTH(TRIM(cls_sub_segment_name)),0)
OR NVL(LENGTH(cls_level),0) <> NVL(LENGTH(TRIM(cls_level)),0)
and rownum < v_commit;
commit;
exit when sql%notfound;
END LOOP;
end;

Chuis super content ca m'enleve une sacrée epine du pied

Merci beaucoup les gars!!!

ps: McM effectivement j'ai confondu rownum....

**eludinart** · 21/07/2011, 15h47

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
declare
v_commit number := 1000;
begin
LOOP
UPDATE DIM_CLUSTER_SCD SET
	cls_name		= TRIM(cls_name),
	cls_segment_code	= TRIM(cls_segment_code),
	cls_segment_name	= TRIM(cls_segment_name),
	cls_sub_segment_code	= TRIM(cls_sub_segment_code),
	cls_sub_segment_name	= TRIM(cls_sub_segment_name),
	cls_level		= TRIM(cls_level)
WHERE NVL(LENGTH(cls_name),0) <> NVL(LENGTH(TRIM(cls_name)),0)
OR NVL(LENGTH(cls_segment_code),0) <> NVL(LENGTH(TRIM(cls_segment_code)),0)
OR NVL(LENGTH(cls_segment_name),0) <> NVL(LENGTH(TRIM(cls_segment_name)),0)
OR NVL(LENGTH(cls_sub_segment_code),0) <> NVL(LENGTH(TRIM(cls_sub_segment_code)),0)
OR NVL(LENGTH(cls_sub_segment_name),0) <> NVL(LENGTH(TRIM(cls_sub_segment_name)),0)
OR NVL(LENGTH(cls_level),0) <> NVL(LENGTH(TRIM(cls_level)),0)
AND rownum < v_commit;

exit when sql%notfound;
commit;
END LOOP;
end;

le commit.... apres le sql%notfound.....
enfin je dis ca je dis rien... mais ca marche mieux sur ma version d'oracle (9i)

**McM** · 22/07/2011, 15h13

Le commit après ??? bein non justement, en gros tu fais une boucle, et tu commites jamais.

Update massif d'une table (massive :D)

SQL Oracle

Discussions similaires

Partager

Partager