alter table with lvarchar field

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?

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