IDS Forum

Re: Cost of ALTER table

Posted By: Art Kagel
Date: Friday, 8 November 2013, at 12:06 p.m.

In Response To: Re: Cost of ALTER table (Fernando Nunes)

Personally, as a database architect, I prefer to use a child table of
smaller CHAR columns of length 60 to 80 bytes with the parent's primary key
and an ordinal sequence number for unlimited text columns instead of a
VARCHAR or LVARCHAR in the parent. That way you ONLY save what you need to
save, you waste less disk, the insert and select performance is better, and
you present your applications with virtually unlimited text entry. If you
make the sequence column a SMALLINT you can get 32765 rows of say 80
characters. If that's not enough, then use an int to allow up to over 2
billion rows. If your users really want to be able to enter text into the
comments field on your forms for years at a time without stopping, make it
a BIGINT and allow 2^64 characters. At 100 words per minute it would take
a VERY fast typist several thousand years to fill the column's capacity.
;-)

But seriously, this is best. If your programmers are truly that brain dead
that they cannot merge the strings together, then give them a stored
procedure to do it that returns the concatenated string in an
LVARCHAR(32000) and wrap the table in a VIEW that makes the call to the
procedure to return the reunified comments "column"! Voila!

Art

Art S. Kagel, Principal Consultant

Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.

On Fri, Nov 8, 2013 at 7:05 AM, Fernando Nunes <domusonline@gmail.com>wrote:

> The only time I tried to measure, VARCHARs were much slower on INSERTs...
> Regards
>
> On Fri, Nov 8, 2013 at 1:46 PM, Everett Mills <
> Everett.Mills@nationalbeef.com> wrote:
>
> > That's true if you aren't updating those fields. Update a CHAR to any
> value
> > that fits the field length, no problem. Update a VARCHAR to something
> > bigger,
> > then you have a few hoops to jump through.
> >
> > --EEM
> >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > > FRANK
> > > Sent: Thursday, November 07, 2013 6:34 PM
> > > To: ids@iiug.org
> > > Subject: Re: Cost of ALTER table [31892]
> > >
> > > As the lengths of values of the column will vary sharply ( from 32
> > > characters to 128 characters), varchar(128) is expected to get both
> > > disk space saving and performance gaining, agree?
> > >
> > > We had past experience : that varchar(256) outperformed a lot
> > > char(256) when the table has hundreds of millions of rows and majority
> > > of its value lengths are much shorter ( less than 50 characters)! Do
> > > you have similar experience ? By the way, our application rarely update
> > > the rows after they are initially inserted.
> > >
> > > Thanks,
> > > Frank
> > >
> > > On Thu, Nov 7, 2013 at 4:42 AM, Habichtsberg, Reinhard <
> > > RHabichtsberg@arz-emmendingen.de> wrote:
> > >
> > > > 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.
> > > >
> > > >
> > > >
> > > >
> > > ***********************************************************************
> > > ********
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --047d7b66f285ca7bfc04ea9f8a0a
> > >
> > >
> > > ***********************************************************************
> > > ********
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
> *******************************************************************************
> > 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...
>
> --047d7bdc192cdcdb9604eaabb41c
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--047d7b343ef260514504eaad66bc

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.