Re: modify serial to serial8

Hello Superboer, I repeated my test after dropping the primary constraint and unique index on pe_id. Even though I ran into a Long Transaction. The docu says: When the Database Server Uses the In-Place Alter Algorithm The database server uses the in-place alter algorithm for certain types of operations that you specify in the ADD, DROP, and MODIFY clauses of the ALTER TABLE statement: ... - Modify a column for which the database server can convert all possible values of the old data type to the new data type. - Modify a column that is part of the fragmentation expression if value changes do not require a row to move from one fragment to another fragment after conversion. ... MODIFY Operations and Conditions That Use the In-Place Alter Algorithm Operation on Column Condition Convert a SERIAL column to a SERIAL8 column All Seems it is really a bug or a docu bug. Regards, Reinhard. > -----Ursprüngliche Nachricht----- > Von: informix-list-bounces@iiug.org > [mailto:informix-list-bounces@iiug.org]Im Auftrag von Superboer > Gesendet am: Mittwoch, 2. April 2008 11:31 > An: informix-list@iiug.org > Betreff: Re: modify serial to serial8 > > Hello Reinhard, > > > i think what is going on is that your fragmentation strategy is > altered (at least that is what the engine sees i think) > and this is probably why the whole table needs to be rewritten... > you could do a test on a nonfragged table and alter a serial into a > serial8. If that also does a slow > alter then the above is nonsense and you hit a bug. either a doc bug > or a real one. > > > Superboer. > > way fast=http://www.clipjes.nl/clip/nederlands/n/normaal_- > _oerend_hard.html > > > On 2 apr, 09:38, "Habichtsberg, Reinhard" <RHabichtsb...@arz- > emmendingen.de> wrote: > > Hi > > > > I need some help with alter table. We have a table with > about 1.500.000.000 > > rows. The table is growing fast. > > > > dbschema: > > > > "xxx.sql" 95 lines, 3483 characters > > { TABLE "informix".xxx row size = 62 number of columns = 18 > index size > > = 50 } > > create table "informix".xxx > > ( > > pe_id serial not null , > > r1 integer, > > r2 char(2), > > r3 smallint, > > r4 datetime year to second, > > r5 datetime year to second, > > r6 char(1), > > r7 char(8), > > r8 smallint, > > r9 smallint, > > r10 smallint, > > r11 char(1), > > r12 smallint, > > r13 smallint, > > r14 integer, > > r15 char(1), > > r16 decimal(12,2), > > r17 smallint > > ) > > fragment by expression > > ((pe_id >= 0 ) AND (pe_id < 60000000 ) ) in dbs111 , > > ((pe_id >= 60000000 ) AND (pe_id < 120000000 ) ) in dbs112, > > ((pe_id >= 120000000 ) AND (pe_id < 180000000 ) ) in dbs113, > > ... > > ((pe_id >= 1800000000 ) AND (pe_id < 1900000000 ) ) in dbs136, > > remainder in dbs137 > > extent size 1000000 next size 200000 lock mode row; > > > > create index "informix".xxx_1 on "informix".xxx (r2) using btree ; > > create index "informix".xxx_2 on "informix".xxx (r4,r14) > using btree ; > > create index "informix".xxx_3 on "informix".xxx (r3,r4) > using btree ; > > create unique index "informix".pk_xxx on "informix".xxx > (pe_id) using btree > > ; > > alter table "informix".xxx add constraint primary key > (pe_id) constraint > > "informix".pk_xxx2 ; > > > > It will not last a long time and pe_id will reach the limit of > > 2.147.483.647. > > > > I wanted to alter the type of the field from serial to serial8. The > > "Performance Guide for 9.40" said that this would be done > with an "in place > > alter". My test could not confirm this. Because the alter > table statements > > lasted long and the log files filled up I finally stopped the server > > instance (fortunately a test instance) to kill the session > (onmode -z didn't > > work). > > > > Now the question: Why does the statement > > > > alter table xxx> > modify pe_id serial8; > > > > doesn't work as "in place alter". > > > > IDS 9.40.FC9W2 on Solaris 9. > > > > Thanks for your help in advance. > > Regards > > Reinhard > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

On 2 Apr, 12:20, "Habichtsberg, Reinhard" <RHabichtsb...@arz- emmendingen.de> wrote: > Hello Superboer, > > I repeated my test after dropping the primary constraint and unique index on > pe_id. Even though I ran into a Long Transaction. > > The docu says: > When the Database Server Uses the In-Place Alter Algorithm > > The database server uses the in-place alter algorithm for certain types of > operations > that you specify in the ADD, DROP, and MODIFY clauses of the ALTER TABLE > statement: > ... > - Modify a column for which the database server can convert all possible > values >   of the old data type to the new data type. > - Modify a column that is part of the fragmentation expression if value > changes >   do not require a row to move from one fragment to another fragment after > conversion. > ... > MODIFY Operations and Conditions That Use the In-Place Alter Algorithm > Operation on Column                                     Condition > Convert a SERIAL column to a SERIAL8 column             All > > Seems it is really a bug or a docu bug. > > Regards, > Reinhard. > > > > > -----Ursprüngliche Nachricht----- > > Von: informix-list-boun...@iiug.org > > [mailto:informix-list-boun...@iiug.org]Im Auftrag von Superboer > > Gesendet am: Mittwoch, 2. April 2008 11:31 > > An: informix-l...@iiug.org > > Betreff: Re: modify serial to serial8 > > > Hello Reinhard, > > > i think what is going on is that your fragmentation strategy is > > altered (at least that is what the engine sees i think) > > and this is probably why the whole table needs to be rewritten... > > you could do a test on a nonfragged table and alter a serial into a > > serial8. If that also does a slow > > alter then the above is nonsense and you hit a bug. either a doc bug > > or a real one. > > > Superboer. > > > way fast=http://www.clipjes.nl/clip/nederlands/n/normaal_- > > _oerend_hard.html > > > On 2 apr, 09:38, "Habichtsberg, Reinhard" <RHabichtsb...@arz- > > emmendingen.de> wrote: > > > Hi > > > > I need some help with alter table. We have a table with > > about 1.500.000.000 > > > rows. The table is growing fast. > > > > dbschema: > > > > "xxx.sql" 95 lines, 3483 characters > > > { TABLE "informix".xxx row size = 62 number of columns = 18 > > index size > > >               = 50 } > > > create table "informix".xxx > > >   ( > > >     pe_id serial not null , > > >     r1 integer, > > >     r2 char(2), > > >     r3 smallint, > > >     r4 datetime year to second, > > >     r5 datetime year to second, > > >     r6 char(1), > > >     r7 char(8), > > >     r8 smallint, > > >     r9 smallint, > > >     r10 smallint, > > >     r11 char(1), > > >     r12 smallint, > > >     r13 smallint, > > >     r14 integer, > > >     r15 char(1), > > >     r16 decimal(12,2), > > >     r17 smallint > > >   ) > > >   fragment by expression > > >     ((pe_id >= 0 ) AND (pe_id < 60000000 ) ) in dbs111 , > > >     ((pe_id >= 60000000 ) AND (pe_id < 120000000 ) ) in dbs112, > > >     ((pe_id >= 120000000 ) AND (pe_id < 180000000 ) ) in dbs113, > > >     ... > > >     ((pe_id >= 1800000000 ) AND (pe_id < 1900000000 ) ) in dbs136, > > >     remainder in dbs137 > > >   extent size 1000000 next size 200000 lock mode row; > > > > create index "informix".xxx_1 on "informix".xxx (r2) using btree ; > > > create index "informix".xxx_2 on "informix".xxx (r4,r14) > > using btree ; > > > create index "informix".xxx_3 on "informix".xxx (r3,r4) > > using btree ; > > > create unique index "informix".pk_xxx on "informix".xxx > > (pe_id) using btree > > > ; > > > alter table "informix".xxx add constraint primary key > > (pe_id) constraint > > > "informix".pk_xxx2  ; > > > > It will not last a long time and pe_id will reach the limit of > > > 2.147.483.647. > > > > I wanted to alter the type of the field from serial to serial8. The > > > "Performance Guide for 9.40" said that this would be done > > with an "in place > > > alter". My test could not confirm this. Because the alter > > table statements > > > lasted long and the log files filled up I finally stopped the server > > > instance (fortunately a test instance) to kill the session > > (onmode -z didn't > > > work). > > > > Now the question: Why does the statement > > > > alter table xxx> > > modify pe_id serial8; > > > > doesn't work as "in place alter". > > > > IDS 9.40.FC9W2 on Solaris 9. > > > > Thanks for your help in advance. > > > Regards > > > Reinhard > > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list- Hide quoted text - > > - Show quoted text - The first bit that mentions "It will not last a long time and pe_id will reach the limit of 2.147.483.647. " does not include the bit in the question "alter table xxx modify pe_id serial8; ". And the first bit includes create table but no loading of rows. What exactly are you doing? Which bit takes too long?