Re: How does "ALTER TALBE" work?

Volker Fr'nkle wrote: > > Hello, > here is a little technical question. > > Is it true, that INFORMIX copies the whole data from the old table to > the new table and then dropps the old table? > > Or is there any different technique? The answer depends on 1) what version of Informix are we talking about and 2) what specific alter? 1) Versions before IDS 7.23 did create a completely new table for ALMOST all alters. Versions after 7.23 began to implement in-place alter capability. Each of 7.23, 7.24 and 7.3 implemented more particular ALTERs as in-place alter. The in-place alter marks the table to be altered and actually alters rows in-place when each row is updated over time. The alter is not completed until all rows have been updated. You can force completion by running a no-op update on all rows (ex: update orders set order_num = order_num where 1 = 1;). In-place alter takes effect for any ALTER TABLE which modifies the data structure of the table including changing types of a column and adding or dropping columns. I do not know how later versions of SE behave in an ALTER TABLE. 2) Alters that do not change the structure or location of the data do not create a new table. ALTER FRAGMENT statements will naturally need to recreate, at least part of, the table in the new fragmentation scheme. Hope this helps. Art S. Kagel