Re: Alter Table Too Slow

In article <2k3428$haa@cronkite.seas.gwu.edu> mstowski@seas.gwu.edu (Mike Mstowski) writes: > I am doing an alter table on a large table(75,000 rows, each row 500 bytes, >and 8 indexes) using Informix Online 5.0 on HP/UX 9000/800 series coumputer. >I'm adding a column to the table. >I probably should have dropped the indexes before such a task but I forgot >to do so. The program is now running for 6 hours and don't know if: >1) I should kill the program. (What problems might occur if I do this?) >2) Let if run. (How much longer might this run?) >3) Will I run out of any resources and how can I tell which ones? The best way to see where you stand is to run tbstat -t a few times. You should see the original tblspace listed (for a table that large, it should be obvious by the number of rows shown). You should also see another active tblspace whose row count is increasing with each tbstat. Assuming your system is otherwise quiet, that is most likely the new version of the table. You can run tbstat with a regular interval (e.g. tbstat -tr 20) and find the difference in the number of rows in the table in that time (via the rowcount in the tbstat -t report). You will then know the number of rows already "converted" (not really what happens, but you could think of it that way), and the number of rows that are likely to be converted in that time interval. You can then extrapolate. It won't be dead-on, but it should get you into the ballpark. It is more efficient to drop the indexes before the alter. Better yet (don't ask why, I haven't researched the why yet), create a new table with the new schema you want. Write a very simple esql/c (or 4gl) program to fetch from the original table sequentially, and then insert into the new table using an insert cursor. When the new table is loaded, drop the old table and rename the new one. Then create the indexes. The program is important, as an insert into..select from in sql will take forever. As a datapoint, using 6.0 on a uniprocessor HP, an insert into..select from on a million row table took over 30 mins (don't know how long exactly as I killed it after I got tired waiting). An esql/c program as described above took less than 10 minutes. Insert cursors fly! Dave -- Disclaimer: These opinions are not those of Informix Software, Inc. ************************************************************************** "I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney