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? > > --Dave I tried to send this through the IIUG mailing list but ended up sending a personal email... Although your current environment doesn't allow it, I would recommend that you consider fragmentation. The reasons why I recommend this are: 1- Your deletes (made with whatever solution you choose from the several suggestions) will only hit one partition. In the end you'll have an existing, but empty partition This will work better if you can make the indexes associated with the partition (not global) 2- It makes sense that in the future we will allow TRUNCATE FRAGMENT. It it happens you'll be prepared... and you just have to exchange a batch process for an SQL statement. The point is to have a fixed set of fragments and re-use them after cleaning them (in cycle). You'd have to ADD/DROP expressions to your fragment expression on a monthly base. For the cleaning process, with the above in mind, if you SELECT based on the fragment column, you'll end up with a full scan of a useless fragment. A simple procedure with a SELECT FOR UPDATE and DELETE WHERE CURRENT OF (with periodic commits) would be able to delete the records in a pretty efficient manner. The really hard cleaning processes are the ones where you have other conditions (business rules) involved in the decision to delete or to keep. Besides this it's never to much to warn against the use of ROWID... I believe we may find one or two situations where it can make sense, but I can't recall one... Maybe for Art's delete utility... Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...