Times of run a Alter

Folks, IDS11.50 FC8 Interesting question. We have a table (see below) with 140 millions of rows. We tested the following two alters sequentially ( run in one session in dbaccess), ALTER TABLE ds_np_gran MODIFY (sub_inventory_id bigserial) ; ALTER TABLE ds_np_gran MODIFY ( inventory_id bigint); The first ALTER took 2.5 hours, but the second only took 5 seconds. Any explanations ? Thanks, Frank create table "informix".ds_np_gran ( sub_inventory_id serial not null , inventory_id integer not null , reference_id varchar(40), granule_id varchar(20), granule_version varchar(10), gn_start_dt datetime year to fraction(3), gn_end_dt datetime year to fraction(3), sw_version varchar(20), asc_desc_flag char(1), granule_status varchar(30), craft_maneuver varchar(4), percent_missing float, percent_err_data float, percent_na float, coordinate_north float, coordinate_east float, coordinate_south float, coordinate_west float, day_night_flag char(1), cloud_cover float, graceful_degrad char(1), orbit_number integer not null , algorithm_version varchar(64), quality_value varchar(255) ) in dbdata21 extent size 8192 next size 2048 lock mode row; --001a11352502fae44504f491ae1e

I believe we should give full schema. Probably no need building index for the second ..... Anyway ... create table "informix".ds_np_gran ( sub_inventory_id serial not null , inventory_id integer not null , reference_id varchar(40), granule_id varchar(20), granule_version varchar(10), gn_start_dt datetime year to fraction(3), gn_end_dt datetime year to fraction(3), sw_version varchar(20), asc_desc_flag char(1), granule_status varchar(30), craft_maneuver varchar(4), percent_missing float, percent_err_data float, percent_na float, coordinate_north float, coordinate_east float, coordinate_south float, coordinate_west float, day_night_flag char(1), cloud_cover float, graceful_degrad char(1), orbit_number integer not null , algorithm_version varchar(64), quality_value varchar(255) ) in dbdata21 extent size 8192 next size 2048 lock mode row; revoke all on "informix".ds_np_gran from "public" as "informix"; create index "informix".ds_np_gran_idx on "informix".ds_np_gran (gn_start_dt) using btree in dbdata21; create index "informix".ds_np_gran_idx1 on "informix".ds_np_gran (gn_end_dt) using btree in dbdata21; create index "informix".ds_np_gran_idx2 on "informix".ds_np_gran (granule_id) using btree in dbdata21; create index "informix".ds_np_gran_idx3 on "informix".ds_np_gran (sw_version) using btree in dbdata21; create unique index "informix".ds_np_gran_ipk on "informix".ds_np_gran (sub_inventory_id) using btree in dbdata21; alter table "informix".ds_np_gran add constraint primary key (sub_inventory_id) constraint "informix".ds_np_gran_pk ; alter table "informix".ds_np_gran add constraint (foreign key (inventory_id) references "informix".ds_np_agg constraint "informix".ds_np_gran_fk); On Fri, Mar 14, 2014 at 10:14 AM, FRANK <yunyaoqu@gmail.com> wrote: > Folks, > > IDS11.50 FC8 > > Interesting question. We have a table (see below) with 140 millions of > rows. > > We tested the following two alters sequentially ( run in one session in > dbaccess), > > ALTER TABLE ds_np_gran MODIFY (sub_inventory_id bigserial) ;> > ALTER TABLE ds_np_gran MODIFY ( inventory_id bigint);> > The first ALTER took 2.5 hours, but the second only took 5 seconds. > > Any explanations ? > > Thanks, > Frank > > create table "informix".ds_np_gran > ( > > sub_inventory_id serial not null , > > inventory_id integer not null , > > reference_id varchar(40), > > granule_id varchar(20), > > granule_version varchar(10), > > gn_start_dt datetime year to fraction(3), > > gn_end_dt datetime year to fraction(3), > > sw_version varchar(20), > > asc_desc_flag char(1), > > granule_status varchar(30), > > craft_maneuver varchar(4), > > percent_missing float, > > percent_err_data float, > > percent_na float, > > coordinate_north float, > > coordinate_east float, > > coordinate_south float, > > coordinate_west float, > > day_night_flag char(1), > > cloud_cover float, > > graceful_degrad char(1), > > orbit_number integer not null , > > algorithm_version varchar(64), > > quality_value varchar(255) > ) in dbdata21 extent size 8192 next size 2048 lock mode row; > > --001a11352502fae44504f491ae1e > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c22c701251f504f491cc18

Yes. To add an int, int8, or bigint column to a table is trivial and is performed as an in-place alter. Not data is modified. The version id of the table's schema is incremented and a new partition page is written for the new schema with a pointer to the older one. Rows are not actually converted from the older schema image to the new one until a row on that page has been updated which causes all rows on the page to be converted and the page rewritten with the new version id. Until a row is converted the engine does an in memory conversion of any row that is read and returned to a user supplying a NULL for any new column's value. For serial, serial8, and bigserial, on the other hand have to be processed immediately causing every row to be converted and a serial value assigned to each row. That's because, as you may guess, the engine would have no idea what value to assign for the new column to a row that was not already converted. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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, Mar 14, 2014 at 10:14 AM, FRANK <yunyaoqu@gmail.com> wrote: > Folks, > > IDS11.50 FC8 > > Interesting question. We have a table (see below) with 140 millions of > rows. > > We tested the following two alters sequentially ( run in one session in > dbaccess), > > ALTER TABLE ds_np_gran MODIFY (sub_inventory_id bigserial) ;> > ALTER TABLE ds_np_gran MODIFY ( inventory_id bigint);> > The first ALTER took 2.5 hours, but the second only took 5 seconds. > > Any explanations ? > > Thanks, > Frank > > create table "informix".ds_np_gran > ( > > sub_inventory_id serial not null , > > inventory_id integer not null , > > reference_id varchar(40), > > granule_id varchar(20), > > granule_version varchar(10), > > gn_start_dt datetime year to fraction(3), > > gn_end_dt datetime year to fraction(3), > > sw_version varchar(20), > > asc_desc_flag char(1), > > granule_status varchar(30), > > craft_maneuver varchar(4), > > percent_missing float, > > percent_err_data float, > > percent_na float, > > coordinate_north float, > > coordinate_east float, > > coordinate_south float, > > coordinate_west float, > > day_night_flag char(1), > > cloud_cover float, > > graceful_degrad char(1), > > orbit_number integer not null , > > algorithm_version varchar(64), > > quality_value varchar(255) > ) in dbdata21 extent size 8192 next size 2048 lock mode row; > > --001a11352502fae44504f491ae1e > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1134de96fd031b04f4927856

Upgrade to 11.70.FC7W1 or later and serial -> bigserial is an in-place alter too. http://pic.dhe.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%2Fcom.ibm.perf.d oc%2Fids_prf_337.htm Ben.

Um, AFAIK, SERIAL -> BIGSERIAL as in-place is supported as is DECIMAL to BIGSERIAL, however, IB that Frank was trying to add a bigserial column which is still not in-place even in 12.10. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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, Mar 14, 2014 at 1:11 PM, BENJAMIN THOMPSON < benjamin.thompson@bskyb.com> wrote: > Upgrade to 11.70.FC7W1 or later and serial -> bigserial is an in-place > alter > too. > > > > http://pic.dhe.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%2Fcom.ibm.perf.d oc%2Fids_prf_337.htm > > Ben. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1134563066631704f4950fc6

Maybe I missed the point but I saw two alters being done and compared: serial -> bigserial; and int -> bigint The table previously contained a serial column. Ben.

Ahh, you are right, I definitely misread the post. I owe you a beer Ben. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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, Mar 14, 2014 at 3:42 PM, BENJAMIN THOMPSON < benjamin.thompson@bskyb.com> wrote: > Maybe I missed the point but I saw two alters being done and compared: > serial -> bigserial; and > int -> bigint > > The table previously contained a serial column. > > Ben. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1137ef8ec065cc04f496b556