 |
IDS Forum
Re: Cost of ALTER table
Posted By: Art Kagel
Date: Saturday, 9 November 2013, at 6:36 p.m.
In Response To: Re: Cost of ALTER table (Fernando Nunes)
It looked good before I sent it.
Art
On Nov 8, 2013 4:40 PM, "Fernando Nunes" <domusonline@gmail.com> wrote:
> 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
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e0116136a32c3f904eac6f848
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
