Add a column to a big table
User asked about adding a column to a large table (hundreds of millions of rows) in IDS 11.70 and whether placement (front/middle/end) affects performance. Responses indicated in-place alter is possible unless extended data types (LVARCHAR, BOOLEAN) are used. Key factors: whether new column causes rows to exceed page size, default value specification, and replication status all impact performance.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Folks, IDS 11.70 A big table with hundreds of millions rows. When adding a column, from performance point of view, any potential preference or difference .... if the column would be added at front, at end or in the middle of existing columns? Thanks, Frank --001a11c29e9cb41d3304e97c6ca0
It should be an in place alter, as long as the table does not use any of the extended data types , such as LVARCHAR or BOOLEAN. If it does, the table will have to be re-written. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FRANK Sent: Thursday, October 24, 2013 10:42 AM To: ids@iiug.org Subject: Add a column to a big table [31802] Folks, IDS 11.70 A big table with hundreds of millions rows. When adding a column, from performance point of view, any potential preference or difference .... if the column would be added at front, at end or in the middle of existing columns? Thanks, Frank --001a11c29e9cb41d3304e97c6ca0 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hi, AFAIK it depends if the added column would extend the table over a page size, but I might be wrong. Also, it depends if you set a default value or not. Setting a default value will obviously touch each record. So, you have to try it on a separate machine from my point of view in order to estimate the time to execute. Best regards, Marcus Haarmann ----- Ursprüngliche Mail ----- Von: "Joe R. Plugge" <JRPlugge@west.com> An: ids@iiug.org Gesendet: Donnerstag, 24. Oktober 2013 18:01:15 Betreff: RE: Add a column to a big table [31807] It should be an in place alter, as long as the table does not use any of the extended data types , such as LVARCHAR or BOOLEAN. If it does, the table will have to be re-written. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FRANK Sent: Thursday, October 24, 2013 10:42 AM To: ids@iiug.org Subject: Add a column to a big table [31802] Folks, IDS 11.70 A big table with hundreds of millions rows. When adding a column, from performance point of view, any potential preference or difference .... if the column would be added at front, at end or in the middle of existing columns? Thanks, Frank --001a11c29e9cb41d3304e97c6ca0 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
That's a good point Marcus, I hadn't thought of that. This problem doesn't require the new row to exceed the page size either! Any alter that would cause the rows on an existing page to no longer all fit on that page will cause slower performance overall, even after all rows were updated. For example, if there were 10 rows on a page with 30 bytes unused, adding a 4 byte integer to the rows will mean that two of the rows will have to be moved to another page leaving redirection pointers behind in an in-place alter. Even if the alter is processed as a rewrite, there will be 20% less data on a page afterwards in cases like this so 20% more disk will be used and potentially need to be accessed. The table may become a candidate to be moved to a dbspace with wider pages. Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ 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 Thu, Oct 24, 2013 at 12:21 PM, Marcus Haarmann <marcus.haarmann@midoco.de > wrote: > Hi, > > AFAIK it depends if the added column would extend the table over a page > size, > but I might be wrong. > Also, it depends if you set a default value or not. Setting a default value > will obviously touch each record. > > So, you have to try it on a separate machine from my point of view in > order to > estimate the time to execute. > > Best regards, > > Marcus Haarmann > > ----- Ursprüngliche Mail ----- > > Von: "Joe R. Plugge" <JRPlugge@west.com> > An: ids@iiug.org > Gesendet: Donnerstag, 24. Oktober 2013 18:01:15 > Betreff: RE: Add a column to a big table [31807] > > It should be an in place alter, as long as the table does not use any of > the > extended data types , such as LVARCHAR or BOOLEAN. If it does, the table > will > have to be re-written. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > FRANK > Sent: Thursday, October 24, 2013 10:42 AM > To: ids@iiug.org > Subject: Add a column to a big table [31802] > > Folks, > > IDS 11.70 > > A big table with hundreds of millions rows. When adding a column, from > performance point of view, any potential preference or difference .... if > the > column would be added at front, at end or in the middle of existing > columns? > > Thanks, > Frank > > --001a11c29e9cb41d3304e97c6ca0 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3c7dc134a6904e97f87a8
No... We're not Oracle (on older versions maybe)... You can set a default and still have an inplace alter... Why should we force a slow alter for that (our competitors may have a reason)? If the row we read is not in the new format, that obviously no value was specified in the INSERT, so the default will be provided in the "new image row". Regards. On Thu, Oct 24, 2013 at 5:21 PM, Marcus Haarmann <marcus.haarmann@midoco.de>wrote: > Hi, > > AFAIK it depends if the added column would extend the table over a page > size, > but I might be wrong. > Also, it depends if you set a default value or not. Setting a default value > will obviously touch each record. > > So, you have to try it on a separate machine from my point of view in > order to > estimate the time to execute. > > Best regards, > > Marcus Haarmann > > ----- Ursprüngliche Mail ----- > > Von: "Joe R. Plugge" <JRPlugge@west.com> > An: ids@iiug.org > Gesendet: Donnerstag, 24. Oktober 2013 18:01:15 > Betreff: RE: Add a column to a big table [31807] > > It should be an in place alter, as long as the table does not use any of > the > extended data types , such as LVARCHAR or BOOLEAN. If it does, the table > will > have to be re-written. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > FRANK > Sent: Thursday, October 24, 2013 10:42 AM > To: ids@iiug.org > Subject: Add a column to a big table [31802] > > Folks, > > IDS 11.70 > > A big table with hundreds of millions rows. When adding a column, from > performance point of view, any potential preference or difference .... if > the > column would be added at front, at end or in the middle of existing > columns? > > Thanks, > Frank > > --001a11c29e9cb41d3304e97c6ca0 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7bf15fc86da1dd04e9805374
If you have replication turned on, it can take quite some time. j. On Oct 24, 2013, at 12:21 PM, "Marcus Haarmann" = <marcus.haarmann@midoco.de> wrote: > Hi,=20 >=20 > AFAIK it depends if the added column would extend the table over a = page size,=20 > but I might be wrong.=20 > Also, it depends if you set a default value or not. Setting a default = value=20 > will obviously touch each record.=20 >=20 > So, you have to try it on a separate machine from my point of view in = order to=20 > estimate the time to execute.=20 >=20 > Best regards,=20 >=20 > Marcus Haarmann=20 >=20 > ----- Urspr=FCngliche Mail -----=20 >=20 > Von: "Joe R. Plugge" <JRPlugge@west.com>=20 > An: ids@iiug.org=20 > Gesendet: Donnerstag, 24. Oktober 2013 18:01:15=20 > Betreff: RE: Add a column to a big table [31807]=20 >=20 > It should be an in place alter, as long as the table does not use any = of the=20 > extended data types , such as LVARCHAR or BOOLEAN. If it does, the = table will=20 > have to be re-written.=20 >=20 > -----Original Message-----=20 > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of = FRANK=20 > Sent: Thursday, October 24, 2013 10:42 AM=20 > To: ids@iiug.org=20 > Subject: Add a column to a big table [31802]=20 >=20 > Folks,=20 >=20 > IDS 11.70=20 >=20 > A big table with hundreds of millions rows. When adding a column, from=20= > performance point of view, any potential preference or difference .... = if the=20 > column would be added at front, at end or in the middle of existing = columns?=20 >=20 > Thanks,=20 > Frank=20 >=20 > --001a11c29e9cb41d3304e97c6ca0=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 >=20