Re: Large Table Deletes

Dave wrote: > 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? I don't have a solution, but I do admire the problem. :o) Actually, I have a couple of kludge-y ideas, but they're not very nice: 1. Run batch deletes continuously in the background. while true prepare delete cursor with hold for i = 1 to 1000 execute delete cursor end for commit work sleep 60 end while Be aware that this could lead to B-tree cleaner issues, but these should be tunable. I think Art Kagel has a program called dbdelete on the IIUG website somewhere that pretty much does this. 2. Amend the application so that each month's data goes into a separate table, so data for January (in any year) goes into table1, data for February (for any year) goes into table2, etc. This may obviously cause more problems than it's worth. 3. Really, you should fix your application so you don't use rowids. ;o) -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.