Cost of ALTER table

Posted in 2013

Summary

Frank asked why ALTER TABLE ... MODIFY a char(32) column to varchar(128) on 100-million-row tables under IDS 11.50 on Linux consumed huge amounts of disk. Keith Simmons and Fernando Nunes explained this is documented behaviour: changing VARCHAR size/reserve specs cannot be an in-place alter, so the whole table is rewritten to new space, needing roughly double the table size. Suggested alternatives were unload/drop/recreate/reload (e.g. via external tables), copying into a raw side table, or fragment-by-fragment migration with rename. Frank opted to take the table offline and run the alter; others debated avoiding varchar on big tables and filing an RFE.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Data Types & Schema Design, Platform-Specific Issues

FRANK — Wed, 06 Nov 2013 21:40:25 — source: IIUG Forums & Mailing Lists

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

↪ replying to FRANK

Keith Simmons — Wed, 06 Nov 2013 22:09:29 — source: IIUG Forums & Mailing Lists

Frank You don't say what the size and type of the column is before the alter. The performance guide indicates "Modifying the original size or reserve specifications of VARCHAR or NVARCHAR columns." does not happen as an in-place alter but as a slow alter which requires a re-write of the entire table to fresh disk space before the old table is deleted (so you will need twice the space during the alter). If you do not have this sort of space available you may need to unload, drop and reload or, if the table is fragmented, ou may be able to transfer a fragment at a time to a new table definition, drop each moved fragment and then rename the new table back to old. Keith On 6 November 2013 21:40, 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. > > --047d7bf15fc8ec426e04ea89660b

↪ replying to FRANK

Fernando Nunes — Wed, 06 Nov 2013 22:17:10 — source: IIUG Forums & Mailing Lists

http://pic.dhe.ibm.com/infocenter/idshelp/v115/topic/com.ibm.perf.doc/ids_prf_33 5.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

↪ replying to Fernando Nunes

FRANK — Wed, 06 Nov 2013 23:36:48 — source: IIUG Forums & Mailing Lists

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/ids_prf_33 5.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

↪ replying to FRANK

Habichtsberg, Reinhard — Thu, 07 Nov 2013 09:42:34 — source: IIUG Forums & Mailing Lists

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.

↪ replying to Habichtsberg, Reinhard

Fernando Nunes — Thu, 07 Nov 2013 15:25:13 — source: IIUG Forums & Mailing Lists

You CAN register feature requests.... the link is on my blog (RFE site) On Nov 7, 2013 9:43 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. > > --047d7b2ed34113ffc504ea97df51

↪ replying to Fernando Nunes

david@smooth1.co.uk — Thu, 07 Nov 2013 23:13:38 — source: IIUG Forums & Mailing Lists

And on my blog justdave.info ! On 07 November 2013 at 15:25 Fernando Nunes <domusonline@gmail.com> wrote: > You CAN register feature requests.... the link is on my blog (RFE site) > On Nov 7, 2013 9:43 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

↪ replying to Habichtsberg, Reinhard

FRANK — Fri, 08 Nov 2013 00:34:20 — source: IIUG Forums & Mailing Lists

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

↪ replying to FRANK

Everett Mills — Fri, 08 Nov 2013 13:46:09 — source: IIUG Forums & Mailing Lists

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 > > > > > > > > > > > > > > > > > > > > > > > > > > > >@@N

↪ replying to Everett Mills

Fernando Nunes — Fri, 08 Nov 2013 15:05:05 — source: IIUG Forums & Mailing Lists

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. > > >

↪ replying to Fernando Nunes

Art Kagel — Fri, 08 Nov 2013 17:06:21 — source: IIUG Forums & Mailing Lists

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 ; > > > > > > >

↪ replying to Art Kagel

Fernando Nunes — Fri, 08 Nov 2013 17:54:04 — source: IIUG Forums & Mailing Lists

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, > > > > > >@@N

↪ replying to Art Kagel

FRANK — Fri, 08 Nov 2013 19:27:50 — source: IIUG Forums & Mailing Lists

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@@

↪ replying to FRANK

Art Kagel — Fri, 08 Nov 2013 19:52:34 — source: IIUG Forums & Mailing Lists

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 spa

↪ replying to Art Kagel

Art Kagel — Fri, 08 Nov 2013 20:02:31 — source: IIUG Forums & Mailing Lists

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 w

↪ replying to Art Kagel

Fernando Nunes — Fri, 08 Nov 2013 21:39:52 — source: IIUG Forums & Mailing Lists

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 req

↪ replying to Fernando Nunes

Art Kagel — Sat, 09 Nov 2013 23:36:44 — source: IIUG Forums & Mailing Lists

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 colum

↪ replying to Fernando Nunes

Habichtsberg, Reinhard — Mon, 11 Nov 2013 15:20:56 — source: IIUG Forums & Mailing Lists

> -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando Nunes > 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 > Don't want to responsible for you getting sacked :-) I entered the RFE: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=414 69 Please vote. Regards Reinhard.

↪ replying to Habichtsberg, Reinhard

Habichtsberg, Reinhard — Tue, 12 Nov 2013 07:20:26 — source: IIUG Forums & Mailing Lists

> Don't want to responsible for you getting sacked :-) I entered the RFE: > http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=414 > 69 Sorry, the link was wrapped: For your convenience: www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=41469 > > Please vote. > > Regards > Reinhard. > > > ************************************************************************ ******* > Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Habichtsberg, Reinhard

Fernando Nunes — Tue, 12 Nov 2013 19:54:36 — source: IIUG Forums & Mailing Lists

Thanks :) Already voted for it. Regards On Tue, Nov 12, 2013 at 9:20 AM, Habichtsberg, Reinhard < RHabichtsberg@arz-emmendingen.de> wrote: > > Don't want to responsible for you getting sacked :-) I entered the > RFE: > > > http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=414 > > 69 > > Sorry, the link was wrapped: > > For your convenience: > www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=41469 > > > > > Please vote. > > > > Regards > > Reinhard. > > > > > > > ************************************************************************ > ******* > > 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... --20cf30780db096886304eb0037da