 |
IDS Forum
Re: Cost of ALTER table
Posted By: Fernando Nunes
Date: Friday, 8 November 2013, at 4:39 p.m.
In Response To: Re: Cost of ALTER table (Art Kagel)
You have to review your ascii art scripts :)
I even tried it on a fixed width font, but it still looks weird !
On Fri, Nov 8, 2013 at 8:02 PM, Art Kagel <art.kagel@gmail.com> wrote:
> Hmm, that was supposed to be a big smiley. B^)
>
> 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 11:52 AM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > 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
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --089e012293ba5e335e04eaafdce7
>
>
>
> *******************************************************************************
> 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...
--001a11339f3ca1f56104eab1384a
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
