Re: Wierd Table -- Aaargh!

In-Reply-To: <32ub3k$hga@ns.oar.net>; from "Pete Stiglich" at Aug 18, 94 12:47 (midnight) } } } Hello, I am having some really strange problems with one table. JUST that } table. Occasionaly, users will complain saying that the order entry program } is bogging down. Invariably, it is because of this ONE table. A query that } normally takes a second will take 10. } } It is using a unique index on 3 columns for the query. I've tried dropping } & recreating the index, clusturing it, bchecking the table & nothing works. } The only thing that gets it back to normal is unloading the table, dropping } it and recreating and reloading. } } There doesn't seem to be any pattern as to when it will happen. Sometimes } it will be fine for a month, sometimes for a week. I've replaced the } disk it was on, for other reasons, so I don't think thats the problem. It } only happens on this table. It is heavily used, but there are other tables } with more usage. } } Thank you. Please respond via mail } } Perplexed Pete } You could be facing the following problem as detailed in the summary as received from rridley@sin-pss.dhl.com } The is a bug #14829 in all versions of Informix Online from version } 5.01.UC1 up to but not including 5.02.UC1. } } The problem is due to the 'npused' value in the systables entry for } a table has been set to a negative number after massive } inserts and deletes have been performed on the table. Once the bug } has appeared, if you re-set the 'npused' value to a positive value, } as soon as you run update statistics it will become negative again. } } This causes the optimizer to do sequential searches through huge } tables, and also prevents applications from accessing data through } the rowid. } } The workaround: } } The workaround will re-set the 'npused' value to a more } accurate value. It is important to ensure that, once the bug has } manifested itself, update statistics is never run as it stands, since } this will immediately re-set the 'npused' value to negative. Another solution I've found is to drop the table and recreated, as you have done. ---------------------------------- Ti Lian Hwang email : tilh@sin-co.sin-ro.dhl.com ----------------------------------