 |
IDS Forum
Re: Cost of ALTER table
Posted By: Art Kagel
Date: Friday, 8 November 2013, at 2:52 p.m.
In Response To: Re: Cost of ALTER table (FRANK)
oooo oooo
o o o o
oooo ( oooo
(_
\__________/
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 11:27 AM, FRANK <yunyaoqu@gmail.com> wrote:
> Yes. Fully agree that lot of good or better options are out there for
> database design from both resource saving or performance point of view...
> to make database run fast and healthy from a long run! Unfortunately,
> many things are out of our control( for example, the Boss designed it
> and like that.... )
>
> On Fri, Nov 8, 2013 at 12:06 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > 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
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a11c3a1bc68786c04eaaf6077
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7b34337cc961c704eaafb87c
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
