 |
IDS Forum
Re: Cost of ALTER table
Posted By: Fernando Nunes
Date: Friday, 8 November 2013, at 12:54 p.m.
In Response To: Re: Cost of ALTER table (Art Kagel)
I don't like this... It's messy for searches and forces joins... But It
does avoid the other issues.
Something to consider is the page size vs rowsize... sometimes peole try to
"save" with VARCHARs, but then there is a lot of wasted space in the pages.
(meaning that in some cases you can use CHAR() without wasting more space.
Then you have to consider how much space you're really saving... Depends on
data...
But of course, we would not be discussing this if we did inplace for
VARCHARs... Some restrictions about inplace are easy to understand (if the
new type cannot represent all the possible values in the old type), but
this one I don't understand (lack of internal engine knowledge).
Did someone enter the RFE? I can do it, but I've done so many that I may
get fired :)
Regards
On Fri, Nov 8, 2013 at 5: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.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--001a11c3bea029d6f304eaae115f
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
