Large Table Deletes

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

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. http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage_062009