Re: High Performance Delete

Don, I am sorry if I was unclear in my conversation with you yesterday. I in no way meant for you to understand that I would not assist you in finding a solution to the problem you posed. However, I was requesting additional information from you to assist me in my research and my comment regarding the IIUG email list, was meant in the context of it being another source of information for you in parallel with my efforts. I would appreciate it if you could send me the table schema information I requested yesterday. Thank you, Roger |---------+----------------------------> | | don.wolford@veriz| | | on.com | | | | | | 06/18/2003 10:27 | | | AM | | | | |---------+----------------------------> >------------------------------------------------------------------------------- -----------------------------------------------| | | | To: Roger Kee/Tampa/IBM@IBMUS, ids@iiug.org | | cc: garnet.williams@verizon.com | | Subject: High Performance Delete | | | >------------------------------------------------------------------------------- -----------------------------------------------| Roger (and IIUG folks), As you (Roger) requested yesterday, here's the email on the delete problem I'm looking for cleverness on. 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) _______________ "Oh, say, does that star-spangled banner yet wave O'er the land of the free and the home of the brave?" _______________ Don Wolford Verizon Wholesale Markets Information Technology tel: +1 813 978 4531, fax: +1 813 632 3812 net: don.wolford@core.verizon.com instant: VZ Lotus SameTime, or AOL IM: DCWtake2 page: +1 813 303 8950, 1592789@pagemart.net