modify serial to serial8
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 xxxmodify 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
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