Re: RE: Large Table Deletes

This is still a multi-tens_of_minutes solution. If not using a fragmentation strategy, it would be faster to re-write the table than delete 20% of it. j. On Jun 15, 2009, Ian Michael Gumby wrote: Ok, You didn't mention what language you wanted to do this in... Since you're testing, can you try these ideas out? If you do not need to do this in a transaction then each delete could be a singleton and atomic statement. Try something like this... (This isn't executable code) curs = SELECT rowid FROM table_name WHERE date < xxxx Foreach row in curs: DELETE FROM table_name WHERE rowid = row.rowid End Foreach Depending on the language, this could be done quickly. Or you could try ... (Again the syntax isn't accurate) SELECT rowid AS row_id INTO TEMP foo FROM table_name a WHERE a.date < xxx DELETE FROM table_name WHERE rowid IN (SELECT row_id FROM foo) [This may reduce your locks and speed things up.] HTH -G > From: In4MixDBA@gmail.com > Subject: Large Table Deletes > Date: Mon, 15 Jun 2009 09:44:57 -0700 > To: informix-list@iiug.org > > Hello, > > I am looking to archive/delete on a monthly basis about 2 million rows > from a 10 million row table. There is a date field that will be used > to drive the delete for records older than 6 months. The table has a > growth rate of about 2 million rows a month. For this particular > table, data is only added and is never modified or deleted by a user. > > Currently we are on IDS 10.00.FC6 and will be upgrading to 11.50.FC4 > within the next few months. > > Our legacy application uses rowids (not recommended, we know) and if > you fragment a table using the "with rowids" clause, then you cannot > detach fragments. I would like to be able to do this delete while > keeping the server on-line and not locking the whole table. Also, > although lock allocation is dynamic, running this delete would just > keep allocating locks and I would end up with a whopping 11.6 million > locks (I tried it in a test environment - not cool). > > Any good ideas on how to run this delete? > > --Dave > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list Hotmail® has ever-growing storage! Don’t worry about storage limits. Check it out. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list