IDS Forum

Re: alter table with lvarchar field

Posted By: Art Kagel
Date: Sunday, 18 August 2013, at 9:22 a.m.

In Response To: alter table with lvarchar field (KAMRAN HAQ)

Most column type alters can be accomplished as what are called in-place
alters. In and in-place alter the data is not actually modified until some
row on a page is inserted or updated at which time the entire page is
converted from its current 'version' to the new 'version'. That is why
most alters seem to be instantaneous, because nothing has happened except
to note that there is a new version of the table's schema and what that
version is or will be.

However, certain data types, including LVARCHAR and other User Defined
Datatypes (or UDTs) - LVARCHAR is implemented built-in as a UDT, cannot be
performed in-place and so the ALTER will trigger an immediate alteration of
all rows in the table using the older offline alter method which actually
requires that the entire table's data be copied into a new table with the
new schema, the original table dropped, the new table renamed, and all
indexes and constraints recreated. This can be VERY time consuming, will
require enough free space for this copy of the data in the new format, and
time to create indexes and possibly re-verify RI constraints.

There is no simple way around that process except by performing the alter
manually yourself to avoid or at least mitigate the downtime (ie copy the
data to a new table yourself somehow). Using dedicated utilities like my
dbcopy utility or engine features like external tables and PDQPRIORITY it
is sometimes possible to reduce the downtime significantly. If large parts
of the data are not normally accessed, you can play games like renaming the
original table dropping most indexes, create the new table, and copy only
active rows immediately so the table can be back in production quickly.
Then you would slowly copy the remaining data over time. However, there
is no way around the downtime issue completely.

Art

Art S. Kagel
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 Sun, Aug 18, 2013 at 12:00 AM, KAMRAN HAQ <khaq@i2cinc.com> wrote:

> It takes too long to alter a table if it has a column of type lvarchar
> (even
> the column being add/drop or modify is not of type lvarchar). A add/drop of
> column usually takes a second but if there is a lvarchar column in table,
> it
> may take hours dpending upon the number of rows in the table.
> Is there a way to speed up the process of structural change in a table with
> lvarchar column?
> Why add/drop column is so fast but modify column is slow? and Why a
> add/drop
> column in a table with lvarchar takes too long?
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001a1133f726d2e57a04e438b6b2

Messages In This Thread

• alter table with lvarchar field
  KAMRAN HAQ -- Sunday, 18 August 2013, at 12:00 a.m.
  • Re: alter table with lvarchar field
    Art Kagel -- Sunday, 18 August 2013, at 9:22 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.