Re: Altering a Large Table

Hmmm - so there is a boolean field in the table... so that must be the reason for not doing an in-place alter. I guess I will have to look at a different alternative for adding this field. Thanks Art!! Laurie >>> Art Kagel <art.kagel@gmail.com> 6/8/2010 3:27 PM >>> Unless the table has "special" columns (blobs, clobs, text, byte, lvarchar, boolean ) columns or you are addig a "special" type column all alters are in-place. Just run it. The only other good alternative is to create a new table without any indexes, constraints, or triggers with the new schema and copy the data from the old table to the new one using a method that limits transaction size (like my dbcopy utility) and when you are done, rename the new table drop the old one and recreate the triggers, indexes, and constraints. If there are constraints against other significant sized tables or foreign keys in other tables that reference this one, this will not be quick. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Tue, Jun 8, 2010 at 5:11 PM, Laurie Gustin <lgustin@utah.gov> wrote: IDS 10.0 FC8 I'm wondering if there is a trick to altering a large table without running into a large transaction. I'm trying to add a char(2) field to the end of an existing table - it has 23 cols and about 12 million rows. The total table size is about 2.5 GB, there is 4GB of logical logs. I'm running the following alter command in dbaccess; alter table address add add_source char(2);Is there a trick to doing an in-place alter? Thanks Laurie Laurie Gustin IT Programmer Analyst Department of Public Safety lgustin@utah.gov 801-965-4410 _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list