Re: How does "ALTER TALBE" work?

Ramasubramanian Mahadevan wrote: > I remember reading in the Informix 7.2 manuals that Online > an in-place ALTER now. The manual is most probably "Guide To SQL: > Tutorial (v7.2)" , and it is one of the changes listed in the > introduction/preface. That is true and since an ALTER TABLE like the one used in this old trick no longer modify ANY rows immediately, but only when they are next updated, one can no longer use this trick to compress a table. However, new features to the rescue! You can do an ALTER FRAGMENT ON TABLE mytable INIT IN new_or_old_dbspace; even on a non-fragmented table and this WILL copy the entire table, immediately, into a new set of extents in the specified dbspace which can be the same dbspace in which the table currently resides. This will, like the ALTER TABLE trick used to, reduce the number of extents in the table as long as there are not a large number of small free extents that are larger than the table's NEXT SIZE value. Increasing NEXT SIZE will reduce this problem and further reduce the number of resulting extents. BTW since this is a page level copy, while the older trick was a row level copy and had to be performed twice, this method is many times faster. > alastl@ctnet.net wrote: > > In article <353DD1DC.6FD234CD@cs-controlling.de>, > > "Volker Fr'nkle" <VFraenkle@cs-controlling.de> 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? > > It's sad, but true. My recommendations: > > 1. Drop all indices before altering table. > > 2. Turn off transaction logging, if possible. > > Both of these will increase the performance and reduce the disk > > usage of ALTER TABLE. Bear in mind that if you are working on a > > database which is in use by others then turning off logging becomes > > more difficult (if not impossible). > > > Or is there any different technique? > > Unload. > > Drop old table. > > Create with new schema (no indices). > > Load. > > Build indices on new table. > > John Prideaux > > alastl@ctnet.net