Lengthening a varchar

Hi, Could anyone give me any pointers on why lengthening a varchar field is a long-winded process with Informix? For example, I have a table with containing a million rows including a column of type varchar(20). I want to lengthen this to varchar(80) so I do: alter table <tabname> modify <colname> varchar(80); This operation takes a long time and this is unacceptable to us. We have had to come up with an alternative method of doing it which involves creating a new column, copying data and then doing some dropping and renaming. Pardon my ignorance, but is a varchar not stored using the amount of space required, rather than the maximum length of the field, so lengthening it should only make the restriction on the amount of space it can use less restrictive. I can see why shortening a field, which involves truncation would be slow, but not with lengthening. Ben.

Ben Thompson wrote: > Hi, > > Could anyone give me any pointers on why lengthening a varchar field is > a long-winded process with Informix? > > For example, I have a table with containing a million rows including a > column of type varchar(20). I want to lengthen this to varchar(80) so I do: > > alter table <tabname> modify <colname> varchar(80); > > This operation takes a long time and this is unacceptable to us. We have > had to come up with an alternative method of doing it which involves > creating a new column, copying data and then doing some dropping and > renaming. > > Pardon my ignorance, but is a varchar not stored using the amount of > space required, rather than the maximum length of the field, so > lengthening it should only make the restriction on the amount of space > it can use less restrictive. > > I can see why shortening a field, which involves truncation would be > slow, but not with lengthening. > Yes, but since you can set a minimum length for a VARCHAR and that might entail altering the records on disk and even may result in relocating them, IDS won't perform an in-place alter on a VARCHAR it uses the older copy-the-table-and-rename-it method whenever you ALTER a VARCHAR. Art S. Kagel Oninit > Ben. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > =========================================================================================== > Please access the attached hyperlink for an important electronic communications disclaimer: > > http://www.oninit.com/home/disclaimer.php > > =========================================================================================== > > =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================

Art S. Kagel (Oninit LLC) wrote: > Yes, but since you can set a minimum length for a VARCHAR and that might > entail altering the records on disk and even may result in relocating > them, IDS won't perform an in-place alter on a VARCHAR it uses the older > copy-the-table-and-rename-it method whenever you ALTER a VARCHAR. Thanks, that explains a few things. Ben.

Ben, what is your email address? "Ben Thompson" <ben@nomonitorsoftspam.com> wrote in message news:fmkk7h$ojq$1$8302bc10@news.demon.co.uk... > Art S. Kagel (Oninit LLC) wrote: > >> Yes, but since you can set a minimum length for a VARCHAR and that might >> entail altering the records on disk and even may result in relocating >> them, IDS won't perform an in-place alter on a VARCHAR it uses the older >> copy-the-table-and-rename-it method whenever you ALTER a VARCHAR. > > Thanks, that explains a few things. > > Ben.