Discussion :

[Karibou]

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]

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]

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]

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]

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]

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]

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