[Chicago-talk] Help on Purging Database

[Steven Lembark]

-- Steve Peters <steve at fisharerojo.org>

> On Tue, Jan 24, 2006 at 12:39:08PM -0800, tiger peng wrote:
>> Hello All,
>> 
>> There is a need to create a general tool for purging
>> old records from transaction databases (Oracle). The
>> referential integrity is required but foreign keys or
>> triggers are no allowed (for performance reason).
>> 
>> I did googling and cpanning without good solution. Is
>> there anyone did similar tasks or knows where are the
>> similar utilities?
>> 
> 
> Without knowing any requirements or anything else about your system, 
> your likely best bet will be something custom in PL/SQL.  I would highly 
> doubt if a generic tool is available that does not at least rely on 
> foreign keys.

Actually, it's not hard to handle with a nested hash
of table dependencies and list of top-level keys that
need to be deleted. The main issue is how many of the
root- level beasties can be flushed without snowballing
deletes flooding the rollback/undo segments. 

A lot of it does depend on how many records need to 
be deleted at what levels and what proportion of 
the database they make up.


-- 
Steven Lembark                                       85-09 90th Street
Workhorse Computing                                Woodhaven, NY 11421
lembark at wrkhors.com                                     1 888 359 3508