IDS Forum

RE: Speed up alter-table execution

Posted By: NormaJean.Sebastian@tellabs.com
Date:

Terrence,
You said you can't be down that long... if you have down time at all
to do this, you could turn off logging. don't know how much that helps,
but it can't hurt.
are you making both changes in the same alter statement? If so, adding
the 4 new columns at the end of the table may not fall into the "in
place alter" logic as the entire table is touched to change the
lvarchar. An 'in place alter' would not take long and would not affect
the table until a row is updated or a new one is inserted. if you
combine the lvarchar change with the new fields... all rows are touched
to change lvarchar, so the 4 new fields, i would assume, are added at
that time. perhaps just modifying lvarchar alone would save some time?
(test). after lvarchar is changed, then alter to add the 4 new columns
at the end. Adding the columns at the end would then be 'in place
alter'/table versioning, and be quick.

what if you unloaded the primary key and the lvarchar column into
another table. then drop lvarchar. then add the new length lvarchar at
the end with the 4 new columns (in-place alter). then load lvarchar from
the saved data table (touches all rows, kinda nullifies the in-place
alter)? would informix have to process the entire table to drop the
column or would it just change the DDL and pretend the column is not
there? (like an 'in place delete' of sorts, i guess). of course to
reclaim the space, you'd probably have to reorg. i'm pretty sure the
pros out here can shoot holes in my theory, but hey... might be worth a
test.

do the modified/new fields affect indices? if so, mayhaps dropping them
and re-creating after the alter command would save time?

have you checked into HPL ? I don't know that much about it, but maybe
it can chew through your data faster and allow the schema change or
speed the movement to a new table/new schema?

Norma Jean


-----Original Message-----
From: redden96@yahoo.com [mailto:redden96@yahoo.com]
Sent: Tuesday, November 18, 2003 3:10 PM
To: ids@iiug.org; forum.subscriber@iiug.org
Subject: Re: Speed up alter-table execution [2189]


I would imagine that PDQ would help, but I don't know for sure. Probably
depends on whether or not the table is fragmented.

Your thought on a new table will probably be your best bet. The lvarchar
modification is what is causing the ALTER to take so long because it
isn't an "in place alter" so you are in reality doubling you table size
anyway and chewing up tons of log space to make this ALTER happen. You
would be better off to create a new "RAW" table and INSERT INTO SELECT *
FROM blah; This will avoid the logging and maximize your speed
requirement.

"Terrence Mu...." <terrence@wagerworks.com> wrote:
All,

Is there a way to speed up the execution time of an 'alter table'
statement?

We have a 7 million record table that requires the following changes:

* modify lvarchar(2048) column to lvarchar(32000)
* add 4 integer or numeric columns to the end of the table.



I've done this already on a development db (1 million records) and it
took over
2.5 hrs. This would take 7 times as long in production. We can't be down
that
long. Other than creating a 'new' table with the 'new schema' and
loading the old
data, is there a way to drastically improve the execution of this
alter-table?

This is a Sun 280R, 2cpu, 6GB memory dbserver, running Solaris 8 and IDS
9.4







Terrence Mullins
www.WagerWorks.com



---------------------------------
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard





-----------------------------------------
============================================================
The information contained in this message may be privileged
and confidential and protected from disclosure. If the
reader of this message is not the intended recipient, or an
employee or agent responsible for delivering this message to
the intended recipient, you are hereby notified that any
reproduction, dissemination or distribution of this
communication is strictly prohibited. If you have received
this communication in error, please notify us immediately by
replying to the message and deleting it from your computer.

Thank you.
Tellabs
============================================================

Messages In This Thread

• Speed up alter-table execution
  Terrence Mullins -- Tuesday, 18 November 2003, at 1:41 p.m.
  • Re: Speed up alter-table execution
    DL Redden -- Tuesday, 18 November 2003, at 4:10 p.m.
    • RE: Speed up alter-table execution
      NormaJean.Sebastian@tellabs.com -- Tuesday, 18 November 2003, at 7:46 p.m.
    • Re: Speed up alter-table execution
      Jean Sagi -- Thursday, 20 November 2003, at 7:18 a.m.
      • Re: Speed up alter-table execution
        Ravi T -- Thursday, 20 November 2003, at 9:53 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.