Re: Question about Informix's alter command

Path: emory!swrinde!sdd.hp.com!hplabs!pyramid!infmx!aland From: aland@informix.com (Colonel Panic) Newsgroups: comp.databases Keywords: informix alter Message-ID: <1991May8.031659.20490@informix.com> Date: 8 May 91 03:16:59 GMT References: <1991May7.142132.24212@kodak.kodak.com> Sender: news@informix.com (Usenet News) Organization: Alferd Packer's Legendary Coronary Fast Food Cannibal Bar & Buffet In article <1991May7.142132.24212@kodak.kodak.com> sengillo@Kodak.Com (Alan Sengillo) writes: >-- >Does anyone know if the alter command in Informix does anything else then just >alter the columns in a table? > >I am maintaining a program that after it delete rows from the database it >does an alter on the table. It seems that the alter is being done just to >reclaim diskspace. The problem is the table is large ( > 1,000,000 rows) and >the alter takes over 45 minutes. I want to remove the alter command, but I >want to make sure that the command is not doing anything else then just >reclaiming diskspace. I do not need to reclaim the diskspace since the table >is constantly growing. The deletes are done to remove old data. >| Alan Sengillo PHONE: (716) 726-9716 or 477-3503 | Probably; it depends upon the context. If it just does ALTER TABLE and modifies an existing column to the same datatype and length as it is already, it was probably done just to force a table rebuild to compress the data. As you state, any deleted record space is reused. Index btrees would be cleaner in a new table, so it could be a minor performance gain if there is a lot of insert/delete/index-update activity. Sometimes, especially if you have a lot of indexes and/or are deleting a large percentage of the rows, you will be better off to recreate the table. There is another method that performs better than ALTER: CREATE TABLE on your table schema under a new table name BEGIN WORK (if using transactions) LOCK TABLE newtable in exclusive mode INSERT INTO newtable SELECT * FROM oldtable UNLOCK TABLE (or COMMIT, if using transactions) CREATE INDEX in newtable for each desired index DROP table on oldtable RENAME table newtable to old name (if you had any views based on oldtable, they will have to be recreated). This is faster because the loading of data will be faster on an indexless table. -- Alan Denney aland@informix.com {pyramid|uunet}!infmx!aland "In the cafeteria just after lunch, (well, not *just* after, more like *during* lunch, about 12:28; say 12:30, give or take a few minutes), I leaned back in my chair (it was one of those aluminum chairs, good strength-to-weight, like titanium but not quite; but then of course titanium would be a bit of an overkill). Anyway, I heard one of the girls talking about how boring she thought engineers could be."