Re: Alter Table

In article <1992Jun8.173005.25600@anasaz> naomi@anasaz (Naomi Walker) writes: >> We have a large table that needs to be altered. We could either alter is by >> using an sql alter or we can unload the table in the revised format, drop the >> table, create the table in the revised format, and reload. We know the second >> method will work, but will be a slightly more complicated procedure. Any >> thoughts on the pitfalls of the first method (eg: root dbspace concerns, >> shared memory concerns, logging concerns, etc.) > >You should have *lots* of concerns about this process. Its a catch-22 mess. > >First, there is the lock table to worry about. The maximum number of >locks that may be configured is 256k. When an ALTER TABLE is executed, >a lock is created for *each* row, for *EACH* key. That is, if you have >100k rows, with 3 keys, you get 300k keys (which would overflow your >lock table, and cause disastrous results). This is completely untrue. ALTER TABLE does NOT require locks for every row, let alone every row/key combination, in either the origin table or the target table. >You could put an exclusive lock on the table, causing only one lock >entry. HOWEVER, then your transaction would be so long it would blow >out your log files. The problem exists even if logging is set to /dev/null, >since the one huge transaction *has* to fit in the log file. Wrong, wrong, wrong. ALTER TABLE will automatically lock the origin table; if anybody else is already using the table in a transaction, the ALTER attempt fails with a -242/-113. It exclusively locks the target table (the table it is building to eventually replace the table being ALTERed). Also, a transaction *can* span log files. You just can't span your *entire set* of log files with one transaction, as it would disallow a rollback. >Informix Note: Are you listening? I know you must have heard this sad >saga before. Can you help us out here? Not really -- this "sad saga" is fiction. > Naomi Walker (aka N7FSA) Anasazi Inc. Phoenix, Arizona > naomi%anasaz.UUCP@asuvax.eas.asu.edu -- Alan Denney # aland@informix.com # {pyramid|uunet}!infmx!aland Disclaimer: These opinions are mine alone. If I am caught or killed, the secretary will disavow any knowledge of my actions. "Cities are among the most cosmopolitan places on Earth." -- Dianne Feinstein, addressing a conference on cities