Re: Massive delete - any ideas to do it nondisruptively? [1

My dbdelete utility was designed, albeit for a single table delete, with just this purpose in mind. It would not be difficult to modify it to be a)table specific, and b)to delete from all of the child tables at the same time or to use the same methodology in a custom crafted application. Dbdelete uses a two stage method, it opens a cursor to retrieve either a key or ROWID from the target table, fetches 8196 ids, closes the cursor, begins a transaction, constructs a delete statement with an IN () clause with a bunch of the ids in it, and executes the delete repeating until all 8196 have been deleted, then it commits that set and reopens the search cursor. This method minimized locking to the 8K rowids selected (and their corresponding index locks). Each block can be deleted and committed very quickly reducing the impact on other applications so long as they work with SET LOCK MODE TO WAIT <nsecs> enabled. The method also avoids having the search query and delete statements interfering with each other, which even when carefully coordinated can tank performance. Dbdelete is part of the package utils2_ak available for download from the IIUG Software Repository. Art S. Kagel ----- Original Message ----- From: iiug@dcw.mailshell.com At: 6/18 11:38 > I'm looking for some clever soul out there to help me with what should be a > well-solved problem by now. (If not, it's our chance to make history!) > > The situation is fairly classic: I have several large tables, one parent and > five children, tied together by a serial number. The parent table, but not the > children, has a timestamp. These are active tables, averaging about 100,000 > rows per day in the parent table and more in the children. When the data gets > old enough, the rows get deleted. > > Question: How can I purge old data, based on the timestamp, from these tables > NONDISRUPTIVELY? > > I know I can use HPL to unload the data I want to keep, drop the table, and > reload it. That works but it's disruptive. > > Obviously, I can just delete the relevant information, either in a single > cascading delete or by a joined delete for the children and a ranged one from > the parents. That works, too, but it's slow. > > I could copy the data I want to keep to new tables, drop the originals and > rename the copies. The problems there are that it's still a little disruptive > (though tolerably so) and the data in the tables being copied is being updated > during the copy, so keeping synchronization would be hard. The fatal flaw in > this plan, though, is I don't have enough disk space to copy the tables. > > The idea has been advanced of fragmenting the parent table based on the date and > time, detaching the fragment with the data to be purged, then dropping the > resultant new table. That works great for the parent (maybe, if indexes don't > have to be rebuilt), but how do I do the children? I have no control over the > serial numbers being used. > > So......... anyone out there have any great ideas for running these massive > deletes as a lights-out, nondisruptive process? (IDS 7.31.FC6, HP/UX 10.20) > > -Don Wolford, Verizon Data Services > > > _______________________________________________________ > The FREE service that prevents junk email http://www.mailshell.com