Discussion :

[slausseur]

Bonjour,

Je vous contacte car je me trouve devant un problème que je ne sais pas résoudre:

J'ai un développeur qui doit changer le type d'une colonne de char(1) à char(2) sur une table partitionnée contenant 600 millions d'enregistrements.

Il avait d'autres modifications de type de colonne sur la meme table, qui sont passées sans problème en quelques secondes. Les colonnes impliquées étaient de type number cependant.

Mais celle-ci boucle pendant 25 heures avant de tomber en erreur car le UNDOTBS est full.

Code :

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

ALTER TABLE xxxxxxxx MODIFY yyyyyyy CHAR(2);

Existe-t-il des raisons qui feraient que cette modification soit plus longue que les autres, alors qu'il n'y a aucune contrainte sur la table?

Oracle version 9.2.0.7


Merci d'avance

[orafrance]

il doit faire un update de toutes les lignes pour ajouter un espace alors forcément c'est long

VARCHAR2 c'est mieux, il y a un réel besoin du CHAR plutôt que VARCHAR2 ?

[slausseur]

aucune idée sur la raison de char(2). Je suis chez ce nouveau client depuis 5 jours....

Quel est l'avantage de varchar par rapport à char sur la durée? (Ma question est peut-être un peu bête...)
Pourquoi le changement de "taille" d'une colonne number n'est pas aussi long?

Puisque la commande alter table induit un update sur chaque ligne, peut-on envisager de couper l'alter pour chaque partition afin d'y glisser un commit?

Un nouvel essai a été lancé sur la base de production ( timing oblige) et le tbs UNDO est en train de grossir de façon importante.

Merci d'avance...

[orafrance]

le type CHAR ajoute autant d'espace que nécessaire pour remplir la colonne contrairement à VARCHAR2. Ainsi un CHAR(2) contient toujours 2 caractères quand un VARCHAR2(2) ne contient que le nombre de caractères insérés gain de place donc ligne moins grosse donc bloc moins rempli donc moins d'I/O à ramener potentiellement plus de perf

Il faut que ton client change le type en VARCHAR2(2) et non CHAR(2) quitte à faire un trigger pour ajouter l'espace plus tard et évidemment un batch pour faire l'update. Ce batch pouvant être lancé partition par partition

Evidemment, il n'est pas possible de changer le type que sur une seule partition puisque là c'est la structure de la table que tu changes

[slausseur]

Merci pour toutes ces informations.

Si j'ai bien compris, la commande alter peut être complètement remplacée par les update? C'est ca?
Car si c'est le cas, les commits pourront être insérés.

Mon problème est vraiment plus le tablespace UNDO qui grossit que le temps que ca mettra. (je vais peut etre essayer de l'agrandir encore un peu, le TBS...)

Car à surveiller l'état d'avancement de la requête, j'ai déja bloqué 5 session OEM de mon poste.... Donc je commence à ne plus vraiment savoir quoi faire.

[pifor]

Pourquoi le changement de "taille" d'une colonne number n'est pas aussi long?

Probablement parce que Oracle ne change pas le format interne dans certains cas:

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
 
dev001> 
dev001> set timing on
dev001>
dev001> drop table t;
 
Table dropped.
 
Elapsed: 00:00:00.12
dev001>
dev001> create table t(x number(20));
 
Table created.
 
Elapsed: 00:00:00.11
dev001>
dev001> insert into t values(1);
 
1 row created.
 
Elapsed: 00:00:00.06
dev001> insert into t values(1000000000);
 
1 row created.
 
Elapsed: 00:00:00.00
dev001>
dev001> select dump(x) from t;
 
DUMP(X)
--------------------------------------------------------------------------------
 
Typ=2 Len=2: 193,2
Typ=2 Len=2: 197,11
 
Elapsed: 00:00:00.01
dev001>
dev001> alter table t modify (x number(30));
 
Table altered.
 
Elapsed: 00:00:00.12
dev001>
dev001> select dump(x) from t;
 
DUMP(X)
--------------------------------------------------------------------------------
 
Typ=2 Len=2: 193,2
Typ=2 Len=2: 197,11
 
Elapsed: 00:00:00.06
dev001>
dev001> alter table t modify (x number(20));
alter table t modify (x number(20))
                      *
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale
 
 
Elapsed: 00:00:00.00

[orafrance]

Si j'ai bien compris, la commande alter peut être complètement remplacée par les update? C'est ca?

oui, sauf qu'avec un CHAR tu as forcément un update sur toutes les lignes

Pourquoi le changement de "taille" d'une colonne number n'est pas aussi long?

parce qu'un nombre c'est un nombre c'est pas une chaine de caractère... Oracle n'ajoute pas d'espace à un type NUMBER

CHAR induit une contrainte forte sur la colonne : avoir une taille fixe de chaine de caractères... suis-je clair ?

[slausseur]

Merci pour number, char() et varchar2.... Maintenant j'ai bien compris la différence.

La problèmatique de char(2) est en fait qu'initialement la colonne avait été créée en char(1) et les développeurs ont pensé qu'augmenter juste la taille de la colonne serait moins couteux que de la modifier de type.

Pour ce qui est de la modification en cours, (de char(1) à char(2)), si elle plante une autre fois, suite à mon problème d'espace dans le UNDO, je peux donc leur proposer de changer leur commande :

Code :

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

ALTER TABLE xxx MODIFY yyy CHAR(2);

en une série d'update de toutes les lignes avec un commit très régulier, ou j'ai vraiment pas compris comment se faisait la modif de taille de colonne?


Je suis désdolée, je ne maitrise VRAIMENT pas l'aspect type de colonne...

[orafrance]

avant de faire l'update il convient :
- de confirmer qu'un blanc en fin de colonne est bien nécessaire s'il n'y a qu'un seul caractère (ce qui serait franchement idiot si je puis me permettre )
- de modifier la colonne en VARCHAR2(2) :

Code :

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

ALTER TABLE xxx MODIFY yyy CHAR(2);

[slausseur]

Je vous remercie pour toutes ces pistes.
La solution qui va être prise sera sûrement de recréer une nouvelle colonne vide de type var(2) (et oui, il y tiennent...) et de rensseigner toutes les lignes par des update. Cela permettra de faire des commits de façon régulière.

Merci encore

[orafrance]

et encore une fois les développeurs auront eu raison de la gentillesse du DBA