RE: How does "ALTER TALBE" work?

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------ =_NextPart_000_01BD72F0.7158109A Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Art, Do you still have to turn off logging first, or does this ALTER FRAG...INIT stmt bypass the logs?? Thanks. =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D Paul A. Mosser, Open Systems DBA Wells Fargo Bank Tempe, Arizona, U.S.A. mosserp@wellsfargo.com =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D > -----Original Message----- > From: Art S. Kagel [SMTP:kagel@bloomberg.com] > Sent: Tuesday, April 28, 1998 7:17 AM > To: informix-list@iiug.org > Subject: Re: How does "ALTER TALBE" work? >=20 > Ramasubramanian Mahadevan wrote: >=20 > > 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. >=20 > That is true and since an ALTER TABLE like the one used in this old=20 > trick no longer modify ANY rows immediately, but only when they are=20 > next updated, one can no longer use this trick to compress a table. =20 > 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=20 > table and this WILL copy the entire table, immediately, into a new = set=20 > of extents in the specified dbspace which can be the same dbspace in=20 > which the table currently resides. This will, like the ALTER TABLE=20 > trick used to, reduce the number of extents in the table as long as=20 > there are not a large number of small free extents that are larger = than=20 > the table's NEXT SIZE value. Increasing NEXT SIZE will reduce this=20 > problem and further reduce the number of resulting extents. BTW = since=20 > 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. > =20 > > alastl@ctnet.net wrote: >=20 > > > In article <353DD1DC.6FD234CD@cs-controlling.de>, > > > "Volker Fr=E4nkle" <VFraenkle@cs-controlling.de> wrote: >=20 > > > > Hello, > > > > here is a little technical question. >=20 > > > > Is it true, that INFORMIX copies the whole data from the old = table > to > > > > the new table and then dropps the old table? >=20 > > > It's sad, but true. My recommendations: > > > 1. Drop all indices before altering table. > > > 2. Turn off transaction logging, if possible. >=20 > > > 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). >=20 > > > > Or is there any different technique? >=20 > > > Unload. > > > Drop old table. > > > Create with new schema (no indices). > > > Load. > > > Build indices on new table. >=20 > > > John Prideaux > > > alastl@ctnet.net ------ =_NextPart_000_01BD72F0.7158109A Content-Type: application/ms-tnef Content-Transfer-Encoding: base64 eJ8+IgkVAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy b3NvZnQgTWFpbC5Ob3RlADEIAQSAAQAhAAAAUkU6IEhvdyBkb2VzICJBTFRFUiBUQUxCRSIgd29y az8AcAkBCYABACEAAAAxN0E2MTA4Q0QwREVEMTExQjFGMTAwMDA4Mzc2OEZGNAAJBwEggAMADgAA AM4HBAAcAA4AOAAFAAIAQgEBBYADAA4AAADOBwQAHAAOADQAGwACAFQBAQ2ABAACAAAAAgACAAED kAYA+AwAACsAAAALAAIAAQAAAAMALgAAAAAAQAA5AJAzqO/vcr0BHgBwAAEAAAAdAAAASG93IGRv ZXMgIkFMVEVSIFRBTEJFIiB3b3JrPwAAAAACAXEAAQAAABsAAAABvXLOnErgv3HO3q8R0ZOKAAD4 RbIKAAgrGEAAAgEJEAEAAAAMCAAACAgAADQNAABMWkZ1ukMERQMACgByY3BnMTI10jIA+zM2Aegg AqQD4wkCAGNoCsBzZXQwPiAHEwKDAFAC8hC5VGEeaANxAoMOUBBWcHJxmjIRVn0KgAjIIDsJbz0O MDUWDwpgAoAKgXVjWwBQCwNjAEELYG4OEDDMMzMLphFRdCwKogqEgQqAICBEbyB5CGA4IHN0AxAD IBDgdmW0IHQb4HQIcAOgbwEgEiAJAGdnC4BnIGaeaREAGtAdYAXAZG8HkQx0aAQAEVBMVEVSQCBG UkFHLiAQSShOSVQcMW0FQGJ5HwqwBBEfIBzQHbFzPz+FGvpUEOBua3MuGVEjAtESAXMxNxr4ZCCO PSSvJZwa9FBhdQMgdEEuBdBvBBAEkB6AT5ZwCfAGAHkcQGVtBCDIREJBGvRXZRxwBCDqRgrAZxvg QiLRIlUokFcoEB6ABxF6AiBhHoBVeC5TLidQGvQEYCeicFRAdylyZinSLgWgbZ8a9CWfJb8kBxOk MTYwSrxsaQ8xCqADYCiAYwVA+i0zEk8FEB3hB0AF0AeQ8HNhZ2UzExr2MqQycQcLEzKkAgBpLTE0 NOMBQDIwMTgwAUAM0Daz1mIfwANhOgyDYhqTBgANJ2BLNDEDIFtTTVSoUDprOVJAAmBvA3DqYgSQ Zy2iXRr1N+AGYAsCMDhHVApQc2RheScrERRgAxEyOB6AMTlgOTggNzojwBFQTTk7R1RvOEcLgAIQ cm0YaXgtMjAcQEBpaRZ1OuAFsGc7SHViagMy0ThHUmU6IEhvKwfgHtMiH3RUH3BCRegiIHcFsGsh 5zU/NkJ/MjQM0AHBGVETwQvFGwNS6mEAwHNB0HJIcQMAA5GqTRMgYQEAdgORdzKiwzhAGwk+IEkg F3AHgL86oktRSZAd8guAIUNJP+VtPhAuFJADgXUHQB8CYckFQE9uMjBuZUrGA5G1C4AtC1FjHNAf dG5DEP8nYCKwHNBNtD/ABCAsgQVAJTKRYgGgbHlDgEd1zmkBAD8RBgBRTEpVSyBHPKAc8AciICh2 TXEp70RQHoAAcCSAaQVAH0ECIP8c0B1wIUMQ0RnAB5FAYgmA10xlSsYLgHQDYGQY0BxQ/QIgLxRg ARBP4SwVIldVc+9YYApQVRMAkG5P8QORQ6boQkxFVuFrHNEhYVXC7nUREFdFVZJsJIAa9Fhg/Q3g a1BxHaFWoQXABGEGkPFScEFOWUtQQxAEIAdw3weATCBOUClwPQFiVABVsf1SYXchYEyDUnAKwBzQ GvTlTsB4BUB1cDzgVyEegf1V0WMDkV9YXYEfFF70HPH3LbFZEQQRYRzgUkFZcBuwvxr0QwFJsCfC TsAH4GZMAF8dIR7yHQEhYWbxYwpQIX0bsFkcEWSyHtBbuB/STWRFTiBwT05ehVxUbb55Z2NLMCBS IEBowl8FsMNukF5gX2RicwqwT/D8OyBocR1RA6BnQFCAT6D/A1A0MAeAAjBXMV6VbZNVIqEfI1dJ TExmoXBScP8hUjvhHkAc0WdyHoBg+1hB/2sxaMMREV6FVgFjoTvhYNH/TIRvMAWQBpAIkCSAbxVi Qf0N4GhkozqwdzRIcBzQeCa/THEa9HikIVJthGpAchdw/wIwUmFm8VLBIwAbsCKwH0H/A/AccB6A XLdb+l6aXYMc8P8egBdxGNBc5E3QS6N2b3ul/yEQX3ODMl6VIWBjAWLyUIDfBUBnQAtgKeCBOnMA wBxx/wNQCeB2h04zYvKFUwXATjFLepZ7dycHoUVYIHBT/ElaXJBJwApBZ7FM4AUA/0wAW2EeEIn4 faKAmB9BGvTrUgJnkG1VE2YIcIRSgJ9/gaNm8ScgHFAeAXaVZ7FC/FRXW1WD9h9BH0FnQAqw/4WB Z5AcwAMgctIegHihbbHvXQNeYIhSXwN3g0FnQGChf5OJXoVVIkmBHOJ5QSgQcv8/8oAyA/BP8B6A HyMHgB8gfwRwUXMAcHMBB3MtUChxcv8sFV6FT0ELYBxAOkAy4E7A/HQunOFJ/0sCSyFwAQAgAw3g bbE8MzUzREQAMURDLjZGRDKQMzRDRJywcy0FoNdYUhxwHfEuAQA+GuWegnkbsCJWBvBc0AXAOABc OCdlNCLgZ5BEUDxW/TgAYQnwo4Ggn6GgnV+eZP1LIEgpcYBwpnqEY5LzMjB3AkCUggWQaAMAZLAD IHHvPLFYwlmLptRJYNEFQFrSB5kyTlEgQEZPUk1J/lhywgiQITRiUAbweeFOUP9nQANSlKZ7pRzw pnqBEwfRy3GaKCFkA2BwcCE0r0f7Ieueg3SJwTQgZEFhwlrSfWexTXySLbFwwWQBWNFz11NnolKi YTFnsUSyQZxB/1FhVTCZAQQgOrA/8VsBkKDvBnEeAWdktw0yfSIdNlhgvwBxANBYwh2mdCEdkHAn kfZpumieKEIysHjgVgRlof+MY1txi0Jc5JhFAHBbkySA+48ZTCBzKlWegl2Ak1JWAf9b+ZFyTAAF wExxQCByA1py/x2QHAJi8kRyHfJv8p4ormL/UjDAInizksIDoGWSIOAdYP+EUiEzTIEdMceDHYk6 sC2x/weQnigEYGMBYBEeMGpAkKDfVHC9sYTyB3C95im+b0sg/zNgWpOEZJphzcKEcQIwqVXrqgGz f1VOkG9JkLqZuDP7r0e6mUNL8SiAfZG/kXWD9xDQKJBnQChfUbjFz4aegvpM1DxCUrBeYbjGb/Gx B8nPn0pvqZAgUAUQAQC9JxB4niicXhr0FSEA4DADAP0/UgMAAB4AMUABAAAAEAAAAE1PU1NFUlA0 Mzk5MURBNwADABpAAAAAAB4AMEABAAAAEAAAAE1PU1NFUlA0Mzk5MURBNwADABlAAAAAAB4AQhAB AAAAHgAAADwzNTQ1RTRGNy42REVBQGJsb29tYmVyZy5jb20+AAAACwADgAggBgAAAAAAwAAAAAAA AEYAAAAAA4UAAAAAAAADAAWACCAGAAAAAADAAAAAAAAARgAAAAAQhQAAAAAAAAMAAIAIIAYAAAAA AMAAAAAAAABGAAAAAFKFAAB0EAAAHgABgAggBgAAAAAAwAAAAAAAAEYAAAAAVIUAAAEAAAAFAAAA OC4wMgAAAAADAAKACCAGAAAAAADAAAAAAAAARgAAAAABhQAAAAAAAAsABIAIIAYAAAAAAMAAAAAA AABGAAAAAA6FAAAAAAAAAwAGgAggBgAAAAAAwAAAAAAAAEYAAAAAEYUAAAAAAAADAAeACCAGAAAA AADAAAAAAAAARgAAAAAYhQAAAAAAAB4ACIAIIAYAAAAAAMAAAAAAAABGAA