IDS Forum

RE: Cost of ALTER table

Posted By: Habichtsberg, Reinhard
Date: Thursday, 7 November 2013, at 4:42 a.m.

In Response To: Re: Cost of ALTER table (FRANK)

Hi all.

The slow alters are a general problem, resp. with varchar, lvarchar and
boolean. If there are lvarchar or boolean columns in your table even
each alter will be a slow alter!

One have to be aware of the expected amout of rows and size of a table
when determining the column types. With big tables I would avoid to use
those types which sqeeze you to slow alter, particular if you have to
alter tables frequently. That is, for example, the case in my company
because most of our software which uses informix is self-developed. With
one or two dba's we have to serve 30 developers. Because we are
underlying frequent software changes due to comply with changing legal
requirements the need to alter even very big tables of 100 of millions
or billions of rows appears rather often. And we cannot afford long
downtimes. We are running 7 x 24 h.

So, we have a critical glance to all "create and alter tables"
statements.

In your case I would challenge the need of varchar. Probably I would
refuse the alter to varchar and would suggest to alter to char(128).

And if anyone reads this who is able to place a feature request: Please
make inplace alter possible for changes on varchar and tables which hold
columns of type lvarchar and boolean.

Kind regards

Reinhard Habichtsberg
Dienstleistungsverantwortlicher IT-Rechenzentrum
Bereich Server-, Speicher- und Datenbanksysteme
B-203

> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
FRANK
> Sent: Thursday, November 07, 2013 12:37 AM
> To: ids@iiug.org
> Subject: Re: Cost of ALTER table [31886]
>
> Thanks Fernando and Keith!
>
> The original table schema is the following.
>
> It is modifying from check_sum char(32) to check_sum varchar(128).
>
> Thanks for the info of double space size needed for this job!
>
> I think we will mark it unavailable and do a online ALTER for the job.
>
> Regards,
> Frank
>
> create table "informix".ds_archive01
> (
>
> dataset_name varchar(255,44) not null ,
>
> archive_site char(10) not null ,
>
> path_name varchar(250,50),
>
> archive_dt datetime year to second,
>
> update_dt datetime year to second,
>
> hsm_fs char(10),
>
> check_sum char(32),
>
> tape_name char(16),
>
> tape_block integer,
>
> tape_offset integer,
>
> checksum_dt datetime year to second
> ) in dbdw04 extent size 4096 next size 4096 lock mode row; revoke all
on
> "informix".ds_archive01 from "public" as "informix";
>
> create index "informix".ds_arch01_idx1 on "informix".ds_archive01
>
> (archive_dt) using btree in dbdw04;
> create index "informix".ds_arch01_idx2 on "informix".ds_archive01
>
> (tape_name) using btree in dbdw04;
> create unique index "informix".ds_arch01_ipk on
"informix".ds_archive01
>
> (dataset_name) using btree in dbdw05;
> alter table "informix".ds_archive01 add constraint primary key
>
> (dataset_name) constraint "informix".ds_arch01_pk ;
>
> On Wed, Nov 6, 2013 at 5:17 PM, Fernando Nunes
> <domusonline@gmail.com>wrote:
>
> >
> >
> >
>
http://pic.dhe.ibm.com/infocenter/idshelp/v115/topic/com.ibm.perf.doc/id
s_prf_335.
> htm
> >
> > Without the whole table schema the answer may not be 100% accurate,
> > but it looks like you're on the last situation described in the URL
above:
> >
> > "Modifying the original size or reserve specifications of VARCHAR or
> > NVARCHAR columns."
> >
> > As the name implies, it will be "slow" and will consume at least the
> > double of the table size. The alternatives don't look good, but
that's
> > the reason why "in-place alters" were created. The options I see:
> >
> > 1- UNLOAD the table, drop/create it and load. This will save space
and
> > with external tables can be fast. Of course it means data
unavailability.
> > 2- Create a side table type raw, with the new schema. Copy the data.
> > Will not save space, but depending on the data use it may help with
> > availability (if the table has historic data that doesn't change you
> > can do most of the work "online" and then stop access for the
"recent"
> > data
> > 3- Other weird options like create a new table, rename the existing
> > one, create a view with union between both tables and move the data
> > "online", maybe with the usage of triggers to capture changes of
data
> > already copied.... Will not save space, but can give you the most
> > availability (and
> > work....)
> >
> > Regards
> >
> > On Wed, Nov 6, 2013 at 9:40 PM, FRANK <yunyaoqu@gmail.com> wrote:
> >
> > > Hi, All,
> > >
> > > IDS11.50 FC8, Redhat Linux 5.
> > >
> > > Seems the following types of ALTER to enlarge a column width of
some
> > > BIG tables(100 millions of rows),
> > >
> > > ALTER TABLE ds_archive01 MODIFY check_sum varchar(128);
> > >
> > > can incur excessive amount of disk consumption !!
> > >
> > > Any comments or suggestions?
> > >
> > > Thanks,
> > > Frank
> > >
> > > --089e0163535606074a04ea88ff3f
> > >
> > >
> > >
> > >
> >
> >
>
************************************************************************
*******
> > > Forum Note: Use "Reply" to post a response in the discussion
forum.
> > >
> > >
> >
> > --
> > Fernando Nunes
> > Portugal
> >
> > http://informix-technology.blogspot.com
> > My email works... but I don't check it frequently...
> >
> > --bcaec51d255275373b04ea898271
> >
> >
> >
> >
>
************************************************************************
*******
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --047d7b62226c17778304ea8a9fdf
>
>
>
************************************************************************
*******
> Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Cost of ALTER table
  FRANK -- Wednesday, 6 November 2013, at 4:40 p.m.
  • Re: Cost of ALTER table
    Keith Simmons -- Wednesday, 6 November 2013, at 5:09 p.m.
  • Re: Cost of ALTER table
    Fernando Nunes -- Wednesday, 6 November 2013, at 5:17 p.m.
    • Re: Cost of ALTER table
      FRANK -- Wednesday, 6 November 2013, at 6:36 p.m.
      • RE: Cost of ALTER table
        Habichtsberg, Reinhard -- Thursday, 7 November 2013, at 4:42 a.m.
        • RE: Cost of ALTER table
          Fernando Nunes -- Thursday, 7 November 2013, at 10:25 a.m.
          • RE: Cost of ALTER table
            david@smooth1.co.uk -- Thursday, 7 November 2013, at 6:13 p.m.
        • Re: Cost of ALTER table
          FRANK -- Thursday, 7 November 2013, at 7:34 p.m.
          • RE: Cost of ALTER table
            Everett Mills -- Friday, 8 November 2013, at 8:46 a.m.
            • Re: Cost of ALTER table
              Fernando Nunes -- Friday, 8 November 2013, at 10:05 a.m.
              • Re: Cost of ALTER table
                Art Kagel -- Friday, 8 November 2013, at 12:06 p.m.
                • Re: Cost of ALTER table
                  Fernando Nunes -- Friday, 8 November 2013, at 12:54 p.m.
                  • RE: Cost of ALTER table
                    Habichtsberg, Reinhard -- Monday, 11 November 2013, at 10:20 a.m.
                    • RE: Cost of ALTER table
                      Habichtsberg, Reinhard -- Tuesday, 12 November 2013, at 2:20 a.m.
                      • Re: Cost of ALTER table
                        Fernando Nunes -- Tuesday, 12 November 2013, at 2:54 p.m.
                • Re: Cost of ALTER table
                  FRANK -- Friday, 8 November 2013, at 2:27 p.m.
                  • Re: Cost of ALTER table
                    Art Kagel -- Friday, 8 November 2013, at 2:52 p.m.
                    • Re: Cost of ALTER table
                      Art Kagel -- Friday, 8 November 2013, at 3:02 p.m.
                      • Re: Cost of ALTER table
                        Fernando Nunes -- Friday, 8 November 2013, at 4:39 p.m.
                        • Re: Cost of ALTER table
                          Art Kagel -- Saturday, 9 November 2013, at 6:36 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.