Re: modify serial to serial8
Hi David.
Sorry, thought it was clear: I wanted to alter field "pe_id" from type
serial to serial8 so the value of pe_id may exceed the limitation of
2,147,483,647 which is in effect for fields of type serial. Serial8 fields
may accept values up to 9,223,372,036,854,775,807.
The table is fragmented by field pe_id and contains about 1,500,000,000
rows.
What lasts very long and ends with long transaction is the alter table
statement which, following the manual should be an "in place alter" and not
a "slow alter".
Regards,
Reinhard.
> -----Ursprüngliche Nachricht-----
> Von: informix-list-bounces@iiug.org
> [mailto:informix-list-bounces@iiug.org]Im Auftrag von
> david@smooth1.co.uk
> Gesendet am: Mittwoch, 2. April 2008 21:02
> An: informix-list@iiug.org
> Betreff: Re: modify serial to serial8
>
> 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?
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>