Change varchar column
Mark found a table grew from 1GB to 1.5GB after unloading, dropping and recreating it with some columns widened from varchar(10) to varchar(255) and reloading. Obnoxio The Clown speculated about minimum sizes; John Carlson asked about identical schemas and detached indexes since 9.21. Madison Pruet said extent allocation in an empty dbspace could explain it, and suggested oncheck -pe. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Dear all, During a table schema migration task, I unloaded data from a table, drop the table and re-create the table with changed some column from varchar(10) to varchar(255), then re-load the original data back to the new table. We found space used increased from 1GB to 1.5GB for that table. Anyone know why. Thanks, Mark
Mark wrote: > Dear all, > > During a table schema migration task, I unloaded data from a table, drop > the table and re-create the table with changed some column from > varchar(10) to varchar(255), then re-load the original data back to the > new table. > > We found space used increased from 1GB to 1.5GB for that table. > > Anyone know why. It's hard to tell from this angle. Perhaps there is a different minimum size to the column or something? -- "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche
On Thu, 18 Dec 2003 01:10:06 +0800, "Mark" <kbl@net-yan.com> wrote: >Dear all, > >During a table schema migration task, I unloaded data from a table, drop the >table and re-create the table with changed some column from varchar(10) to >varchar(255), then re-load the original data back to the new table. > >We found space used increased from 1GB to 1.5GB for that table. > >Anyone know why. > Before and after schemas are identical? Starting with version 9.21 <?>, Informix creates all indices as detached.
"Mark" <kbl@net-yan.com> wrote in message news:<brq3mc$243e$1@news.hgc.com.hk>...
> Dear all,
>
> During a table schema migration task, I unloaded data from a table, drop the
> table and re-create the table with changed some column from varchar(10) to
> varchar(255), then re-load the original data back to the new table.
>
> We found space used increased from 1GB to 1.5GB for that table.
>
> Anyone know why.
How did you measure the table space? If you are simply checking the
npused and you also loaded the table in an empty dbspace, then it is
possible that as extents were being added that full extents were
allocated simply because the space was otherwise empty.
When a new extent is allocated, if the existing dbspace does not
currently have enough contigeous space to allocate a full extent, then
we will still allocate what we can. The order of allocation is 1)
empty space after the last allocated extent, 2) the next extent size,
3) what ever we can get. So if the space is available for a full
extent allocation, then we would have allocated that space.
It might be worth it to include the oncheck -pe of the dbspace to get
some idea as to how the space was allocated to figure out the why.
>
> Thanks,
> Mark